使用v$wait_chains分析系统为何hang住

11g之前，可以使用hang analyze分析系统为何会hang住
11g之后，可以通过视图v$wait_chains诊断数据库hang和contention
在11gR1里，Oracle通过diag进程实现了一个功能，每隔3秒做一次本地的hang analyze，每隔10秒做一次global的hang analyze。收集的信息放在内存中的”hang analysis cache”区域。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

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 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,                'Wait Event: ' || wait_event_text wait_event,                'P1: ' || p1 p1,                'P2: ' || p2 p2,                'P3: ' || 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, v$instance i          WHERE wc.instance = i.instance_number(+)            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;

 

11gR2中，v$session视图中有一个字段叫final_blocking_session,这个字段能够去查看最上层的阻塞者

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

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;