Oracle查询阻塞会话

查询当前活动会话的阻塞源：

set HEADING ON;
select WAIT_EVENT_TEXT,BLOCKER_INSTANCE_ID,BLOCKER_SID ,count(*) SESSION_COUNT
from gv$session_blockers group by WAIT_EVENT_TEXT,BLOCKER_INSTANCE_ID,BLOCKER_SID;

以上查询，能够找到阻塞当前阻塞的会话有哪些，并且排除了重复的会话：

WAIT_EVENT_TEXT                                                  BLOCKER_INSTANCE_ID BLOCKER_SID   COUNT(*)
---------------------------------------------------------------- ------------------- ----------- ----------
enq: TX - row lock contention                                                      1          50          2

查询阻塞源的状态：

SET HEADING ON;
col username format a20
col machine format a30
col terminal format a30
col program format a40
col event for a40
set linesize 300
select BLOCKING_SESSION,INST_ID,SID,SERIAL#,STATUS,EVENT,WAIT_TIME,LAST_CALL_ET,SQL_ID,TO_CHAR(SQL_EXEC_START, 'YYYY-MM-DD HH24:MI:SS') SQL_EXEC_START
From gv$session start with inst_id =1 and sid = 50 connect by (prior BLOCKING_SESSION)=sid;

这里用到了递归查询的方式，不用在代码中写一个递归函数，去一层层的网下查，直到找到最内层的阻塞源。
使用start with … connect … 的sql 语法，就能方便的完成递归查询了。

BLOCKING_SESSION    INST_ID        SID    SERIAL# STATUS   EVENT                                     WAIT_TIME LAST_CALL_ET SQL_ID        SQL_EXEC_START
---------------- ---------- ---------- ---------- -------- ---------------------------------------- ---------- ------------ ------------- -------------------
                          1         50      17848 INACTIVE SQL*Net message from client                       0          111

解法：

alter system kill session 'sid,serial#';

找到了阻塞源，就需要对阻塞源进行简单的自动判断，目前主要分为两种情况

1，空闲的等待：

如果 EVENT == SQL*Net message from client
并且 STATUS == INACTIVE
就认为这个一个空闲的等待造成的阻塞，根据LAST_CALL_ET的结果，距离现在大于30分钟了，就提示考虑可以直接kill掉这个会话。

2，sql等待
如果 SQL_ID 不为空
SQL_EXEC_START 不为空
就认为这是一个sql导致的阻塞，可以根据sql id 查询到这条sql具体在执行什么操作：

select SQL_FULLTEXT from V$SQL where sql_id='xxxx';

然后在人工判断是否要继续等待下去，后者手动kill会话。

当然脚本也提供了一个基准的判断，可以传递一个时间参数，比如大于24小时，不管是什么sql都先给他kill了。

3，其他等待
除了以上两种之外的等待事件，这个就需要人工判别了，情况多样，自动化的处理可能不太好覆盖。

案例：

制造环境测试

1.空闲等待：
session1:

create table T1 as select object_name,owner,object_id from dba_objects where rownum <=10;
update T1 set object_name='TEST' where object_id=3;

session2:

delete from sys.T1 where object_id=3;

2，sql 等待：
制造一个大表，几千万行数据

session1(T4有几千万行数据)

insert into T3 SELECT * From T4;

session2

update T3 set object_name='TEST' where object_id=3;

3，制造3层的等待：
session1:

update T1 set object_name='TEST' where object_id=3;

session2:

begin
update T2 set object_name='TEST' where object_id=3;
update T1 set object_name='TEST' where object_id=3;
end;
/

session3:

update sys.T2 set object_name='TEST' where object_id=3;