Check Buffer Hit Ratio

ttitle off
btitle off

prompt HIT_RATIO should be > 90%
prompt

prompt ( < 8i hit ratio )

--select 1-((phy.value + &n_acm)/(cur.value + con.value)) HIT_RATIO
select TRUNC((1-(phy.value/(cur.value + con.value)))*100,2) HIT_RATIO
from v$sysstat cur
, v$sysstat con
, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads'
/

prompt
prompt ( 8i+ hit ratio )
-- hit ratio =
--
-- 1 - ( physical reads - (physical reads direct + physical reads direct (lob)) )
-- --------------------------------------------------------------------------
-- ( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )
--

SELECT TRUNC(
(1 - (phy.value - (phyd.value + phydl.value)) /
(get.value + con.value - (phyd.value + phydl.value))
) * 100
,2) hit_ratio
FROM v$sysstat phy
, v$sysstat phyd
, v$sysstat phydl
, v$sysstat get
, v$sysstat con
WHERE phy.name = 'physical reads'
AND phyd.name = 'physical reads direct'
AND phydl.name = 'physical reads direct (lob)'
AND get.name = 'db block gets'
AND con.name = 'consistent gets'
/