【转载自墨天轮】利用ASH视图分析数据库阻塞问题的三段分析法：【时间】、【事件】、【源头】

首先，可以从历史活动会话按时间查找会话数量截取到分钟并按时间分组，数量倒序排列。
select trunc(sample_time,'mi'),count(1)
from gv$active_session_history
group by trunc(sample_time,'mi') order by 1;

             

接下来，利用三段分析法进行逐一排查：

                                                                    【时间】

select trunc(sample_time, 'mi'), count(1)
  from dba_hist_active_sess_history
 where sample_time > to_date('20190527 10:18:00', 'yyyymmdd hh24:mi:ss')
   and sample_time < to_date('20190527 10:23: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('20190527 10:18:00', 'yyyymmdd hh24:mi:ss')
   and sample_time < to_date('20190527 10:23: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 instance_number,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('20190527 10:18:00', 'yyyymmdd hh24:mi:ss')
and sample_time < to_date('20190527 10:23:00', 'yyyymmdd hh24:mi:ss'))
select * from (
select sample_time,blocking_session final_block,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 instance_number= blocking_inst_id and sample_id = prior sample_id) a
where instr(sid_chain,final_block)=0 and not exists
(select 1 from ash b where a.final_block=b.session_id and b.blocking_session is not null)
order by sample_time;