loop and check pool usage.sql
–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
Discussion ¬