1.在必要的时候备份下gv$active_session_history
create table ash_xxxx as select * from gv$active_session_history
where SAMPLE_TIME between TO_TIMESTAMP (’<time_begin>’, ‘YYYY-MM-DD HH24:MI:SS’) and TO_TIMESTAMP (’<time_end>’, ‘YYYY-MM-DD HH24:MI:SS’);
2.通过ash速查找阻塞源头
–分钟级
col sample_time for a30
col event for a60
col count(1) for 999999
select to_char(trunc(sample_time,‘mi’),‘yyyy-mm-dd hh24:mi:ss’) sample_time,event,count(1)
from gv$active_session_history
where sample_time >=to_date(’&begin_time’,‘yyyy-mm-dd hh24:mi:ss’)
and sample_time <=to_date(’&end_time’,‘yyyy-mm-dd hh24:mi:ss’)
group by trunc(sample_time,‘mi’),event order by 1;
–秒级
select to_char(sample_time,‘yyyy-mm-dd hh24:mi:ss’) sample_time,event,count(1)
from gv$active_session_history
where sample_time >=to_date(’&begin_time’,‘yyyy-mm-dd hh24:mi:ss’)
and sample_time <=to_date(’&end_time’,‘yyyy-mm-dd hh24:mi:ss’)
group by to_char(sample_time,‘yyyy-mm-dd hh24:mi:ss’),event 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 gv$active_session_history where
sample_time >to_date(‘2021-03-01 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
and
to_char(sample_time,‘hh24:mi:ss’) between ‘06:00:00’ and ‘06:10:00’)
select SAMPLE_TIME,FINAL_BLK,FINAL_PROGRAM,
nvl(FINAL_EVT,‘ON CPU’) as FINAL_EVT
,(LVL2_EVENT) as LVL2_EVENT,LVL3_EVENT,LVL4_EVENT,
count() from(
select SESSION_ID,SAMPLE_TIME,sys_connect_by_path(SESSION_ID,’,’) CHAIN,
connect_by_root(PROGRAM)as FINAL_PROGRAM,
connect_by_root(SESSION_ID)FINAL_BLK,
connect_by_root(event) FINAL_EVT,event ,
case when level=2 then event end as LVL2_EVENT ,
case when level=3 then event end as LVL3_EVENT ,
case when level=4 then event end as LVL4_EVENT
from ash start with BLOCKING_SESSION is null
connect by prior SESSION_ID = BLOCKING_SESSION and prior inst_id= BLOCKING_INST_ID and sample_id = prior sample_id
) group by FINAL_BLK,FINAL_EVT,SAMPLE_TIME,FINAL_PROGRAM ,LVL3_EVENT,LVL4_EVENT,LVL2_EVENT
having count() > 1
order by SAMPLE_TIME,count(*) desc
–https://blogs.oracle.com/database4cn/
