Oracle RAC环境下定位并杀掉最终阻塞的会话方法是什么

2025-03-14 技术教程

本篇内容介绍了“Oracle RAC环境下定位并杀掉最终阻塞的会话方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

实验环境：
Oracle RAC 11.2.0.4 （2节点）

1.模拟故障：会话被级联阻塞

2.常规方法：梳理找出最终阻塞会话

3.改进方法：立即找出最终阻塞会话

之前其实也写过一篇相关文章：

如何定位Oracle数据库被锁阻塞会话的根源

1.模拟故障：会话被级联阻塞

准备工作：
我这里在每个实例开两个会话来模拟RAC在负载均衡模式下的业务会话：
实例1：会话1，会话2;
实例2：会话3，会话4;
在时间点1 -> 时间点2 -> 时间点3 -> 时间点4 的这个时间轴上分别执行以下操作：

时间点1：
在实例1的会话1（INS1-session1）执行语句未提交或回滚：

select*fromv$mystatwhererownum=1;updateempsetsal=8000whereempno=7788;

时间点2：
在实例2的会话3（INS2-session3）执行语句：

select*fromv$mystatwhererownum=1;deletefromempwhereempno=7839;updateempsetjob='MANAGER'whereempno=7788;rollback;

时间点3：
在实例2的会话4（INS2-session4）执行语句：

select*fromv$mystatwhererownum=1;updateempsetsal=15000whereempno=7839;rollback;

时间点4：
在实例1的会话2（INS1-session2）执行语句：

select*fromv$mystatwhererownum=1;updateempsetjob='CEO'whereempno=7839;rollback;

此时可以看到，在后面3个时间点进行操作的会话均hang住，显然都是被阻塞了。4个会话的现象如下：

那么他们究竟都是被谁阻塞了呢？下文会详细分析。

2.常规方法：梳理找出最终阻塞会话

我们常规会去GV$SESSION查询blocking_session，再看这个blocking_session有没有又被其他会话阻塞，直到找到根源。

--blockingsetlines180colprogramfora30colmachinefora20selectinst_id,SID,SERIAL#,event,machine,sql_id,blocking_session,blocking_instancefromgv$sessionwhereblocking_sessionisnotnull;

结果如下：

SYS@jyzhao1>--blockingSYS@jyzhao1>setlines180SYS@jyzhao1>colprogramfora30SYS@jyzhao1>colmachinefora20SYS@jyzhao1>selectinst_id,2SID,3SERIAL#,4event,5machine,6sql_id,7blocking_session,8blocking_instance9fromgv$session10whereblocking_sessionisnotnull;INST_IDSIDSERIAL#EVENTMACHINESQL_IDBLOCKING_SESSIONBLOCKING_INSTANCE----------------------------------------------------------------------------------------------------------------------------------------11466283enq:TX-rowlockcontentionjyrac1052gy77vp276s25222510250enq:TX-rowlockcontentionjyrac23t2npbvdcf2d21501214532069enq:TX-rowlockcontentionjyrac20ct116qw46shq252SYS@jyzhao1>

可以看到实例1的sid=146的会话以及实例2的sid=145的会话都被实例2的sid=25的会话阻塞，而实例2的sid=25的这个会话又被实例1的sid=150的会话阻塞。这个例子只模拟了几个会话尚且可以快速定位，但如果是真实故障，很可能受影响的不止这么几个会话，虽然也可以慢慢最终找出来，但毕竟会看的眼花缭乱是不是。我们高傲的DBA又怎么会甘心一直去做这种事情呢？

3.改进方法：立即找出最终阻塞会话

之前我在单实例或者确认业务只跑在某一个节点的环境，一直在用的一个找出最终阻塞会话的脚本：

--cascadeblockingsetlines200pages100coltreefora30coleventfora40select*from(selecta.sid,a.serial#,a.sql_id,a.event,a.status,connect_by_isleafasisleaf,sys_connect_by_path(SID,'<-')tree,levelastree_levelfromv$sessionastartwitha.blocking_sessionisnotnullconnectbynocyclea.sid=priora.blocking_session)whereisleaf=1orderbytree_levelasc;

这个脚本用到了start with connect by prior 的递归查询用法，非常方便可以直接找出最终阻塞的会话；可如果是RAC，业务是负载均衡跑在多个节点的，那上面的这个脚本就不好用了，比如我上面构造的这个例子，就需要明确查出各个会话分别在哪个实例上，否则你怎么确认去哪里杀呢，怎么办呢？其实也简单，只需要稍加改动下这个脚本即可，改后如下：

--cascadeblocking@gv$sessionselect*from(selecta.inst_id,a.sid,a.serial#,a.sql_id,a.event,a.status,connect_by_isleafasisleaf,sys_connect_by_path(a.SID||'@'||a.inst_id,'<-')tree,levelastree_levelfromgv$sessionastartwitha.blocking_sessionisnotnullconnectby(a.sid||'@'||a.inst_id)=prior(a.blocking_session||'@'||a.blocking_instance))whereisleaf=1orderbytree_levelasc;

结果如下：

SYS@jyzhao1>--cascadeblocking@gv$sessionSYS@jyzhao1>select*2from(selecta.inst_id,a.sid,a.serial#,3a.sql_id,4a.event,5a.status,6connect_by_isleafasisleaf,7sys_connect_by_path(a.SID||'@'||a.inst_id,'<-')tree,8levelastree_level9fromgv$sessiona10startwitha.blocking_sessionisnotnull11connectby(a.sid||'@'||a.inst_id)=prior(a.blocking_session||'@'||a.blocking_instance))12whereisleaf=113orderbytree_levelasc;INST_IDSIDSERIAL#SQL_IDEVENTSTATUSISLEAFTREETREE_LEVEL---------------------------------------------------------------------------------------------------------------------------------------------11508742SQL*NetmessagefromclientINACTIVE1<-25@2<-150@1211508742SQL*NetmessagefromclientINACTIVE1<-145@2<-25@2<-150@1311508742SQL*NetmessagefromclientINACTIVE1<-146@1<-25@2<-150@13SYS@jyzhao1>

非常清晰可以看到最终阻塞其他会话的会话是实例1的sid=150，serial#=8742的会话。
那么与相关人员都确认后，就可以直接杀掉这个最终阻塞会话：

SYS@jyzhao1>altersystemkillsession'150,8742'immediate;Systemaltered.

再次查询，恢复正常，不再有堵塞了：

“Oracle RAC环境下定位并杀掉最终阻塞的会话方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！