SQL> SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets )) "HIT_RATIO"
FROM V$BUFFER_POOL_STATISTICS
WHERE ( consistent_gets + db_block_gets ) !=0
/
NAME HIT_RATIO
-------------------- ----------
DEFAULT .817011081
-- OR
SQL> select a.value + b.value "logical_reads", c.value "phys_reads",
round (100 * ((a.value + b.value)-c.value) / (a.value + b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
-- OR
-- Check hit ratio for a specific session
SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
FROM v$sesstat P1, v$statname N1, v$sesstat P2, v$statname N2,
v$sesstat P3, v$statname N3
WHERE N1.name = 'db block gets'
AND P1.statistic# = N1.statistic#
AND P1.sid = <SID>
AND N2.name = 'consistent gets'
AND P2.statistic# = N2.statistic#
AND P2.sid = P1.sid
AND N3.name = 'physical reads'
AND P3.statistic# = N3.statistic#
AND P3.sid = P1.sid;
|