hit_ratio - liamlamth/blog GitHub Wiki

column "Consistent Gets" for 99,999,999,999,999
column "DB Block Gets" for 99,999,999,999,999
column "Physical Reads" for 99,999,999,999,999

show sga;

PROMPT
PROMPT Hit ratio should exceed 89%

SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
       Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
       Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
       Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
         Sum(Decode(a.name, 'db block gets', a.value, 0)) -
         Sum(Decode(a.name, 'physical reads', a.value, 0))  )/
           (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
             Sum(Decode(a.name, 'db block gets', a.value, 0))))
             *100,2) "Hit Ratio %"
FROM   v$sysstat a;

select 100*(sum(pins)-sum(reloads))/sum(pins) library_cache_hit_ratio
          from v$librarycache;

select 100*(sum(gets)-sum(getmisses))/sum(gets) row_cache_hit_ratio
       from v$rowcache;

exit