Wait가 가장 많은 사용자 및 해당 Sql 조회

-- wait가 가장 많은 사용자 조회

select se.sid, p.spid,ss.status, ss.machine, ss.program, ss.terminal,
        ss.username, ss.sql_hash_value, se.event, se.time_waited,
        so.block_gets, so.consistent_gets, so.physical_reads
from v$session_event se, v$session ss, v$process p, v$sess_io so
 where 1=1
and event like '%db file %'
and se.sid = ss.sid
and ss.paddr = p.addr
and ss.sid = so.sid
order by time_waited desc;
 
-- 해당 sql 조회
select *
from v$sqltext
where hash_value = '984118682'
order by piece;
 
-- wait를 발생시킨 세션과 이벤트 조회
select sid,event,p1,p2,p3,seconds_in_wait from v$session_wait
where event not like '%SQL*Net%'
and event not like '%timer%'
and event not like '%message%'
order by event;
 
select sid,event,p1,p2,p3,seconds_in_wait from v$session_wait
where event ='enqueue';
 
select event,count(*) from v$session_wait
group by event
order by count(*);

출처 : http://blog.naver.com/tkpolee/80010718510