Number of waiters: '||num_waiters waiters,
'Final Blocking Process: '||decode(p.spid,null,'',
p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
'Program: '||p.program image,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
gv$session s,
gv$session bs,
gv$instance i,
gv$process p
WHERE wc.instance = i.instance_number (+)
AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
and wc.sess_serial# = s.serial# (+))
AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;
Current Process: 12028 SID orcl INST #: 1
Blocking Process: from Instance Number of waiters: 2
Wait Event: SQL*Net message from client P1: 1650815232 P2: 1 P3: 0
Seconds in Wait: 1155 Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain:
Current Process: 12164 SID orcl INST #: 1
Blocking Process: 12028 from Instance 1 Number of waiters: 1
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 589825 P3: 2599
Seconds in Wait: 964 Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain:
Current Process: 12342 SID orcl INST #: 1
Blocking Process: 12164 from Instance 1 Number of waiters: 0
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 327708 P3: 2417
Seconds in Wait: 954 Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain:
Current Process: 12476 SID orcl INST #: 1
Blocking Process: from Instance Number of waiters: 1
Wait Event: SQL*Net message from client P1: 1650815232 P2: 1 P3: 0
Seconds in Wait: 578 Seconds Since Last Wait:
Wait Chain: 2: 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain:
Current Process: 12527 SID orcl INST #: 1
Blocking Process: 12476 from Instance 1 Number of waiters: 0
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 458753 P3: 2465
Seconds in Wait: 567 Seconds Since Last Wait:
Wait Chain: 2: 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain:
这里可以看到当前会话是2395在等待enq: TM – contention，而它的顶级阻塞者是2309。通过这些脚本我们能够方便的进行查询，能够方便的找到谁是阻塞者，甚至是最上层的阻塞者。当然在我们的diaghang.sql的脚本里面，我们看到了下列内容。这里Oracle通过我们内存直接访问，从x$ksdhng_chains里面把chain的信息全部获取出来，用于最终的hang分析的诊断。
-- dump hang analysis chains
oradebug direct_access enable trace
oradebug direct_access disable reply
oradebug direct_access set content_type = 'text/plain'
oradebug direct_access select * from x$ksdhng_chains
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (orcl.orcl)
os id: 13018
process id: 39, oracle@rhel5 (TNS V1-V3)
session id: 1
session serial #: 516
}
is waiting for 'enq: TX - row lo |