Keeping the Data Dictionary Cache Hit Ratio at or above 95 Percent

The data dictionary cache is a key area to tune because the dictionary is accessed so frequently, especially by the internals of Oracle. At startup, the data dictionary cache contains no data. But as more data is read into cache, the likelihood of cache misses decreases. For this reason, monitoring the data dictionary cache should be done only after the system has been up for a while and stabilized. If the dictionary cache hit ratio is below 95 percent, then you’ll probably need to increase the size of the SHARED_POOL_SIZE parameter in the initialization parameter file.

Use the following query against the Oracle V$ view to determine the data dictionary cache hit ratio:

select ((1 - (Sum(GetMisses) / (Sum(Gets) + Sum(GetMisses)))) * 100) "Hit Rate"
from V$RowCache
where Gets + GetMisses <> 0;

Hit Rate
---------
91.747126


To diagnose a problem with the shared pool or the overuse of the shared pool, use a modified query to the V$ROWCACHE view. This will show how each individual parameter makes up the data dictionary cache, also referred to as the row cache.

column parameter format a20 heading 'Data Dictionary Area'
column gets format 999,999,999 heading 'Total|Requests'
column getmisses format 999,999,999 heading 'Misses'
column modifications format 999,999 heading 'Mods'
column flushes format 999,999 heading 'Flushes'
column getmiss_ratio format 9.99 heading 'Miss|Ratio'
set pagesize 50
ttitle 'Shared Pool Row Cache Usage'

select parameter, gets, getmisses, modifications, flushes,
(getmisses / decode(gets,0,1,gets)) getmiss_ratio,
(case when (getmisses / decode(gets,0,1,gets)) > .1 then '*' else ' ' end) " "
from v$rowcache
where Gets + GetMisses <> 0;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-598226/，如需轉載，請註明出處，否則將追究法律責任。