REM ************************************************************************
REM Filename:	Perf_bufferHitRatio.sql    
REM Description:
REM 	Displays the Buffer Hit Ratio.
REM Comments:
REM
REM Modifications:
REM Author		Date	  Reason
REM Darryl B. Smith	09/01/97  Created
REM ************************************************************************
prompt The bufferHitRation should be > 70.  If it not you should
prompt increase db_block_buffers in init.ora 
 
--select trunc((1-(sum(decode(name,'physical reads',value,0))/ 
--					(sum(decode(name,'db block gets',value,0))+ 
--		(sum(decode(name,'consistent gets',value,0))))) 
--		)* 100) "Buffer Hit Ratio" 
--from v$sysstat; 

column "logical_reads" format 99,999,999,999 
column "phys_reads" format 999,999,999 
column "phy_writes" format 999,999,999 
select a.value + b.value "logical_reads", 
		c.value   "phys_reads", 
		d.value   "phy_writes", 
		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, v$sysstat d 
where 
	a.statistic# = 37 
and 
	b.statistic# = 38 
and 
	c.statistic# = 39 
and 
	d.statistic# = 40;