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

文章来自： https://blogs.oracle.com/Database4CN/tags/dba_hist_active_sess_history

背景

在很多情况下，当数据库发生性能问题的时候，我们并没有机会来收集足够的诊断信息，比如system state dump或者hang analyze，甚至问题发生的时候DBA根本不在场。这给我们诊断问题带来很大的困难。那么在这种情况下，我们是否能在事后收集一些信息来分析问题的原因呢？在Oracle 10G或者更高版本上，答案是肯定的。本文我们将介绍一种通过dba_hist_active_sess_history的数据来分析问题的一种方法。

适用于

Oracle 10G或更高版本，本文适用于任何平台。

详情

在Oracle 10G中，我们引入了AWR和ASH采样机制，有一个视图gv$active_session_history会每秒钟将数据库所有节点的Active Session采样一次，而dba_hist_active_sess_history则会将gv$active_session_history里的数据每10秒采样一次并持久化保存。基于这个特征，我们可以通过分析dba_hist_active_sess_history的Session采样情况，来定位问题发生的准确时间范围，并且可以观察每个采样点的top event和top holder。下面通过一个例子来详细说明。

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

为了不影响生产系统，我们可以将问题大概期间的ASH数据export出来在测试机上分析。

基于dba_hist_active_sess_history创建一个新表m_ash，然后将其通过exp/imp导入到测试机。在发生问题的数据库上执行exp:

SQL> conn user/passwd

SQL> create table m_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=m_ash.dmp tables=(m_ash) log=m_ash.exp.log

然后导入到测试机：

$ imp user/passwd file=m_ash.dmp log=m_ash.imp.log

2. 验证导出的ASH时间范围:

为了加快速度，我们采用了并行查询。另外建议采用Oracle SQL Developer来查询以防止输出结果折行不便于观察。

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 /*+ parallel 8 */

 t.dbid, t.instance_number, min(sample_time), max(sample_time), count(*) session_count

  from m_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

2    2015-03-26 21:02:12.047    2015-03-26 22:59:42.584    36

从以上输出可知该数据库共2个节点，采样时间共2小时，节点1的采样比节点2要多很多，问题可能发生在节点1上。

3. 确认问题发生的精确时间范围:

参考如下脚本：

select /*+ parallel 8 */

 dbid, instance_number, sample_id, sample_time, count(*) session_count

  from m_ash t

 group by dbid, instance_number, sample_id, sample_time

 order by dbid, instance_number, sample_time;

INSTANCE_NUMBER    SAMPLE_ID    SAMPLE_TIME    SESSION_COUNT

1    36402900    2015-03-26 22:02:50.985    4

1    36402910    2015-03-26 22:03:01.095    1

1    36402920    2015-03-26 22:03:11.195    1

1    36402930    2015-03-26 22:03:21.966    21

1    36402940    2015-03-26 22:03:32.116    102

1    36402950    2015-03-26 22:03:42.226    181

1    36402960    2015-03-26 22:03:52.326    200

1    36402970    2015-03-26 22:04:02.446    227

1    36402980    2015-03-26 22:04:12.566    242

1    36402990    2015-03-26 22:04:22.666    259

1    36403000    2015-03-26 22:04:32.846    289

1    36403010    2015-03-26 22:04:42.966    147

1    36403020    2015-03-26 22:04:53.076    2

1    36403030    2015-03-26 22:05:03.186    4

1    36403040    2015-03-26 22:05:13.296    1

1    36403050    2015-03-26 22:05:23.398    1

注意观察以上输出的每个采样点的active session的数量，数量突然变多往往意味着问题发生了。从以上输出可以确定问题发生的精确时间在2015-03-26 22:03:21 ~ 22:04:42，问题持续了大约1.5分钟。

注意: 观察以上的输出有无断档，比如某些时间没有采样。

4. 确定每个采样点的top n event:

在这里我们指定的是top 2 event��并且注掉了采样时间以观察所有采样点的情况。如果数据量较多，您也可以通过开启sample_time的注释来观察某个时间段的情况。注意最后一列session_count指的是该采样点上的等待该event的session数量。

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_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;