Oracle RAC環境下如何定位並殺掉最終阻塞的會話
- 2020 年 3 月 11 日
- 筆記
導讀：Oracle RAC環境下定位並殺掉最終阻塞的會話，本文通過一個測試demo來具體介紹。
實驗環境： Oracle RAC 11.2.0.4 （2節點）
- 1.模擬故障：會話被級聯阻塞
- 2.常規方法：梳理找出最終阻塞會話
- 3.改進方法：立即找出最終阻塞會話
但上文給出的例子過於簡單，實際對於生產中複雜的阻塞問題，一步步找最終阻塞就比較麻煩。所以本篇旨在尋求更好更快捷的辦法。
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有沒有又被其他會話阻塞，直到找到根源。
--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 >--blockingSYS@jyzhao1 >set lines 180SYS@jyzhao1 >col program for a30SYS@jyzhao1 >col machine for a20SYS@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 blockingset lines 200 pages 100col tree for a30col event for a40select * 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;
這個腳本用到了start with connect by prior 的遞歸查詢用法，非常方便可以直接找出最終阻塞的會話；可如果是RAC，業務是負載均衡跑在多個節點的，那上面的這個腳本就不好用了，比如我上面構造的這個例子，就需要明確查出各個會話分別在哪個實例上，否則你怎麼確認去哪裡殺呢，怎麼辦呢？其實也簡單，只需要稍加改動下這個腳本即可，改後如下：
--cascade blocking@gv$sessionselect * 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$sessionSYS@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 >
非常清晰可以看到最終阻塞其他會話的會話是實例1的sid=150，serial#=8742的會話。 那麼與相關人員都確認後，就可以直接殺掉這個最終阻塞會話：
SYS@jyzhao1 >alter system kill session '150,8742' immediate;System altered.
再次查詢，恢復正常，不再有堵塞了：
SYS@jyzhao1 >--cascade blocking@gv$sessionSYS@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 >
至此，就達到了我們在RAC環境中快速定位並殺掉這種最終阻塞會話的目的。
墨天輪原文鏈接：https://www.modb.pro/db/21859（複製到瀏覽器中打開或者點擊左下角的「閱讀原文」）
