通过 dba_hist_active_sess_history 分析数据库历史性能问题

1.Dump出问题期间的ASH数据

SQL> conn user/passwd  
SQL> create table t_ash as select * from dba_hist_active_sess_history where SAMPLE_TIME between TO_TIMESTAMP ('<time_begin>', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('<time_end>', 'YYYY-MM-DD HH24:MI:SS');

$ exp user/passwd file=t_ash.dmp tables=(t_ash) log=t_ash.exp.log

然后导入到测试机：  
$ imp user/passwd file=t_ash.dmp log=t_ash.imp.log

2.验证时间范围是否准确

set line 200 pages 1000  
col sample_time for a25  
col event for a40  
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';

select  
 t.dbid, t.instance_number, min(sample_time), max(sample_time), count(*) session_count  
  from t_ash t  
 group by t.dbid, t.instance_number  
 order by dbid, instance_number;

INSTANCE_NUMBER    MIN(SAMPLE_TIME)    MAX(SAMPLE_TIME)    SESSION_COUNT  
1    2015-03-26 21:00:04.278    2015-03-26 22:59:48.387    2171  

3.找出问题发生的精确时间

select  
 dbid, instance_number, sample_id, sample_time, count(*) session_count  
  from t_ash t  
 group by dbid, instance_number, sample_id, sample_time  
 order by dbid, instance_number, sample_time;

4.确定每个采样点的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  
                 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 t_ash t  
                /*where sample_time >  
                    to_timestamp('2013-11-17 13:59:00',  
                                 'yyyy-mm-dd hh24:mi:ss')  
                and sample_time <  
                    to_timestamp('2013-11-17 14:10:00',  
                                 'yyyy-mm-dd hh24:mi:ss')*/  
                ) t  
         where r1 = 1) t  
 where r < 3  
 order by dbid, instance_number, sample_time, r;

5.观察采样点的等待链

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 t_ash t  
/*where sample_time >  
    to_timestamp('2013-11-17 13:55:00',  
                 'yyyy-mm-dd hh24:mi:ss')  
and sample_time <  
    to_timestamp('2013-11-17 14:10: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;

6.确定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  
                         level              lv,  
                         connect_by_isleaf  isleaf,  
                         connect_by_iscycle iscycle,  
                         t.*  
                          from t_ash t  
                        /*where sample_time >  
                            to_timestamp('2013-11-17 13:55:00',  
                                         'yyyy-mm-dd hh24:mi:ss')  
                        and sample_time <  
                            to_timestamp('2013-11-17 14:10: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;