ash-дерево блокировок (09bcaa)

ASH. Получаем дерево блокировок сессий в рамках одного сэмпла

Порой необходимо найти кто именно был инициатором блокировок на инстанции в прошлом.

Так как поля Final blocking session в ASH отчете нет. То действуем следующим образом. Берем инетересующий нас промежуток и событие

SELECT sample_id,event,count(1) 
FROM   dba_hist_active_sess_history ash 
where  ash.BLOCKING_SESSION is not null
and    event = 'enq: TX - row lock contention'
and    ash.SAMPLE_TIME between to_date('10.08.2018 12:20:00','dd.mm.yyyy hh24:mi:ss') 
and    to_date('10.08.2018 12:30:00','dd.mm.yyyy hh24:mi:ss') 
group by sample_id,event
order by 3 desc;

и по нужному sample_id строим дерево блокировок, выбрасывая все, что не причастно к блокировкам

with s as (
SELECT /*+materialize*/* FROM dba_hist_active_sess_history t 
where sample_id = 272753416
)
SELECT sample_id,snap_id,level,s.SESSION_ID,sql_id,s.BLOCKING_SESSION,event,CONNECT_BY_ISLEAF is_leaf
FROM s
where not (CONNECT_BY_ISLEAF = 1 and level = 1)
CONNECT BY PRIOR s.SESSION_ID = s.BLOCKING_SESSION
START WITH s.blocking_session IS null;

По дереву находим блокера, после чего по сиду сблокера и сэмплу ищем верхнеуровневый запрос

SELECT s.* FROM dba_hist_active_sess_history t left join v$sql s on s.sql_id = t.top_level_sql_id
where sample_id = 272753416 and session_id = 2650;

В результат находим виновника, которым оказался джоб

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; 
BEGIN 
  COLVIR.Z_S03_PKGICM.pRun(next_date,COLVIR.C_pkgJob.fOpenUsr(4070,'COLVIR')); 
  :mydate := next_date; 
  IF broken THEN :b := 1; ELSE :b := 0; END IF; 
END; 