REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM 	s_sga_db_buffer.sql
REM
REM <SQLDIR_GRP>SGA MOST</SQLDIR_GRP>
REM
REM Author:
REM 	Vitaliy Mogilevskiy
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM
REM Purpose:
REM	<SQLDIR_TXT>
REM	Displays Hit Ratio for DB_BUFFER
REM	HIT RATIO should be > 90%
REM     Refer to Metalink Doc ID: 33883.1 
REM          STATISTIC "cache hit ratio" - Reference Note
REM	</SQLDIR_TXT>
REM
REM    < 8i
REM    -------------
REM                               phys reads
REM    Db buffer hitratio = 1 -  --------------------------------     >  90% (v$sysstat)
REM                               db_block_gets + consistent_gets
REM
REM    8i + 
REM    ------------
REM     hit ratio =   
REM    
REM          1 -  ( physical reads - (physical reads direct + physical reads direct (lob)) )
REM               -------------------------------------------------------------------------- 
REM         ( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )
REM
REM Usage:
REM	s_sga_db_buffer.sql
REM
REM Example:
REM	s_sga_db_buffer.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM	09-10-2002	VMOGILEV	Added 8i + ratio formula
REM
REM

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'
/