oracle11.2版本后根据视图v$wait_chains快速定位阻塞者：使用如下sql查询可快速根据Final Blocking Process对应的spid结束其系统进程即可释放阻塞。
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;
测试过程：
会话1：建个测试表，并执行删除delete
[oracle@lnkf ~]$ sqlplus jyc/jyc
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 14 12:45:10 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
444
确认会话对应的spid系统进程号
SQL> select spid from v$process where addr in (select paddr from v$session where sid in(select userenv('sid') from dual));
SPID
------------------------
33754
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> set time on
12:57:58 SQL>
12:57:59 SQL> delete from t1 where object_id>100 and object_id<10000;
9710 rows deleted.
12:58:01 SQL>
会话2：会话1执行删除未提交，会话2也执行delete删除记录，此时hang住了
[oracle@lnkf ~]$ sqlplus jyc/jyc
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 14 12:58:09 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set time on
12:58:12 SQL> select spid from v$process where addr in (select paddr from v$session where sid in(select userenv('sid') from dual));
SPID
------------------------
33768
12:58:21 SQL> delete from t1 where object_id<300;
298 rows deleted.
13:02:33 SQL>
13:02:42 SQL>
13:03:08 SQL>
13:03:08 SQL>
13:03:09 SQL>
会话3：会话1执行删除未提交，会话3也执行delete删除记录，此时hang住了
[oracle@lnkf ~]$ sqlplus jyc/jyc
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 14 12:58:58 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set time on
12:59:07 SQL> select spid from v$process where addr in (select paddr from v$session where sid in(select userenv('sid') from dual));
SPID
------------------------
33771
12:59:15 SQL> delete from t1 where object_id>88 and object_id<150;
61 rows deleted.
13:03:36 SQL> 13:03:36 SQL> 13:03:36 SQL> 13:03:36 SQL>
13:03:38 SQL>
13:03:38 SQL>
13:03:39 SQL> rollback;
Rollback complete.
13:03:55 SQL>
会话4：查看阻塞：
[oracle@lnkf ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 14 13:00:29 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set time on
13:00:45 SQL> set pages 1000
13:00:48 SQL> set lines 120
13:00:48 SQL> set heading off
13:00:48 SQL> column w_proc format a50 tru
13:00:48 SQL> column instance format a20 tru
13:00:48 SQL> column inst format a28 tru
13:00:48 SQL> column wait_event format a50 tru
13:00:48 SQL> column p1 format a16 tru
13:00:48 SQL> column p2 format a16 tru
13:00:48 SQL> column p3 format a15 tru
13:00:48 SQL> column Seconds format a50 tru
13:00:48 SQL> column sincelw format a50 tru
13:00:48 SQL> column blocker_proc format a50 tru
13:00:48 SQL> column fblocker_proc format a50 tru
13:00:48 SQL> column waiters format a50 tru
13:00:48 SQL> column chain_signature format a100 wra
13:00:48 SQL> column blocker_chain format a100 wra
13:00:48 SQL>
13:00:48 SQL> SELECT *
13:00:48 2 FROM (SELECT 'Current Process: ' || osid W_PROC,
13:00:48 3 'SID ' || i.instance_name INSTANCE,
13:00:48 4 'INST #: ' || instance INST,
13:00:48 5 'Blocking Process: ' ||
13:00:48 6 decode(blocker_osid, null, '', blocker_osid) ||
13:00:48 7 ' from Instance ' || blocker_instance BLOCKER_PROC,
13:00:48 8 'Number of waiters: ' || num_waiters waiters,
13:00:48 9 'Final Blocking Process: ' ||
13:00:48 10 decode(p.spid, null, '', p.spid) || ' from Instance ' ||
13:00:48 11 s.final_blocking_instance FBLOCKER_PROC,
13:00:48 12 'Program: ' || p.program image,
13:00:48 13 'Wait Event: ' || wait_event_text wait_event,
13:00:48 14 'P1: ' || wc.p1 p1,
13:00:48 15 'P2: ' || wc.p2 p2,
13:00:48 16 'P3: ' || wc.p3 p3,
13:00:48 17 'Seconds in Wait: ' || in_wait_secs Seconds,
13:00:48 18 'Seconds Since Last Wait: ' || time_since_last_wait_secs sincelw,
13:00:48 19 'Wait Chain: ' || chain_id || ': ' || chain_signature chain_signature,
13:00:48 20 'Blocking Wait Chain: ' ||
13:00:48 21 decode(blocker_chain_id, null, '', blocker_chain_id) blocker_chain
13:00:48 22 FROM v$wait_chains wc,
13:00:48 23 gv$session s,
13:00:48 24 gv$session bs,
13:00:48 25 gv$instance i,
13:00:48 26 gv$process p
13:00:48 27 WHERE wc.instance = i.instance_number(+)
13:00:48 28 AND (wc.instance = s.inst_id(+) and wc.sid = s.sid(+) and
13:00:48 29 wc.sess_serial# = s.serial#(+))
13:00:48 30 AND (s.final_blocking_instance = bs.inst_id(+) and
13:00:48 31 s.final_blocking_session = bs.sid(+))
13:00:48 32 AND (bs.inst_id = p.inst_id(+) and bs.paddr = p.addr(+))
13:00:48 33 AND (num_waiters > 0 OR
13:00:49 34 (blocker_osid IS NOT NULL AND in_wait_secs > 10))
13:00:49 35 ORDER BY chain_id, num_waiters DESC)
13:00:49 36 WHERE ROWNUM < 101;
Current Process: 33754 SID lndb 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: 167 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:
Current Process: 33768 SID lndb INST #: 1
Blocking Process: 33754 from Instance 1 Number of waiters: 1
Final Blocking Process: 33754 from Instance 1 Program: oracle@lnkf (TNS V1-V3)
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 655367 P3: 9316779
Seconds in Wait: 134 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:
Current Process: 33771 SID lndb INST #: 1
Blocking Process: 33768 from Instance 1 Number of waiters: 0
Final Blocking Process: 33754 from Instance 1 Program: oracle@lnkf (TNS V1-V3)
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 589842 P3: 3699585
Seconds in Wait: 82 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:
可以看到Final Blocking Process: 33754是会话2最终阻塞了会话2和3
删除会话1对应的系统进程号33754,过几秒后会话2不再hang住。
13:00:49 SQL> !kill -9 33754
13:01:31 SQL>
13:02:00 SQL> /
Current Process: 33754 SID lndb 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: 239 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:
Current Process: 33768 SID lndb INST #: 1
Blocking Process: 33754 from Instance 1 Number of waiters: 1
Final Blocking Process: 33754 from Instance 1 Program: oracle@lnkf (TNS V1-V3)
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 655367 P3: 9316779
Seconds in Wait: 206 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:
Current Process: 33771 SID lndb INST #: 1
Blocking Process: 33768 from Instance 1 Number of waiters: 0
Final Blocking Process: 33754 from Instance 1 Program: oracle@lnkf (TNS V1-V3)
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 589842 P3: 3699585
Seconds in Wait: 154 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:
13:02:01 SQL> !ps -ef|grep 33754
oracle 33840 33792 0 13:02 pts/3 00:00:00 /bin/bash -c ps -ef|grep 33754
oracle 33842 33840 0 13:02 pts/3 00:00:00 grep 33754
13:02:19 SQL>
13:02:44 SQL>
13:02:45 SQL> /
Current Process: 33768 SID lndb INST #: 1
Blocking Process: from Instance Number of waiters: 1
Final Blocking Process: from Instance Program:
Wait Event: SQL*Net message from client P1: 1650815232 P2: 1 P3: 0
Seconds in Wait: 12 Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain:
Current Process: 33771 SID lndb INST #: 1
Blocking Process: 33768 from Instance 1 Number of waiters: 0
Final Blocking Process: 33768 from Instance 1 Program: oracle@lnkf (TNS V1-V3)
Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 589842 P3: 3699585
Seconds in Wait: 199 Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'
Blocking Wait Chain:
可以看到Final Blocking Process: 33768是会话2阻塞了会话3
13:02:46 SQL> !ps -ef|grep 33768
oracle 33768 33767 0 12:58 ? 00:00:00 oraclelndb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 33981 33792 0 13:03 pts/3 00:00:00 /bin/bash -c ps -ef|grep 33768
oracle 33983 33981 0 13:03 pts/3 00:00:00 grep 33768
删除会话2对应的系统进程号33768,过几秒后会话3不再hang住。
13:03:22 SQL> !kill -9 33768
13:03:29 SQL>
13:03:29 SQL>
13:03:30 SQL>
13:03:34 SQL>
13:03:35 SQL> /
no rows selected
13:03:49 SQL>
有时候上面也解决不了：
select spid from v$process where addr in (select paddr from v$session where sid in(select sid from v$access where object in('对象名')));
也看看有哪些事务：
select p.spid,s.machine,t.start_time,s.status,sa.sql_text
from v$process p,v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa
where p.addr=s.paddr and s.taddr=t.addr and t.xidusn=r.segment_id(+)
and s.sql_address=sa.address(+);
其它参考数据库层面删除会话：
查看阻塞：
select *
from (select a.inst_id, a.sid, a.serial#,
a.sql_id,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
level as tree_level
from gv$session a
start with a.blocking_session is not null
connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
where isleaf = 1
order by tree_level asc;
删kill会话：
alter system kill session 'sid,serial,@sid' immediate;
处理死锁参考：
SELECT d.spid,a.sid,b.status,e.event,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM V$SESS_IO A,V$SESSION B,V$SQL C,V$PROCESS D,v$session_wait e
WHERE A.SID=B.SID AND B.SQL_HASH_VALUE=C.HASH_VALUE and B.sid=E.sid
AND B.PADDR=D.ADDR and A.sid in(select sid from v$Lock where block=1);
查锁其它：
with b as
(select blocker_instance_id,
blocker_sid,
blocker_sess_serial#,
count(1) cnt
from gv$session_blockers b1
where not exists
(select 1 from gv$session_blockers b2
where b1.blocker_instance_id = b2.inst_id
and b1.blocker_sid = b2.sid
and b1.blocker_sess_serial# = b2.sess_serial#)
group by blocker_instance_id, blocker_sid, blocker_sess_serial#)
SELECT inst_id,
sid,
username,
serial#,
status,
machine,
program,
sql_id,
prev_sql_id,
event,
state,
round(wait_time_micro / 1000 / 1000, 1) wait_time,
final_blocking_instance,
final_blocking_session,
b.cnt
FROM gv$session g, b
where g.inst_id = b.blocker_instance_id
and g.sid = b.blocker_sid
and g.serial# = blocker_sess_serial#;
查看等待的会话参考：
SELECT vs.inst_id,vs.osuser,vw.event,vw.p1,vw.p2,vw.p3 ,vt.sql_text , vs.program
FROM gv$session_wait vw, gv$sqltext vt , gv$session vs
WHERE vw.event = 'PL/SQL lock timer'
AND vt.address=vs.sql_address
AND vs.inst_id = vw.inst_id
AND vs.sid = vw.sid;
最后修改时间：2024-01-02 14:52:02
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
