Oracle阻塞會話源頭查找-單機和RAC環境

• 2020 年 5 月 25 日
• 筆記
• ----111 Oracle Lock

在寫 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");

結果如下，紅色的是阻塞源頭:

【完】