-- name       : buffer_cache.sql
-- date       : 14-Nov-2001
-- Author     : Pentest Limited
-- Description: Show the buffer cache hit ratio
-- limitation : needs access to v$sysstat
--
-- useage     : SQL> @buffer_cache

set pages 50
set feed off
set verify off
set linesize 200

spool buffer_cache.lis

col buffer_cache head "Buffer Cache Hit Ratio" for 99.99

select round(((1-(sum(decode(name,'physical reads',value,0))/ 
       (sum(decode(name,'db block gets',value,0))+
       (sum(decode(name,'consistent gets',value,0))))))
	*100),4) buffer_cache 
from v$sysstat;

spool off

set pages 24
set feed on
set verify on
set lines 80