Cache Hit Ratios

Just thought I'd list the cache hit ratios using the gv$ views instead of v$ view so that the ratios can be retrieved for all instances in one query.

Even with 10g and Oracle automatically sizing the pools within the SGA, these ratios are a good guide in whether enough memory is available to each instance.


db_cache hit ratio
Sizing for db_cache_size
Here we are aiming for a cache hit ratio > 95%

select ses.inst_id, 1-(PHY.VALUE - LOB.VALUE - DIR.VALUE)/SES.VALUE "DB_CACHE HIT RATIO"
FROM GV$SYSSTAT SES, GV$SYSSTAT LOB, GV$SYSSTAT DIR, GV$SYSSTAT PHY
WHERE ses.name ='session logical reads'
and dir.name ='physical reads direct'
and phy.name ='physical reads'
and lob.name ='physical reads direct (lob)'
and ses.inst_id = lob.inst_id
and ses.inst_id = dir.inst_id
and ses.inst_id = phy.inst_id;


library cache hit ratio
Sizing for shared_pool_size
Here we are aiming for a cache hit ratio > 99%

SELECT inst_id, SUM(PINS-RELOADS)/SUM(PINS)*100 "LIBRARY HIT RATIO"
FROM GV$LIBRARYCACHE
group by inst_id;


library cache reload to pins ratio
Sizing for shared_pool_size
Here we are aiming for a ratio < 1%

SELECT inst_id, SUM(PINS) "EXECUTIONS", SUM(RELOADS) "MISSES", SUM(RELOADS)/SUM(PINS)
"RELOAD RATIO"
FROM gV$LIBRARYCACHE
group by inst_id;


dictionary cache hit ratio
Here we are aiming for a cache hit ratio > 95%

SELECT inst_id, (SUM(GETS-GETMISSES))/SUM(GETS) "DICTIONARY CACHE HIT RATIO"
FROM GV$ROWCACHE
group by inst_id
/