| select
/*+ ordered / distinct /* 속도를 위해 v$sql을 조인할 경우 중복되는 레코드 제거
*/
s.sid SID, s.username, s.program, p.spid "OS-Pid",w.seconds_in_wait
as "W_time(Sec)",
decode(w.wait_time,0,'Wai-ting',
'Waited') Status, w.ename event,
--
p1text || ':' || decode(event,'latch free',p1raw, to_char(p1))
||','||
--
p2text || ':' || to_char(p2) ||','|| p3text || ':' || to_char(p3)
"Additional Info",
q.sql_text
from ( select a.*, decode(a.event,'latch free', 'latch free
(' ||b.name||')',
'row cache lock', 'row cache lock (' || c.parameter || ')',
'enqueue', 'enqueue ('||chr(bitand(p1, -16777216)/16777215)||
chr(bitand(p1,16711680)/65535)||':'||
decode(bitand(p1,65535),
1, 'N', 2, 'SS',3,'SX',4,'S',5,'SSX',6,'X') ||')',
a.event
) ename
from v$session_wait a, v$latchname b, v$rowcache c
where
a.p2 = b.latch#(+) and a.p1 = c.cache#(+) and c.type(+) =
'PARENT'
and
a.event not in ('rdbms ipc message','smon timer','pmon timer','slave
wait','pipe get','null event',
'SQL*Net
message from client', 'SQL*Net message to client','PX Idle
Wait',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'ges remote message', 'wakeup time manager', /* idle event
적절히 수정 */
'lock manager wait for remote message', 'single-task message')
) w, v$session
s, v$process p, v$sql q
where w.sid = s.sid and s.paddr = p.addr
and s.sql_hash_value = q.hash_value(+) and s.sql_address =
q.address(+)
order by w.ename;
|
Bookmark
