—查询确定阻塞时间
select trunc(sample_time, ‘mi’), count(1)
from dba_hist_active_sess_history
where sample_time > to_date(‘20190919 05:45:00’, ‘yyyymmdd hh24:mi:ss’)
and sample_time < to_date(‘20190919 09:30:00’, ‘yyyymmdd hh24:mi:ss’)
and event is not null
group by trunc(sample_time,‘mi’) having count(1)>2 order by 1;
select trunc(sample_time, ‘mi’),event,count(1)
from dba_hist_active_sess_history
where sample_time > to_date(‘20190919 06:45:00’, ‘yyyymmdd hh24:mi:ss’)
and sample_time < to_date(‘20190919 09:30:00’, ‘yyyymmdd hh24:mi:ss’)
and event is not null
group by trunc(sample_time,‘mi’),event having count(1)>2 order by 1,3;
–查看阻塞会话
with ash as
(select --inst_id,
session_id,
event,
blocking_session,
program,
to_char(sample_time, ‘yyyymmdd hh24miss’) sample_time,
sample_id,
blocking_inst_id
from dba_hist_active_sess_history
where sample_time > to_date(‘20190919 06:50:00’, ‘yyyymmdd hh24:mi:ss’)
and sample_time < to_date(‘20190919 08:30:00’, ‘yyyymmdd hh24:mi:ss’))
select *
from (select sample_time,
blocking_session final_blocking,
sys_connect_by_path(session_id, ‘,’) sid_chain,
sys_connect_by_path(event, ‘,’) event_chain
from ash
start with session_id is not null
connect by prior blocking_session = session_id
–and prior inst_id = blocking_inst_id
and sample_id = prior sample_id) a
where instr(sid_chain,final_blocking) = 0 and not exists
(select 1
from ash b
where a.final_blocking = b.session_id
and b.blocking_session is not null)
order by sample_time
