-- name       : sga_sizing.sql
-- date       : 14-Nov-2001
-- Author     : Pentest Limited
-- Description: Show details of the SGA sizing
-- limitation : needs access to v$parameter and v$sgastat
--
-- useage     : SQL> @sga_sizing

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


col sga_size  head "SGA Size" for 9,999.99
col sga_used  head "Used" for 9,999.99
col sga_avail head "Available" for 9,999.99
col sga_pct   head "Pct Used" for 999.99

spool sga_sizing.lis

select 	max(b.value)/(1024*1024) sga_size,
	sum(a.bytes)/(1024*1024) sga_used, 
      	(max(b.value)/(1024*1024))-(sum(a.bytes)/(1024*1024)) sga_avail,
	(sum(a.bytes)/max(b.value))*100 sga_pct
from 	v$sgastat a, 
	v$parameter b 
where 	a.name in ('reserved stopper','table definiti','dictionary cache',
		'library cache','sql area','PL/SQL DIANA','SEQ S.O.') 
and	b.name='shared_pool_size';

spool off

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