–ct1.sql
— check the various SGA and PGA/UGA areas
— VERSION FOR CONTINUOUS SPOOLING

set pause off feed off pages 90 timi off
col pga_mem form 9999,999,999
col uga_mem form 9999,999,999
col pga_stat_name form a38 wrap
col pga_stat_val form 999,999,999,999
–spool c1.lst

select to_char(sysdate,’DD-MON-YY HH24:MI:SS’) date_time
,count(*) sess from v$session
/
prompt sga
select name,block_size,(sum(buffers)*8)/1024 buffer_mb
from v$buffer_pool
group by name,block_size
/
prompt pga_mem
select sum( s.value) pga_mem
from v$statname n, v$sesstat s
where n.statistic# = s.statistic#
and n.name in (‘session pga memory’);

prompt uga_mem
select sum(s.value) uga_mem
from v$statname n, v$sesstat s
where n.statistic# = s.statistic#
and n.name in (‘session uga memory’);

prompt pga_stats
select name pga_stat_name,value,unit from
v$pgastat
/

prompt sqlworkareaactive
select * from
v$sql_workarea_active
where WORK_AREA_SIZE- EXPECTED_SIZE>54857600;

prompt v$process_sum
select sum(PGA_USED_MEM), sum(PGA_ALLOC_MEM) , sum(PGA_FREEABLE_MEM)
from v$process;

prompt
select PID,PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM from
v$process order by PGA_ALLOC_MEM;

prompt v$processmemdetail
select * from v$process_memory_detail;

–spool off
— eof