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.