行锁阻塞分析

1、分析会话阻塞出现的时间段

select trunc(sample_time,'mi'),count(1) from dba_hist_active_sess_history where

sample_time>to_date('20200320 15:00:00','yyyymmdd hh24:mi:ss') and

sample_time

group by trunc(sample_time,'mi') having count(1)>2 order by 1;

2、分析会话阻塞的等待事件

select trunc(sample_time,'mi'),event,count(1) from dba_hist_active_sess_history where

sample_time>to_date('20200320 15:00:00','yyyymmdd hh24:mi:ss') and

sample_time2 order by 1,3;

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('20200320 15:00:00','yyyymmdd hh24:mi:ss') and

sample_time

select * from (

select sample_time,blocking_session final_block,sys_connect_by_path(session_id,',') sid_chain,sys_connect_by_path(event,',') enent_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;