Oracle阻塞会话源头查找-单机和RAC环境

在写 Oracle session相关数据字典(一)  这篇文章时，提到使用v$session视图的树形查询可以得到Oracle锁树，这样就便于我们找出阻塞会话的源头，但是仅仅可以在单机环境中使用。今天把单机和RAC的阻塞树都整理了一下，话不多说，直接开干，奥利给。

（一）单机的阻塞会话树

单机的不再测试，直接搬以前的记录。EM里面的Blocking session里面看到下图，以折叠图的形式展现，最上面的代表正在阻塞其它session的会话，研究了一下，可以使用v$session来得到类似的效果。

                                                                  图1.blocking session树形图

我们来做一次测试，对表test01和test02进行DML操作，观察运行情况，操作如下

（1）创建测试表test01,test02。并在2个表的id列添加主键约束

         test01表                                                               test02表

（2）对2个表进行操作，执行顺序如下

----------------------------------------test01操作-------------------------------------------------------

[sessid:27  ]  delete from test01;                                          --删除test01整个表的数据，未提交，阻塞源头

[sessid:28  ]  update test01 set name = 'aaa' where id = 1;    --更新test01表id=1的行，会话产生等待

[sessid:142]   insert into test01 values(1,'abc');                     --插入test01表id=1的行，会话产生等待

[sessid:25 ]   delete from test01 where id = 2;                       --删除test01表id=2的行，会话产生等待

[sessid:29 ]   update test01 set  name = 'bbb' where id  = 2;  --更新test01表id=2的行，会话产生等待

-----------------------------------------test02操作--------------------------------------------------------

[sessid:30 ]  insert into test02 values(3,'augly',3000);          --插入test02表id=3的行，未提交，阻塞源头

[sessid:23 ]  insert into test02 values(3,'augly',3300);          --插入test02表id=3的行，会话产生等待

此时我们到EM里面观察，就会发现图1的blocking session树形图。

在实际环境中，大部分系统是没有安装EM的，在没有EM的情况下，我们依然可以通过v$session得到类似的图形

--使用v$session来查看单实例的阻塞session信息

SELECT   LPAD(' ',5*(LEVEL-1))||S."USERNAME" AS user_name , 
         LPAD(' ',5*(LEVEL-1))||S."SID" AS session_id,
         S."SERIAL#",
         S."SQL_ID",         S."ROW_WAIT_OBJ#",
         S."WAIT_CLASS",
         S."EVENT",
         S."P1",
         S."P2",
         S."P3",
         S."SECONDS_IN_WAIT"
FROM     V$SESSION S 
WHERE    S."BLOCKING_SESSION" IS NOT NULL
OR       S.SID IN(SELECT DISTINCT BLOCKING_SESSION FROM V$SESSION)
START WITH S."BLOCKING_SESSION" IS NULL
CONNECT BY PRIOR S."SID" = S."BLOCKING_SESSION";

结果如下，红色的为阻塞源头：

（二）RAC的阻塞会话树

RAC的阻塞会话，在EM里面是按照实例分开的，没有合并在一起，如下图：

我们尝试将两个图的结果合并在一起，测试如下：

（1）创建测试表，并添加主键

CREATE TABLE test01
(
   ID      NUMBER,
   NAME    VARCHAR(30),
   age     NUMBER
);
ALTER TABLE test01 ADD CONSTRAINT pk_id PRIMARY KEY(ID);

（2）往测试表里面写入数据，形成跨节点的会话阻塞，执行如下：

----------------------------------------test01操作：模拟跨节点阻塞--------------------------------------------

[节点1：会话36]    INSERT INTO test01 VALUES(1,'lijiaman',18);    --节点1插入数据，未提交，阻塞源头

[节点2：会话265]  INSERT INTO test01 VALUES(1,'xiaohua',19);    --节点2插入数据，主键相同，阻塞

[节点1：会话43]    INSERT INTO test01 VALUES(1,'xiaoli',20);        --节点1插入数据，同样被阻塞

----------------------------------------test01操作：模拟同一节点阻塞-------------------------------------------

[节点2：会话34]    INSERT INTO TEST01 VALUES (2, 'b', 18);          --节点2插入数据，未提交，阻塞源头

[节点2：会话275]  INSERT INTO TEST01 VALUES (2, 'c', 18);           --节点2插入数据，主键相同，阻塞

使用如下SQL查询RAC和单节点的会话阻塞：

--使用v$session来查看RAC数据库和单实例阻塞session信息

SELECT   
         LPAD(' ',5*(LEVEL-1))||S."USERNAME" ,  
         LPAD(' ',5*(LEVEL-1))||S."INST_ID"||','||S."SID" ,
         S."SERIAL#" ,
         S."SQL_ID",        
         S."ROW_WAIT_OBJ#",
         S."WAIT_CLASS",
         S."EVENT",
         S."P1",
         S."P2",
         S."P3",
         S."SECONDS_IN_WAIT",
         s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION"
FROM     GV$SESSION S 
WHERE    S."BLOCKING_SESSION" IS NOT NULL
OR       (S."INST_ID"||','||S."SID") IN(SELECT DISTINCT BLOCKING_INSTANCE||','||BLOCKING_SESSION FROM GV$SESSION)
START WITH  (s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION") = ','
CONNECT BY PRIOR (S."INST_ID"||','||S."SID") = (s."BLOCKING_INSTANCE"||','||s."BLOCKING_SESSION");

结果如下，红色的是阻塞源头:

【完】