-- name       : sysstat.sql
-- date       : 14-Nov-2001
-- Author     : Pentest Limited
-- Description: This script shows the three key SGA hit ratios
-- limitation : needs access to v$sysstat, v$rowcache
--              and v$librarycache
--
-- useage     : SQL> @sysstat

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

spool sysstat.lis

select (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;

select sum(gets),sum(getmisses),(1-(sum(getmisses)/
	(sum(gets) + sum(getmisses)))) *100 "Dictionary Hit Ratio"
from	v$rowcache;

select	sum(pins) "Executions", 
	sum(pinhits) "hits",
	((sum(pinhits) / sum(pins))*100) "Library PinHitRatio",
	sum(reloads) "Misses", 
	((sum(pins) / (sum(pins) + sum(reloads)))*100) "Library HitRatio"
from	v$librarycache;

spool off

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