最近生产环境出现大量的由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