ASH裸データdba_hist_active_sess_historyの分析

以前、あるケース「enq:TX-row lock contentionの分析過程を記録する」でこの方法を使用したことがある.最近の故障処理では、このようなクエリを用いてASH裸データの分析を行うことが多いので、以下m_ash 0902はネーミングで、時間は: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;