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