脚本内容
--ASH 3段式第一段,确定活跃会话最多的时间段及SNAP_ID
select snap_id,instance_number,to_char(sample_time,'yyyymmdd-hh24mi'),count(*) from
dba_hist_active_sess_history
where sample_time >=to_date('&begintime_yyyymmddhh24','yyyymmddhh24')
and sample_time <=to_date('&endtime_yyyymmddhh24','yyyymmddhh24')
group by snap_id,instance_number,to_char(sample_time,'yyyymmdd-hh24mi')
order by to_char(sample_time,'yyyymmdd-hh24mi');
--ASH 3段式第二段,获取特定SNAP及时间段内最多的等待事件
select snap_id,instance_number,to_char(sample_time,'yyyymmdd-hh24mi'),count(*),event from
dba_hist_active_sess_history
where sample_time >=to_date('&begintime_yyyymmddhh24','yyyymmddhh24')
and sample_time <=to_date('&endtime_yyyymmddhh24','yyyymmddhh24')
and snap_id = &snap_id -- 可明显加快速度
group by snap_id,instance_number,to_char(sample_time,'yyyymmdd-hh24mi'),event
having count(*) > 2
order by to_char(sample_time,'yyyymmdd-hh24mi');
--ASH 3段式第三段,获取特定SNAP及时间段内的等待链
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('&begintime_yyyymmddhh24','yyyymmddhh24')
and sample_time <=to_date('&endtime_yyyymmddhh24','yyyymmddhh24')
and snap_id = &snap_id -- 可明显加快速度
)
select SAMPLE_TIME,FINAL_BLK,FINAL_PROGRAM,nvl(FINAL_EVT,'ON CPU') as FINAL_EVT,(LVL2_EVENT) as LVL2_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
from ash start with BLOCKING_SESSION is null
connect by prior SESSION_ID = BLOCKING_SESSION and prior INSTANCE_NUMBER= BLOCKING_INST_ID and sample_id = prior sample_id
) group by FINAL_BLK,FINAL_EVT,SAMPLE_TIME,FINAL_PROGRAM ,LVL2_EVENT
having count(*) > 2
order by SAMPLE_TIME
/
评论
贡献排行榜