原文鏈接:https://www.modb.pro/db/21859
導讀: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)執行語句未提交或回滾:
<code>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)執行語句:/<code>
時間點2:
在實例2的會話3(INS2-session3)執行語句:
<code>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)執行語句:/<code>
時間點3:在實例2的會話4(INS2-session4)執行語句:
<code>select * from v$mystat where rownum = 1;update emp set sal = 15000 where empno = 7839;rollback;/<code>
時間點4:
在實例1的會話2(INS1-session2)執行語句:
<code>select * from v$mystat where rownum = 1;
update emp set job = 'CEO' where empno = 7839;
rollback;/<code>
此時可以看到,在後面3個時間點進行操作的會話均hang住,顯然都是被阻塞了。4個會話的現象如下:
那麼他們究竟都是被誰阻塞了呢?下文會詳細分析。
2.常規方法:梳理找出最終阻塞會話
我們常規會去GV$SESSION查詢blocking_session,再看這個blocking_session有沒有又被其他會話阻塞,直到找到根源。
<code>--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;/<code>
結果如下:
<code>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 >/<code>
可以看到實例1的sid=146的會話以及實例2的sid=145的會話都被實例2的sid=25的會話阻塞,而實例2的sid=25的這個會話又被實例1的sid=150的會話阻塞。這個例子只模擬了幾個會話尚且可以快速定位,但如果是真實故障,很可能受影響的不止這麼幾個會話,雖然也可以慢慢最終找出來,但畢竟會看的眼花繚亂是不是。我們高傲的DBA又怎麼會甘心一直去做這種事情呢?
3.改進方法:立即找出最終阻塞會話
之前我在單實例或者確認業務只跑在某一個節點的環境,一直在用的一個找出最終阻塞會話的腳本:
<code>--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;/<code>
這個腳本用到了start with connect by prior 的遞歸查詢用法,非常方便可以直接找出最終阻塞的會話;可如果是RAC,業務是負載均衡跑在多個節點的,那上面的這個腳本就不好用了,比如我上面構造的這個例子,就需要明確查出各個會話分別在哪個實例上,否則你怎麼確認去哪裡殺呢,怎麼辦呢?其實也簡單,只需要稍加改動下這個腳本即可,改後如下:
<code>--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;/<code>
結果如下:
<code>--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;/<code>
非常清晰可以看到最終阻塞其他會話的會話是實例1的sid=150,serial#=8742的會話。
那麼與相關人員都確認後,就可以直接殺掉這個最終阻塞會話:
<code>SYS@jyzhao1 >alter system kill session '150,8742' immediate;
System altered./<code>
再次查詢,恢復正常,不再有堵塞了:
<code>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 >/<code>
至此,就達到了我們在RAC環境中快速定位並殺掉這種最終阻塞會話的目的。
想了解更多關於數據庫、雲技術的內容嗎?
快來關注“數據和雲"、"雲和恩墨,"公眾號及"雲和恩墨"官方網站,我們期待大家一同學習與進步!
小程序”DBASK“在線問答,隨時解惑,歡迎瞭解和關注!
閱讀更多 數據和雲智能 的文章