ash_top
emcs
85次下载
921次浏览
2019-07-24
4.5

脚本内容

--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 /

评论

贡献排行榜