导读:Oracle RAC环境下定位并杀掉最终阻塞的会话,本文通过一个测试demo来具体介绍。
1.模拟故障:会话被级联阻塞
2.常规方法:梳理找出最终阻塞会话
3.改进方法:立即找出最终阻塞会话
select * from v$mystat where rownum = 1;update emp set sal = 8000 where empno = 7788;
select * from v$mystat where rownum = 1;delete from emp where empno = 7839;update emp set job = 'MANAGER' where empno = 7788;rollback;
select * from v$mystat where rownum = 1;
update emp set sal = 15000 where empno = 7839;
rollback;
select * from v$mystat where rownum = 1;
update emp set job = 'CEO' where empno = 7839;
rollback;
--blockingset lines 180col program for a30col machine for a20select inst_id, SID, SERIAL#, event, machine, sql_id, blocking_session, blocking_instance from gv$session where blocking_session is not null;
SYS@jyzhao1 >--blocking
SYS@jyzhao1 >set lines 180
SYS@jyzhao1 >col program for a30
SYS@jyzhao1 >col machine for a20
SYS@jyzhao1 >select inst_id,
2 SID,
3 SERIAL
4 event,
5 machine,
6 sql_id,
7 blocking_session,
8 blocking_instance
9 from gv$session
10 where blocking_session is not null;
INST_ID SID SERIAL
---------- ---------- ---------- ---------------------------------------- -------------------- ------------- ---------------- -----------------
1 146 6283 enq: TX - row lock contention jyrac1 052gy77vp276s 25 2
2 25 10250 enq: TX - row lock contention jyrac2 3t2npbvdcf2d2 150 1
2 145 32069 enq: TX - row lock contention jyrac2 0ct116qw46shq 25 2
SYS@jyzhao1 >
--cascade blocking
set lines 200 pages 100
col tree for a30
col event for a40
select *
from (select a.sid, a.serial#,
a.sql_id,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(SID, '<-') tree,
level as tree_level
from v$session a
start with a.blocking_session is not null
connect by nocycle a.sid = prior a.blocking_session)
where isleaf = 1
order by tree_level asc;
--cascade blocking@gv$session
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;
SYS@jyzhao1 >--cascade blocking@gv$session
SYS@jyzhao1 >select *
2 from (select a.inst_id, a.sid, a.serial#,
3 a.sql_id,
4 a.event,
5 a.status,
6 connect_by_isleaf as isleaf,
7 sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
8 level as tree_level
9 from gv$session a
10 start with a.blocking_session is not null
11 connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
12 where isleaf = 1
13 order by tree_level asc;
INST_ID SID SERIAL# SQL_ID EVENT STATUS ISLEAF TREE TREE_LEVEL
---------- ---------- ---------- ------------- ---------------------------------------- -------- ---------- ------------------------------ ----------
1 150 8742 SQL*Net message from client INACTIVE 1 <- 25@2 <- 150@1 2
1 150 8742 SQL*Net message from client INACTIVE 1 <- 145@2 <- 25@2 <- 150@1 3
1 150 8742 SQL*Net message from client INACTIVE 1 <- 146@1 <- 25@2 <- 150@1 3
SYS@jyzhao1 >
SYS@jyzhao1 >alter system kill session '150,8742' immediate;
System altered.
SYS@jyzhao1 >--cascade blocking@gv$session
SYS@jyzhao1 >select *
2 from (select a.inst_id, a.sid, a.serial#,
3 a.sql_id,
4 a.event,
5 a.status,
6 connect_by_isleaf as isleaf,
7 sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
8 level as tree_level
9 from gv$session a
10 start with a.blocking_session is not null
11 connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
12 where isleaf = 1
13 order by tree_level asc;
no rows selected
SYS@jyzhao1 >
联系客服