V$Wait_Chains定位阻塞源头
惜分飞发表于 2023-04-13 12:50:09
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
从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
能够快速的定位到阻塞会话的源头,以及阻塞的级联关系