track PGA

gistfile1.txt

select * from (
select grouping_id(module) g,dt, schemaname,machine,decode(grouping_id(module),1,'{------}',module) module, round(sum(pga_used_mem)/1024/1024) as mb_used, round(sum(pga_alloc_mem)/1024/1024) as mb_alloc,
count(*) CNT,count(case when logon_time > dt - interval '30' minute then 1 end) cnt_30min,
round(ratio_to_report(sum(pga_alloc_mem)) over(partition by dt,grouping_id(machine,module))*100) "%mem",
round(ratio_to_report(count(*)) over(partition by dt,grouping_id(machine,module))*100) "%cnt", round(sum(diff_alloc)/1024/1024) MB_grow,
count(*)-lag(count(*)) over (partition by schemaname,machine,module order by dt) ses_added
from (
select pga_alloc_mem-lag(pga_alloc_mem) over (partition by spid,sid,serial# order by dt) diff_alloc,
t.* from track_pga t 
)  
group by dt, schemaname,machine, rollup(module)
order by dt desc, g desc, mb_alloc desc
) where "%mem" >=2