oracle查询blocking session阻塞情况

    最近生产环境出现大量的由library cache lock和cursor: pin S wait on X这两个等待事件组成的阻塞会话，现场通过kill大量blocking session解决了此问题。由于当时没有及时做errorstack以及systemstat dump，根据mos上troubleshooting的文章去排查并没有发现上面列出的类似的情况而导致此问题。这里梳理了一下查看blocking session阻塞源头以及阻塞的层级关系的SQL。 1.no-rac

select a.*,
       row_number() over(partition by blocking_source order by "LEVEL" asc) as rn
  from (select *
          from (select lpad(' ', (level - 1) * 2, ' ') || sid as sid,
                       serial#,
                       sql_id,
                       paddr,
                       username,
                       event,
                       machine,
                       program,
                       SUBSTR(numtodsinterval(seconds_in_wait, 'second'),
                              12,
                              8) as wait_time,
                       wait_class#,
                       level,
                       blocking_session,
                       (prior sid) as blocking_sid,
                       (prior serial#) as blocking_serial#,
                       (prior sql_id) as blocking_sql_id,
                       sys_connect_by_path(sid, '->') as blocking_path,
                       connect_by_root(sid) as blocking_source
                  from v$session
                 start with blocking_session is null
                connect by (prior sid) = blocking_session)
         where "LEVEL" > 1
        union
        select to_char(sid) as sid,
               serial#,
               sql_id,
               paddr,
               username,
               event,
               machine,
               program,
               SUBSTR(numtodsinterval(seconds_in_wait, 'second'), 12, 8) as wait_time,
               wait_class#,
               1,
               null,
               null,
               null,
               null,
               null,
               sid
          from v$session
         where sid in (select blocking_source
                         from (select wait_class#,
                                      username,
                                      level,
                                      connect_by_root(sid) as blocking_source
                                 from v$session
                                start with blocking_session is null
                               connect by (prior sid) = blocking_session)
                        where "LEVEL" > 1)) a

2.rac

select a.*,
       row_number() over(partition by blocking_source order by "LEVEL" asc) as rn
  from (select *
          from (select  inst_id,
                       lpad(' ', (level - 1) * 2, ' ') || sid as sid,
                       serial#,
                       sql_id,
                       paddr,
                       username,
                       event,
                       machine,
                       program,
                       SUBSTR(numtodsinterval(seconds_in_wait, 'second'),
                              12,
                              8) as wait_time,
                       wait_class#,
                       level,
                       blocking_session,
                       (prior sid) as blocking_sid,
                       (prior serial#) as blocking_serial#,
                       (prior sql_id) as blocking_sql_id,
                       sys_connect_by_path(sid, '->') as blocking_path,
                       connect_by_root(sid) as blocking_source
                  from gv$session
                 start with blocking_session is null
                connect by (prior sid) = blocking_session)
         where "LEVEL" > 1
        union
        select  inst_id,
               to_char(sid) as sid,
               serial#,
               sql_id,
               paddr,
               username,
               event,
               machine,
               program,
               SUBSTR(numtodsinterval(seconds_in_wait, 'second'), 12, 8) as wait_time,
               wait_class#,
               1,
               null,
               null,
               null,
               null,
               null,
               sid
          from gv$session
         where sid in (select blocking_source
                         from (select wait_class#,
                                      username,
                                      level,
                                      connect_by_root(sid) as blocking_source
                                 from gv$session
                                start with blocking_session is null
                               connect by (prior sid) = blocking_session)
                        where "LEVEL" > 1)) a