Data Dictionary Cache Hit Ratio

Every Oracle database has a data dictionary, which is owned by the SYS user and stored in the SYSTEM tablespace. This consists of read-only tables, which record information about the database e.g. table definitions, details of integrity constraints, usernames and the roles and privileges granted to them etc. You can read about this in detail in Oracle's own documentation.

So, for example, if user SCOTT tries to read a table, Oracle checks the data dictionary first to see whether he has been granted SELECT access to it.

Each time the data dictionary is accessed, the GETS column is updated in V$ROWCACHE.

Oracle retains parts of the data dictionary in a cache in the SGA. If the information (to determine if SCOTT will be allowed to read the table) is not  found in this cache, Oracle has to get it from the underlying tables. When this happens, the GETMISSES column is updated in V$ROWCACHE.

Once your database has been open for a while, you can use the number of GETS and GETMISSES to calculate the data dictionary cache hit ratio as shown in the SQL below, which I ran on an Oracle 11.2 database: 

SQL> select sum(gets) as "Gets",

  2  sum(getmisses) as "Misses",

  3  (1-(sum(getmisses)/sum(gets)))*100 as "DDC Hit Ratio"

  4  from v$rowcache

  5  /

 

      Gets     Misses DDC Hit Ratio

---------- ---------- -------------

  39375878      96143   99.7558327

 

SQL>

The hit ratio should be around 99% but anything over 90% should be OK. If it is too low, you can try increasing the size of your database's shared pool.

ORA-01643 etc

I have used ALTER TABLESPACE to change the status of a tablespace in several other posts. However, in some situations, you cannot change a tablespace's status. You can see what I mean in the examples below, which I tried out in an Oracle 10 database running on Linux.

You cannot make the SYSTEM, UNDO or TEMPORARY tablespaces READ ONLY:

SQL> alter tablespace system read only

  2  /
alter tablespace system read only
*
ERROR at line 1:
ORA-01643: system tablespace can not be made read only

SQL> alter tablespace undotbs1 read only
  2  /
alter tablespace undotbs1 read only
*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace

SQL> alter tablespace temp read only
  2  /
alter tablespace temp read only
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY
TABLESPACE

You cannot make a tablespace READ WRITE if is already ONLINE (READ WRITE and ONLINE have the same meaning):

SQL> select tablespace_name, status
  2  from dba_tablespaces
  3  /

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> alter tablespace users read write
  2  /
alter tablespace users read write
*
ERROR at line 1:
ORA-01646: tablespace 'USERS' is not read only -
cannot make read write

SQL> alter tablespace users read only
  2  /

Tablespace altered.

SQL>

... and once a tablespace is READ ONLY, you cannot make it READ ONLY again!

SQL> alter tablespace users read only
  2  /
alter tablespace users read only
*
ERROR at line 1:
ORA-01644: tablespace 'USERS' is already read only

SQL>

gather_schema_stats

You can gather statistics for a schema as follows:

ORACLE 9 > sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Thu Sep 16 10:31:39 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning option

JServer Release 9.2.0.7.0 - Production

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYSTEM');

PL/SQL procedure successfully completed.

SQL>

You used to be told not to analyze tables owned by SYS. However, we found that this was required before exporting constraints etc. from a production database during an upgrade from Oracle 9 to 11. It reduced the time taken from 2 to 3 hours down to 5 minutes.