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 se.event like '%db file %'
and se.sid = ss.sid 
and ss.paddr = p.addr 
and ss.sid = so.sid 
order by se.time_waited desc;    

-- 해당 sql 조회 
select * 
from v$sqltext 
where hash_value = '3846591444'
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(*);