Oracle RAC環(huán)境下定位并殺掉最終阻塞的會話方法是什么

本篇內(nèi)容介紹了“Oracle RAC環(huán)境下定位并殺掉最終阻塞的會話方法是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

實驗環(huán)境：
Oracle RAC 11.2.0.4 （2節(jié)點）

• 1.模擬故障：會話被級聯(lián)阻塞

• 2.常規(guī)方法：梳理找出最終阻塞會話

• 3.改進方法：立即找出最終阻塞會話

之前其實也寫過一篇相關文章：

• 如何定位Oracle數(shù)據(jù)庫被鎖阻塞會話的根源

1.模擬故障：會話被級聯(lián)阻塞

準備工作：
我這里在每個實例開兩個會話來模擬RAC在負載均衡模式下的業(yè)務會話：
實例1：會話1，會話2;
實例2：會話3，會話4;
在 時間點1 -> 時間點2 -> 時間點3 -> 時間點4 的這個時間軸上分別執(zhí)行以下操作：

時間點1：
在實例1的會話1（INS1-session1）執(zhí)行語句未提交或回滾：

select * from v$mystat where rownum = 1;
update emp set sal = 8000 where empno = 7788;

時間點2：
在實例2的會話3（INS2-session3）執(zhí)行語句：

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）執(zhí)行語句：

select * from v$mystat where rownum = 1;
update emp set sal = 15000 where empno = 7839;
rollback;

時間點4：
在實例1的會話2（INS1-session2）執(zhí)行語句：

select * from v$mystat where rownum = 1;
update emp set job = 'CEO' where empno = 7839;
rollback;

此時可以看到，在后面3個時間點進行操作的會話均hang住，顯然都是被阻塞了。4個會話的現(xiàn)象如下：




那么他們究竟都是被誰阻塞了呢？下文會詳細分析。

2.常規(guī)方法：梳理找出最終阻塞會話

我們常規(guī)會去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.改進方法：立即找出最終阻塞會話

之前我在單實例或者確認業(yè)務只跑在某一個節(jié)點的環(huán)境，一直在用的一個找出最終阻塞會話的腳本：

--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;

這個腳本用到了start with connect by prior 的遞歸查詢用法，非常方便可以直接找出最終阻塞的會話；可如果是RAC，業(yè)務是負載均衡跑在多個節(jié)點的，那上面的這個腳本就不好用了，比如我上面構造的這個例子，就需要明確查出各個會話分別在哪個實例上，否則你怎么確認去哪里殺呢，怎么辦呢？其實也簡單，只需要稍加改動下這個腳本即可，改后如下：

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

非常清晰可以看到最終阻塞其他會話的會話是實例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, ' <- ') 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 >

“Oracle RAC環(huán)境下定位并殺掉最終阻塞的會話方法是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注億速云網(wǎng)站，小編將為大家輸出更多高質量的實用文章！