Find_Blocker.sql–查询数据库中阻塞关系

昨日在线上分享案例时,一个朋友询问了一个问题,等待Cursor Pin s wait on X的会话是否可以Kill掉。当数据库发生严重的等待时,我一般常用的一个脚本如下:

with blocked as
  ( SELECT  *
  FROM 
    ( SELECT  instance, sid, sess_serial#, blocker_instance, blocker_sid, blocker_sess_serial#, WAIT_EVENT_TEXT, level lv, num_waiters, blocker_chain_id
    FROM v$wait_chains connect by PRIOR sid = blocker_sid
        AND PRIOR sess_serial# = blocker_sess_serial#
        AND PRIOR INSTANCE = BLOCKER_INSTANCE START
    WITH blocker_is_valid = 'FALSE')
    WHERE num_waiters > 0
        OR blocker_sid is NOT null) SELECT  s.username, b.instance, b.ses, b.sess_serial#, b.wait_event_text
  FROM 
  ( SELECT  instance, lpad(' ', 2 * (lv - 1)) || b.sid ses, b.sess_serial#, WAIT_EVENT_TEXT, b.blocker_instance, b.blocker_sid, b.blocker_sess_serial#
  FROM blocked b) b, gv$session s
WHERE s.sid=b.ses
    AND s.serial#=b.sess_serial#;

通过此脚本可以实现如下效果,可以确认当前数据库中的阻塞Chains,大多数阻塞类等待均可以找到源头,该SQL是利用11g中v$wait_chains的视图中BLOCKER_SID进行树形查询,我个人在诊断数据库性能时经常用到,原SQL出自于《深入理解 Oracle RAC 12c》中,
我稍作了修改,进行添加显示event与username的修改,方便从阻塞关系中明确等待事件之间的联系。

find_blocker

 

本代码我也分享在了我的github上,链接如下:

https://github.com/Leungalbert/Troubleshooting/blob/master/Find_Blocker