11g之前，通常我們資料庫hang住了之後，我們會對資料庫做hang analyze來進行分析，在11g之後，我們可以透過一個新的檢視v$wait_chains來診斷資料庫hang和contention。在11gR1這個版本里面，Oracle透過diag程式實現了一個功能，每隔3秒做一次本地的hang analyze，每隔10秒做一次global的hang analyze。而這些資訊會存放在記憶體裡面，Oracle把這一塊記憶體稱作”hang analysis cache”。而這一部分記憶體資訊，對我們資料庫診斷hang和contention起著非常重要的作用。而資料庫還有一些特性及工具也需要使用這塊記憶體區域。比如Hang Management, Resource Manager Idle Blocker Kill, SQL Tune Hang Avoidance和pmon清除，還有一些外部工具如Procwatcher。

因為在11gR2的v$session檢視中有一個欄位叫final_blocking_session,這個欄位能夠去檢視最上層的阻塞者。最終的blocker一般都處於wait_chain的頂端。這樣的session才會引起問題。我們先來看看普通的查詢.首先隨便製造兩個session共同更新一行的情況。

SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30) FROM v$wait_chains;

set pages 1000

set lines 120

set heading off

column w_proc format a50 tru

column instance format a20 tru

column inst format a28 tru

column wait_event format a50 tru

column p1 format a16 tru

column p2 format a16 tru

column p3 format a15 tru

column Seconds format a50 tru

column sincelw format a50 tru

column blocker_proc format a50 tru

column fblocker_proc format a50 tru

column waiters format a50 tru

column chain_signature format a100 wra

column blocker_chain format a100 wra

SELECT *

FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,

'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||

' from Instance '||blocker_instance BLOCKER_PROC,

'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;

使用final_blocking_session欄位，能查到最上端的阻塞程式。