-- name       : cache.sql
-- date       : 14-Nov-2001
-- Author     : Pentest Limited
-- Description: This script checks out various hit ratios and reports
--              on the SGA sizes and also checks out physical and 
--              memory sorts.
-- limitation : needs access to v$sysstat, v$parameter, v$rowcache
--              v$librarycache and v$sgastat
--
-- useage     : SQL> @cache

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

spool cache.lis

--
-- check cache hit ratio's
--

ttitle "SGA Report"

col name head "SGA Parameter" for a30
col value head "Value" for a30

select 	name name,
	value value
from	v$parameter
where	name in ('db_block_buffers','db_block_size',
	'shared_pool_size','sort_area_size');
--
-- buffer hit ratio should be > 95%, ora recomend 90% going from
-- 95% to 99% can yeild performance gains
--
doc
	The buffer cache hit ratio should be greater than 95% and increasing
	to nearer 99% can result in performance gains.
#

col hit_ratio head "Buffer Cache Hit Ratio" for 99.99

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 hit_ratio
from	v$sysstat;

--
-- should be above 95%
-- increasing the shared pool will move this up, but will be about 85%
-- on database start up
--

doc
	The dictionary cache should have a hit ratio greater than 95%. 
	Increasing the shared_pool_size will result in a higher hit ratio.
#

col hit_ratio head "Dictionary Cache" for 99.99

select	(1-(sum(getmisses)/sum(gets)))*100 hit_ratio
from	v$rowcache;

--
-- hit ratio should be over 95%, increasing the shared pool will
-- move this up, dont measure at startup
--

doc
	The library cache hit ratio should be greater than 95%. increasing the 
	share_pool_size will improve this hit ratio.
#

col hit_ratio head "Library Cache Hit Ratio" for 99.99

select sum(pins) / (sum(pins) + sum(reloads))*100 hit_ratio
from	v$librarycache;

--
-- check the amount of free memory in the shared pool
--
doc


	This scetion checks out the shared pool size, the free memory in the
	shared pool and the percentage of free memory.

#

col value for 999,999,999,999 head "shared pool size"
col bytes for 999,999,999,999 head "free bytes"
col pct_free for 99.99 head "Pct Free"

-- 
-- this script assumes the sizes are in bytes and not say 50M
--

select	to_number(v$parameter.value) value, 
	v$sgastat.bytes bytes,
	(v$sgastat.bytes/to_number(v$parameter.value))*100 pct_free
from	v$sgastat,v$parameter
where	v$sgastat.name='free memory'
and	v$parameter.name='shared_pool_size';

--
-- get disk reads and writes
--
doc


	This section shows the sort ratios and the amount of disk sorts and memory sorts.

#

select	a.value "disk sorts",
	b.value "memory sorts",
	(round(b.value)/decode((a.value+b.value),0,1,(a.value+b.value))*100) "pct memory sorts"
from	v$sysstat a,v$sysstat b
where	a.name='sorts (disk)'
and	b.name='sorts (memory)';

spool off

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