怎么可以定位oracle被谁占用,Oracle RAC环境下定位并杀掉最终阻塞的会话

实验环境：

Oracle RAC 11.2.0.4 (2节点)

之前其实也写过一篇相关文章：

但上文给出的例子过于简单，实际对于生产中复杂的阻塞问题，一步步找最终阻塞就比较麻烦。所以本篇旨在寻求更好更快捷的办法。

1.模拟故障：会话被级联阻塞

准备工作：

我这里在每个实例开两个会话来模拟RAC在负载均衡模式下的业务会话：

实例1：会话1，会话2;

实例2：会话3，会话4;

在 时间点1 -> 时间点2 -> 时间点3 -> 时间点4 的这个时间轴上分别执行以下操作：

时间点1：

在实例1的会话1(INS1-session1)执行语句未提交或回滚：

select * from v$mystat where rownum = 1;

update emp set sal = 8000 where empno = 7788;

时间点2：

在实例2的会话3(INS2-session3)执行语句：

select * from v$mystat where rownum = 1;

delete from emp where empno = 7839;

update emp set job = 'MANAGER' where empno = 7788;

rollback;

时间点3：

在实例2的会话4(INS2-session4)执行语句：

select * from v$mystat where rownum = 1;

update emp set sal = 15000 where empno = 7839;

rollback;

时间点4：

在实例1的会话2(INS1-session2)执行语句：

select * from v$mystat where rownum = 1;

update emp set job = 'CEO' where empno = 7839;

rollback;

此时可以看到，在后面3个时间点进行操作的会话均hang住，显然都是被阻塞了。4个会话的现象如下：

那么他们究竟都是被谁阻塞了呢？下文会详细分析。

2.常规方法：梳理找出最终阻塞会话我们常规会去GV$SESSION查询blocking_session，再看这个blocking_session有没有又被其他会话阻塞，直到找到根源。

--blocking

set lines 180

col program for a30

col machine for a20

select 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# EVENT                                    MACHINE              SQL_ID        BLOCKING_SESSION BLOCKING_INSTANCE

---------- ---------- ---------- ---------------------------------------- -------------------- ------------- ---------------- -----------------

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 >

可以看到实例1的sid=146的会话以及实例2的sid=145的会话都被实例2的sid=25的会话阻塞，而实例2的sid=25的这个会话又被实例1的sid=150的会话阻塞。这个例子只模拟了几个会话尚且可以快速定位，但如果是真实故障，很可能受影响的不止这么几个会话，虽然也可以慢慢最终找出来，但毕竟会看的眼花缭乱是不是。我们高傲的DBA又怎么会甘心一直去做这种事情呢？

3.改进方法：立即找出最终阻塞会话之前我在单实例或者确认业务只跑在某一个节点的环境，一直在用的一个找出最终阻塞会话的脚本：

--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, '

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;

这个脚本用到了start with connect by prior 的递归查询用法，非常方便可以直接找出最终阻塞的会话；可如果是RAC，业务是负载均衡跑在多个节点的，那上面的这个脚本就不好用了，比如我上面构造的这个例子，就需要明确查出各个会话分别在哪个实例上，否则你怎么确认去哪里杀呢，怎么办呢？其实也简单，只需要稍加改动下这个脚本即可，改后如下：

--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, ' 

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, ' 

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  

1        150       8742               SQL*Net message from client              INACTIVE          1  

1        150       8742               SQL*Net message from client              INACTIVE          1  

SYS@jyzhao1 >

非常清晰可以看到最终阻塞其他会话的会话是实例1的sid=150，serial#=8742的会话。

那么与相关人员都确认后，就可以直接杀掉这个最终阻塞会话：

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, ' 

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 >

至此，就达到了我们在RAC环境中快速定位并杀掉这种最终阻塞会话的目的。