惜分飞

从11.1开始,查询会话阻塞,V$Wait_Chains是一个非常好的视图,通过他可以快速找到阻塞源头.进行一个简单的测试
测试表create table t1 as select * from dba_objects;
会话1

SQL> delete from t1 where object_id>100 and object_id<10000;

9712 rows deleted.

会话2

SQL> delete from t1 where object_id<200;
---hang住

会话3

SQL> delete from t1 where object_id>88 and object_id<150;
--hang住

会话4进行查询分析

SQL> set lines 150
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30) FROM v$wait_chains; 

  CHAIN_ID NUM_WAITERS IN_WAIT_SECS OSID                      BLOCKER_OSID              SUBSTR(WAIT_EVENT_TEXT,1,30)
---------- ----------- ------------ ------------------------- ------------------------- ----------------------------------
         1           0          438 17072                     17005                     enq: TX - row lock contention
         1           1          483 17005                     16930                     enq: TX - row lock contention
         1           2          505 16930                                               SQL*Net message from client

SQL> set pages 1000
SQL>  set lines 120
SQL>  set heading off
SQL>  column w_proc format a50 tru
SQL>  column instance format a20 tru
SQL>  column inst format a28 tru
SQL>  column wait_event format a50 tru
SQL>  column p1 format a16 tru
SQL>  column p2 format a16 tru
SQL>  column p3 format a15 tru
SQL>  column Seconds format a50 tru
SQL>  column sincelw format a50 tru
SQL>  column blocker_proc format a50 tru
SQL>  column waiters format a50 tru
SQL>  column chain_signature format a100 wra
SQL>  column blocker_chain format a100 wra
SQL>  
SQL>  SELECT *
  2   FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
  3   'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
  4   ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
  5   'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
  6   'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
  7   'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
  8   '<none>',blocker_chain_id) blocker_chain
  9   FROM v$wait_chains wc,
 10   v$instance i
 11   WHERE wc.instance = i.instance_number (+)
 12   AND ( num_waiters > 0
 13   OR ( blocker_osid IS NOT NULL
 14   AND in_wait_secs > 10 ) )
 15   ORDER BY chain_id,
 16   num_waiters DESC)
 17   WHERE ROWNUM < 101;

Current Process: 16930                             SID orcl             INST #: 1
Blocking Process: <none> from Instance             Number of waiters: 2
Wait Event: SQL*Net message from client            P1: 1650815232   P2: 1            P3: 0
Seconds in Wait: 140                               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: <none>

Current Process: 17005                             SID orcl             INST #: 1
Blocking Process: 16930 from Instance 1            Number of waiters: 1
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 524290       P3: 8984
Seconds in Wait: 119                               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: <none>

Current Process: 17072                             SID orcl             INST #: 1
Blocking Process: 17005 from Instance 1            Number of waiters: 0
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 458768       P3: 8720
Seconds in Wait: 74                                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: <none>

SQL> set pages 1000
SQL> set lines 120
SQL> set heading off
SQL> column w_proc format a50 tru
SQL> column instance format a20 tru
SQL> column inst format a28 tru
SQL> column wait_event format a50 tru
SQL> column p1 format a16 tru
SQL> column p2 format a16 tru
SQL> column p3 format a15 tru
SQL> column Seconds format a50 tru
SQL> column sincelw format a50 tru
SQL> column blocker_proc format a50 tru
SQL> column fblocker_proc format a50 tru
SQL> column waiters format a50 tru
SQL> column chain_signature format a100 wra
SQL> column blocker_chain format a100 wra
SQL> SELECT *
  2  FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
  3   'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
  4   ' from Instance '||blocker_instance BLOCKER_PROC,
  5   'Number of waiters: '||num_waiters waiters,
  6   'Final Blocking Process: '||decode(p.spid,null,'<none>',
  7   p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
  8   'Program: '||p.program image,
  9   'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
 10   'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 11   'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 12   '<none>',blocker_chain_id) blocker_chain
 13  FROM v$wait_chains wc,
 14   gv$session s,
  15  gv$session bs,
 16   gv$instance i,
 17   gv$process p
 18  WHERE wc.instance = i.instance_number (+)
 19   AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
 20   and wc.sess_serial# = s.serial# (+))
 21   AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
 22   AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
 23   AND ( num_waiters > 0
 24   OR ( blocker_osid IS NOT NULL
 25   AND in_wait_secs > 10 ) )
 26  ORDER BY chain_id,
 27   num_waiters DESC)
 28  WHERE ROWNUM < 101;

Current Process: 16930                             SID orcl             INST #: 1
Blocking Process: <none> from Instance             Number of waiters: 2
Final Blocking Process: <none> from Instance       Program:
Wait Event: SQL*Net message from client            P1: 1650815232   P2: 1            P3: 0
Seconds in Wait: 177                               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: <none>

Current Process: 17005                             SID orcl             INST #: 1
Blocking Process: 16930 from Instance 1            Number of waiters: 1
Final Blocking Process: 16930 from Instance 1      Program: oracle@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3)
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 524290       P3: 8984
Seconds in Wait: 155                               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: <none>

Current Process: 17072                             SID orcl             INST #: 1
Blocking Process: 17005 from Instance 1            Number of waiters: 0
Final Blocking Process: 16930 from Instance 1      Program: oracle@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3)
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 458768       P3: 8720
Seconds in Wait: 110                               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: <none>

SQL> col SES for a15
SQL> set linesize 150
SQL> WITH BLOCKED AS
  2   (SELECT *
  3    FROM (SELECT INSTANCE,
  4                   SID,
  5                   SESS_SERIAL#,
  6                   BLOCKER_INSTANCE,
  7                   BLOCKER_SID,
  8                   BLOCKER_SESS_SERIAL#,
  9                   LEVEL LV,
 10                   NUM_WAITERS,
 11                   BLOCKER_CHAIN_ID
 12              FROM V$WAIT_CHAINS
 13            CONNECT BY PRIOR SID = BLOCKER_SID
 14                   AND PRIOR SESS_SERIAL# = BLOCKER_SESS_SERIAL#
 15                   AND PRIOR INSTANCE = BLOCKER_INSTANCE
 16             START WITH BLOCKER_IS_VALID = 'FALSE')
 17     WHERE NUM_WAITERS > 0
 18        OR BLOCKER_SID IS NOT NULL)
 19  SELECT INSTANCE,
 20         LPAD(' ', 2 * (LV - 1)) || B.SID SES,
 21         B.SESS_SERIAL#,
 22         B.BLOCKER_INSTANCE,
 23         B.BLOCKER_SID,
 24         B.BLOCKER_SESS_SERIAL#
 25    FROM BLOCKED B ;

  INSTANCE SES             SESS_SERIAL# BLOCKER_INSTANCE BLOCKER_SID BLOCKER_SESS_SERIAL#
---------- --------------- ------------ ---------------- ----------- --------------------
         1 42                      1819
         1   32                   52659                1          42                 1819
         1     39                 39865                1          32                52659

能够快速的定位到阻塞会话的源头,以及阻塞的级联关系