终的blocker一般都处于wait_chain的顶端。这样的session才会引起问题。我们先来看看普通的查询.首先随便制造两个session共同更新一行的情况。
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30)
2 FROM v$wait_chains;
CHAIN_ID NUM_WAITERS IN_WAIT_SECS OSID BLOCKER_OSID SUBSTR(WAIT_EVENT_TEXT,1,30)
---------- ----------- ------------ ------------------------- ------------------------- ------------------------------------------------------------
1 0 8 31377 31447 enq: TX - row lock contention
1 1 15 31447 SQL*Net message from client
通用的查询:
接下来在执行下一个基础的格式化后的脚本.
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;
最终结果如下图所示，我们能够清楚的看到进程12476阻塞了进程13018。进程13018在等待enq: TX – row lock contention。
Current Process: 12476 SID orcl INST #: 1
Blocking Process: from Instance
Number of waiters: 2
Final Blocking Process: from Instance
Program:
Wait Event: SQL*Net message from client P1: 1650815232 P2: 1 P3: 0
Seconds in Wait: 2503
Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain:
Current Process: 13018 SID orcl INST #: 1
Blocking Process: 12476 from Instance 1
Number of waiters: 0
Final Blocking Process: 12476 from Instance 1
Program: oracle@rhel5 (TNS V1-V3)
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 458753 P3: 2465
Seconds in Wait: 441
Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain:
使用final_blocking_session字段，能查到最上端的阻塞进程。
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,
' |