因为最近故障处理经常会用到这类查询进行ASH裸数据的分析,下面以m_ash0902为命名,时间为:2019-09-02 16:00:00 -> 2019-09-02 18:00:00,方便根据实际情况直接进行批量替换。
将客户的awrdump导入到自己测试环境后,可以直接通过dba_hist_active_sess_history查询,但推荐还是新建一张表专门存放相关数据,表名以m_ash<日期>命名
create table m_ash0902 tablespace dbs_d_awr as select * from dba_hist_active_sess_history where dbid = &dbid;
注:以下脚本原创作者:Maclean Liu
- 1.确定异常时刻的top n event
- 2.确定最终的top holder
- 3.找出每个采样点的最终top holder
1.确定异常时刻的top n event
--1.确定异常时刻的top n event select t.dbid, t.sample_id, t.sample_time, t.instance_number, t.event, t.session_state, t.c session_count from (select t.*, rank() over(partition by dbid, instance_number, sample_time order by c desc) r from (select /*+ parallel 8 */ t.*, count(*) over(partition by dbid, instance_number, sample_time, event) c, row_number() over(partition by dbid, instance_number, sample_time, event order by 1) r1 from m_ash0902 t where sample_time > to_timestamp(‘2019-09-02 16:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and sample_time < to_timestamp(‘2019-09-02 18:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) ) t where r1 = 1) t where r < 3 order by dbid, instance_number, sample_time, r;
2.确定最终的top holder
--2.确定最终的top holder select level lv, connect_by_isleaf isleaf, connect_by_iscycle iscycle, t.dbid, t.sample_id, t.sample_time, t.instance_number, t.session_id, t.sql_id, t.session_type, t.event, t.session_state, t.blocking_inst_id, t.blocking_session, t.blocking_session_status from m_ash0902 t where sample_time > to_timestamp(‘2019-09-02 16:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and sample_time < to_timestamp(‘2019-09-02 18:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) start with blocking_session is not null connect by nocycle prior dbid = dbid and prior sample_time = sample_time /*and ((prior sample_time) - sample_time between interval ‘-1‘ second and interval ‘1‘ second)*/ and prior blocking_inst_id = instance_number and prior blocking_session = session_id and prior blocking_session_serial# = session_serial# order siblings by dbid, sample_time;
3.找出每个采样点的最终top holder
--3.基于上一步的原理来找出每个采样点的最终top holder: select t.lv, t.iscycle, t.dbid, t.sample_id, t.sample_time, t.instance_number, t.session_id, t.sql_id, t.session_type, t.event, t.seq#, t.session_state, t.blocking_inst_id, t.blocking_session, t.blocking_session_status, t.c blocking_session_count from (select t.*, row_number() over(partition by dbid, instance_number, sample_time order by c desc) r from (select t.*, count(*) over(partition by dbid, instance_number, sample_time, session_id) c, row_number() over(partition by dbid, instance_number, sample_time, session_id order by 1) r1 from (select /*+ parallel 8 */ level lv, connect_by_isleaf isleaf, connect_by_iscycle iscycle, t.* from m_ash0902 t where sample_time > to_timestamp(‘2019-09-02 16:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and sample_time < to_timestamp(‘2019-09-02 18:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) start with blocking_session is not null connect by nocycle prior dbid = dbid and prior sample_time = sample_time /*and ((prior sample_time) - sample_time between interval ‘-1‘ second and interval ‘1‘ second)*/ and prior blocking_inst_id = instance_number and prior blocking_session = session_id and prior blocking_session_serial# = session_serial#) t where t.isleaf = 1) t where r1 = 1) t where r < 3 order by dbid, sample_time, r;