Tuesday, November 20, 2007

Top timed events of a session

set linesize 130 pagesize 1000
column sid format 999999 truncated
column program format A40 truncated
column top_events format A50 truncated

with events as (select sid,time,sys_connect_by_path(text,' + ') top_eventsfrom (select sid,e.event,sum(time) over (partition by sid) time,dense_rank() over (partition by sid order by e.time desc) rank,time/sum(time) over (partition by sid) pct,count(*) over (partition by sid) cnt,to_char(round(100*e.time/sum(e.time) over (partition by sid)))'% 'e.event textfrom(select sid,event event,total_waits waits,time_waited/100 time from v$session_eventunion allselect sid,'CPU',null,value/100 from v$statname join v$sesstat using (statistic#)where name = 'CPU used by this session') ewhere time > 0) where rank=cntconnect by prior rank=rank-1 and prior sid=sid start with rank=1)select sid,v$session.program,substr(top_events,4,instr(top_events'+ 0%','+ 0%')-4) top_events, (block_gets+consistent_gets) logical_reads,round(pga_max_mem/1024) pga_kb from events join v$session using(sid) join v$sess_io using (sid) join v$process on (paddr=v$process.addr)order by block_gets+consistent_gets desc/

No comments: