Saturday, March 31, 2012

How to find out top wait events for active sessions????

col sid format 999999
col username format a12
col event format a45
col Sec_wait format 999999
col total_waits format 999999
col time_wait_sec format 999999

select s.sid,s.username, se.event, s.seconds_in_wait Sec_Wait, total_waits,time_waited/100 time_wait_sec from v$session_event se, v$session s where s.sid=se.sid and s.status = 'ACTIVE' and s.username is not null and s.seconds_in_wait > 2 and se.time_waited > 100 and se.event not in ('SQL*Net message from client','SQL*Net message to client','events in waitclass Other') order by s.seconds_in_wait, s.sid,s.serial#, se.time_waited desc, se.total_waits desc;

No comments:

Post a Comment