AWR历史数据报表工具AHR

1,背景
   AWR报告只能分析指定时段统计数据，无法获取统计数据随时间的变化。
   为了便于以时序方式分析多个AWR快照中统计数据变化,特开发AWR历史数据分析工具AHR(Awr History Report)。

1.1功能
   使用AWR快照数据实时生成系统报表（系统负载、运行效率，统计项类似AWR报告），以便监控数据库运行状态。
   比如，可以如下方式指定AWR快照集，并对所指定快照集生成报表(结果见附件1)：
   *系统最近按小时统计数据变化，以便实时监控系统运行情况
   *系统最近若干天指定时点统计数据变化，以便监控高峰时段运行情况（缺省按小时采集数据）

1.2实现方式,权限要求
   为便于使用,使用PL/SQL实现,使用sqlplus创建报表包，然后使用匿名包调用。
   报表信息来自动态视图和AWR历史数据，需要SELECT ANY DICTIONARY系统权限。

1.3版本0.1功能
   系统统计信息报表，参见附件2源码。

1.4后续版本功能
   *指定sql/高负载sql相关报表
   *segment相关报表
   *file io相关报表
   *SGA 相关报表
   *PGA 相关报表
   *RAC 相关报表
   *等待事件相关报表

1.5功能扩展
   *图形化展示(采用nmon采集数据并通过EXCEL展示类似方式)
   *历史数据保存,阈值设置与告警
   *结合AWR资料的导出／导入功能，可以订制分析更长时间系统变化报表

2,系统统计信息报表操作参考
2.1设置数据库实例
   通过设置dbid,inst_num,可以生成其他实例报告,缺省时生成当前连接数据库／实例统计信息
   awr_hist_rpt.set_dbid_inst(null,null);

2.2设置AWR快照集
2.2.1设置最近按小时采集的AWR快照集
   awr_hist_rpt.init_snap_lastest;
2.2.2设置最近若干天指定时点AWR快照集
   awr_hist_rpt.init_snap_same_hour;
2.2.3使用游标设置AWR快照集 
  awr_hist_rpt.init_snap_by_cursor(l_cursor);
 
2.3获取统计信息
2.3.1获取实例主要统计信息
   awr_hist_rpt.get_sysstat;
2.3.2获取实例指定统计项
   --获取实例指定统计项,如"user calls"
   awr_hist_rpt.get_sysstat('user calls');

2.4,例子
2.4.1使用系统最近AWR快照集
set serveroutput on
begin
   --设置dbid,inst_num
   awr_hist_rpt.set_dbid_inst(null,null);

   --分析最近按小时采集的快照
   awr_hist_rpt.init_snap_lastest;

   --获取实例主要统计信息
   awr_hist_rpt.get_sysstat;

end;
/

2.4.1使用游标设置AWR快照集
set serveroutput on
DECLARE
  l_cursor awr_hist_rpt.cursor_ref;
BEGIN
  --初始化，通过设置dbid,inst_num,可以生成其他实例报告,缺省时生成当前连接数据库／实例统计信息
  awr_hist_rpt.set_dbid_inst(NULL, NULL);

  OPEN l_cursor FOR
    SELECT *
      FROM (SELECT s1.snap_id           snap_id1,
                   s1.end_interval_time snap_time1,
                   s2.snap_id           snap_id2,
                   s2.end_interval_time snap_time2
              FROM dba_hist_snapshot s1, dba_hist_snapshot s2
             WHERE s1.dbid = s2.dbid
               AND s1.instance_number = s2.instance_number
               AND s1.startup_time = s2.startup_time
               AND s1.end_interval_time = s2.begin_interval_time
             ORDER BY snap_id1 DESC)
     WHERE rownum <= 6
     ORDER BY snap_id1;

  --分析最近按小时采集的快照
  awr_hist_rpt.init_snap_by_cursor(l_cursor);

  --获取实例主要统计信息
  awr_hist_rpt.get_sysstat;

END;
/

附测试结果：
          DB TIME
   snap_id  Db time/Ela        diff     sec
--------------------------------------------
       673           .9        3313    3580
       674           .9        3378    3633
       675           .9        3312    3577
       676           .9        3312    3582
       677           .9        3375    3630
       678           .9        3253    3569
 
          LOAD PROFILE
 
          redo size
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673       1231.7     4409632    3580
       674       1028.4     3736448    3633
       675        402.6     1440404    3577
       676        460.2     1648712    3582
       677       1436.3     5213804    3630
       678       1075.3     3837900    3569
 
          session logical reads
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673         67.4      241394    3580
       674         23.1       84149    3633
       675          7.3       26266    3577
       676          7.9       28474    3582
       677         38.7      140685    3630
       678         15.2       54548    3569
 
          db block changes
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673          6.7       24262    3580
       674          5.0       18448    3633
       675          1.6        5715    3577
       676          1.8        6637    3582
       677          7.6       27908    3630
       678          5.6       20117    3569
 
          physical reads
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673           .0          22    3580
       674           .0           5    3633
       675           .0           1    3577
       676           .0           2    3582
       677           .0           5    3630
       678           .0          17    3569
 
          physical writes
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673           .3        1154    3580
       674           .3        1086    3633
       675           .2         821    3577
       676           .2         847    3582
       677           .3        1248    3630
       678           .2        1028    3569
 
          user calls
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673           .8        3040    3580
       674          1.3        4780    3633
       675           .1         355    3577
       676           .0         338    3582
       677          2.3        8418    3630
       678           .3        1343    3569
 
          parse count (total)
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673          1.7        6073    3580
       674          1.4        5117    3633
       675           .6        2391    3577
       676           .6        2350    3582
       677          2.0        7341    3630
       678          1.0        3602    3569
 
          parse count (total)
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673          1.7        6073    3580
       674          1.4        5117    3633
       675           .6        2391    3577
       676           .6        2350    3582
       677          2.0        7341    3630
       678          1.0        3602    3569
 
          sorts (memory)
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673           .5        2094    3580
       674           .5        1965    3633
       675           .5        1778    3577
       676           .5        1789    3582
       677           .5        1984    3630
       678           .5        1836    3569
 
          logons cumulative
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673           .0         116    3580
       674           .0         113    3633
       675           .0         114    3577
       676           .0         113    3582
       677           .0         113    3630
       678           .0         113    3569
 
          execute count
   snap_id     diff/sec        diff     sec
--------------------------------------------
       673          3.4       12246    3580
       674          3.0       11040    3633
       675          1.6        5834    3577
       676          1.6        5753    3582
       677          4.8       17556    3630
       678          2.1        7739    3569

附件2

CREATE OR REPLACE PACKAGE awr_hist_rpt IS
  TYPE snap_pair_type IS RECORD(
    snap_id1   INT,
    snap_time1 TIMESTAMP,
    snap_id2   INT,
    snap_time2 TIMESTAMP);
  TYPE snap_pair_tab IS TABLE OF snap_pair_type INDEX BY PLS_INTEGER;
  g_snap_pair_tab snap_pair_tab;

  TYPE cursor_ref IS REF CURSOR;

  g_dbid            INT := 0;
  g_inst_num        INT := 0;
  g_max_snap_id     INT := 0;
  g_inst_startup    TIMESTAMP;
  g_snap_range_delt INT := 1 / 24 / 20;

  FUNCTION snap_pair(snap_id1   IN INT,
                     snap_time1 IN DATE,
                     snap_id2   IN INT,
                     snap_time2 IN DATE) RETURN snap_pair_type;
  PROCEDURE log(p_txt IN VARCHAR2);
  PROCEDURE set_dbid_inst(p_dbid     IN INT DEFAULT NULL,
                          p_inst_num IN INT DEFAULT NULL);
  PROCEDURE init_snap_lastest(p_snap_top_n IN INT DEFAULT 24);
  PROCEDURE init_snap_by_cursor(p_cursor IN cursor_ref);
  PROCEDURE init_snap_same_hour(p_hh24       IN VARCHAR2 DEFAULT '15',
                                p_snap_top_n IN INT DEFAULT 24);
  PROCEDURE get_env;
  PROCEDURE get_sysstat(p_statname IN VARCHAR2,
                        p_title    IN VARCHAR2 DEFAULT NULL);
  PROCEDURE get_sysstat;
END awr_hist_rpt;
/
CREATE OR REPLACE PACKAGE BODY awr_hist_rpt AS
  FUNCTION snap_pair(snap_id1   IN INT,
                     snap_time1 IN DATE,
                     snap_id2   IN INT,
                     snap_time2 IN DATE) RETURN snap_pair_type IS
    l_snap_pair snap_pair_type;
  BEGIN
    l_snap_pair.snap_id1   := snap_id1;
    l_snap_pair.snap_time1 := snap_time1;
    l_snap_pair.snap_id2   := snap_id2;
    l_snap_pair.snap_time2 := snap_time2;
    RETURN l_snap_pair;
  END;

  PROCEDURE log(p_txt IN VARCHAR2) IS
  BEGIN
    dbms_output.put_line(p_txt);
  END log;

  PROCEDURE log_blank_line IS
  BEGIN
    dbms_output.put_line(' ');
  END log_blank_line;

  PROCEDURE log_sep_line IS
  BEGIN
    dbms_output.put_line('--------------------------------------------');
  END log_sep_line;

  PROCEDURE log_title(p_txt IN VARCHAR2) IS
  BEGIN
    dbms_output.put_line(lpad(' ', 10, ' ') || p_txt);
  END log_title;

  PROCEDURE set_dbid_inst(p_dbid     IN INT DEFAULT NULL,
                          p_inst_num IN INT DEFAULT NULL) IS
  BEGIN
    dbms_output.enable(NULL);
    --dbid
    IF p_dbid IS NULL THEN
      SELECT dbid INTO g_dbid FROM v$database;
    ELSE
      g_dbid := p_dbid;
    END IF;
 
    --instance_number
    IF p_inst_num IS NULL THEN
      SELECT instance_number INTO g_inst_num FROM v$instance;
    ELSE
      g_inst_num := p_inst_num;
    END IF;
 
    --g_max_snap_id
    SELECT MAX(snap_id)
      INTO g_max_snap_id
      FROM dba_hist_snapshot
     WHERE dbid = g_dbid
       AND instance_number = g_inst_num;
 
    --startup_time    
    SELECT startup_time
      INTO g_inst_startup
      FROM dba_hist_snapshot
     WHERE dbid = g_dbid
       AND instance_number = g_inst_num
       AND snap_id = g_max_snap_id;
 
  END set_dbid_inst;

  PROCEDURE init_snap_lastest(p_snap_top_n IN INT DEFAULT 24) IS
    l_snap_cnt INT := 0;
  BEGIN
    FOR s2 IN (SELECT snap_id,
                      begin_interval_time,
                      end_interval_time,
                      dbid,
                      instance_number,
                      startup_time
                 FROM dba_hist_snapshot
                WHERE dbid = g_dbid
                  AND instance_number = g_inst_num
                  AND startup_time = g_inst_startup
                  AND rownum <= p_snap_top_n
                ORDER BY snap_id) LOOP
      FOR s1 IN (SELECT snap_id
                   FROM dba_hist_snapshot
                  WHERE dbid = s2.dbid
                    AND instance_number = s2.instance_number
                    AND startup_time = s2.startup_time
                    AND end_interval_time = s2.begin_interval_time) LOOP
        g_snap_pair_tab(l_snap_cnt) := snap_pair(s1.snap_id,
                                                 s2.begin_interval_time,
                                                 s2.snap_id,
                                                 s2.end_interval_time);
        l_snap_cnt := l_snap_cnt + 1;
      END LOOP;
    END LOOP;
    --log('====>' || l_snap_cnt);
  END init_snap_lastest;

  PROCEDURE init_snap_by_cursor(p_cursor IN cursor_ref) IS
    l_snap_cnt  INT := 0;
    l_snap_pair snap_pair_type;
  BEGIN
    LOOP
      FETCH p_cursor
        INTO l_snap_pair;
      EXIT WHEN p_cursor%NOTFOUND;
      g_snap_pair_tab(l_snap_cnt) := l_snap_pair;
      l_snap_cnt := l_snap_cnt + 1;
    END LOOP;
    CLOSE p_cursor;
    --log('====>' || l_snap_cnt);
  EXCEPTION
    WHEN OTHERS THEN
      CLOSE p_cursor;
  END init_snap_by_cursor;

  PROCEDURE init_snap_same_hour(p_hh24       IN VARCHAR2 DEFAULT '15',
                                p_snap_top_n IN INT DEFAULT 24) IS
    l_snap_cnt INT := 0;
  BEGIN
    FOR s2 IN (SELECT snap_id,
                      begin_interval_time,
                      end_interval_time,
                      dbid,
                      instance_number,
                      startup_time
                 FROM dba_hist_snapshot
                WHERE dbid = g_dbid
                  AND instance_number = g_inst_num
                  AND startup_time = g_inst_startup
                  AND to_char(end_interval_time, 'HH24') = p_hh24
                  AND end_interval_time BETWEEN
                      begin_interval_time + 1 / 24 - g_snap_range_delt AND
                      begin_interval_time + 1 / 24 + g_snap_range_delt
                  AND rownum <= p_snap_top_n
                ORDER BY snap_id) LOOP
      FOR s1 IN (SELECT snap_id
                   FROM dba_hist_snapshot
                  WHERE dbid = s2.dbid
                    AND instance_number = s2.instance_number
                    AND startup_time = s2.startup_time
                    AND end_interval_time = s2.begin_interval_time) LOOP
        g_snap_pair_tab(l_snap_cnt) := snap_pair(s1.snap_id,
                                                 s2.begin_interval_time,
                                                 s2.snap_id,
                                                 s2.end_interval_time);
        l_snap_cnt := l_snap_cnt + 1;
      END LOOP;
    END LOOP;
    --log('====>' || l_snap_cnt);
  END init_snap_same_hour;

  PROCEDURE get_env IS
  BEGIN
    log('-----------------------');
    log('g_dbid:' || g_dbid);
    log('g_inst_num:' || g_inst_num);
    log('g_max_snap_id:' || g_max_snap_id);
    log('g_inst_startup:' || to_char(g_inst_startup, 'YYYYMMDD:HH24MISS'));
 
    log('-----------------------');
    IF g_snap_pair_tab.COUNT > 0 THEN
      FOR i IN g_snap_pair_tab.FIRST .. g_snap_pair_tab.LAST LOOP
        log('snap_id:' || g_snap_pair_tab(i)
            .snap_id1 || '->' || g_snap_pair_tab(i)
            .snap_id2 || ' timestamp:' ||
            to_char(g_snap_pair_tab(i).snap_time1, 'YYYYMMDD:HH24MISS') || '->' ||
            to_char(g_snap_pair_tab(i).snap_time2, 'YYYYMMDD:HH24MISS'));
      END LOOP;
    ELSE
      log('--not found useable snap_id');
    END IF;
  END get_env;

  PROCEDURE get_sysstat(p_statname IN VARCHAR2,
                        p_title    IN VARCHAR2 DEFAULT NULL) IS
    v1         NUMBER;
    v2         NUMBER;
    t1         DATE;
    t2         DATE;
    delt_t     NUMBER(22, 2);
    l_statname VARCHAR2(30) := p_statname;
    l_title    VARCHAR2(30) := nvl(p_title, p_statname);
  BEGIN
    log_blank_line;
    log_title(l_title);
    log(lpad('snap_id', 10) || lpad('diff/sec', 13) || lpad('diff', 12) ||
        lpad('sec', 8));
    log_sep_line;
 
    IF g_snap_pair_tab.COUNT > 0 THEN
      FOR i IN g_snap_pair_tab.FIRST .. g_snap_pair_tab.LAST LOOP
        t1     := g_snap_pair_tab(i).snap_time1;
        t2     := g_snap_pair_tab(i).snap_time2;
        delt_t := (t2 - t1) * 86400.0;
        BEGIN
          SELECT VALUE
            INTO v1
            FROM dba_hist_sysstat
           WHERE dbid = g_dbid
             AND instance_number = g_inst_num
             AND stat_name = l_statname
             AND snap_id = g_snap_pair_tab(i).snap_id1;
          SELECT VALUE
            INTO v2
            FROM dba_hist_sysstat
           WHERE dbid = g_dbid
             AND instance_number = g_inst_num
             AND stat_name = l_statname
             AND snap_id = g_snap_pair_tab(i).snap_id2;
          log(lpad(g_snap_pair_tab(i).snap_id2, 10) ||
              lpad(to_char((v2 - v1) / delt_t, '9999999999d99'), 13) ||
              lpad((v2 - v1), 12) || lpad(round(delt_t), 8));
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;
      END LOOP;
    ELSE
      log('--not found useable snap_id');
    END IF;
 
  END get_sysstat;

  PROCEDURE get_dbtime IS
    v1         NUMBER;
    v2         NUMBER;
    t1         DATE;
    t2         DATE;
    delt_t     NUMBER(22, 2);
    l_statname VARCHAR2(30);
  BEGIN
    log_blank_line;
    log_title('DB TIME');
    log(lpad('snap_id', 10) || lpad('Db time/Ela', 13) || lpad('diff', 12) ||
        lpad('sec', 8));
    log_sep_line;
 
    l_statname := 'DB time';
    IF g_snap_pair_tab.COUNT > 0 THEN
      FOR i IN g_snap_pair_tab.FIRST .. g_snap_pair_tab.LAST LOOP
        t1     := g_snap_pair_tab(i).snap_time1;
        t2     := g_snap_pair_tab(i).snap_time2;
        delt_t := (t2 - t1) * 86400.0;
        BEGIN
          SELECT VALUE
            INTO v1
            FROM dba_hist_sysstat
           WHERE dbid = g_dbid
             AND instance_number = g_inst_num
             AND stat_name = l_statname
             AND snap_id = g_snap_pair_tab(i).snap_id1;
          SELECT VALUE
            INTO v2
            FROM dba_hist_sysstat
           WHERE dbid = g_dbid
             AND instance_number = g_inst_num
             AND stat_name = l_statname
             AND snap_id = g_snap_pair_tab(i).snap_id2;
          log(lpad(g_snap_pair_tab(i).snap_id2, 10) ||
              lpad(to_char((v2 - v1) / 100 / delt_t, '9999999999d99'), 13) ||
              lpad(round((v2 - v1) / 100), 12) || lpad(round(delt_t), 8));
        EXCEPTION
       
          WHEN OTHERS THEN
            NULL;
        END;
      END LOOP;
    ELSE
      log('--not found useable snap_id');
    END IF;
 
  END get_dbtime;

  PROCEDURE get_sysstat IS
  BEGIN
    get_dbtime;
 
    log_blank_line;
    log_title('LOAD PROFILE');
 
    --Redo size      
    get_sysstat('redo size');
 
    --Logical reads:  
    --session logical reads
    --db block gets
    --consistent gets
    get_sysstat('session logical reads');
 
    --Block changes:
    --db block changes
    --consistent changes
    get_sysstat('db block changes');
 
    --Physical reads:
    --physical reads
    get_sysstat('physical reads');
 
    --Physical writes:
    --physical writes
    get_sysstat('physical writes');
 
    --User calls:
    --user calls
    get_sysstat('user calls');
 
    --Parses:
    --parse count (total)
    get_sysstat('parse count (total)');
 
    --Hard parses:
    --parse count (hard)
    get_sysstat('parse count (total)');
 
    --Sorts:
    --sorts (memory)
    --sorts (disk)
    get_sysstat('sorts (memory)');
 
    --Logons:
    --logons cumulative
    --logons current
    get_sysstat('logons cumulative');
 
    --Executes:
    --execute count
    get_sysstat('execute count');
 
    --Transactions:
    --user commits
    --user rollbacks
 
    --% Blocks changed per Read:
    --db block changes/physical reads
 
    --Recursive Call %:
    --recursive calls/user calls
 
    --Rollback per transaction %:  
    --(user rollbacks)/(user commits+user rollbacks)
 
    --Rows per Sort:    98.78
    --sorts (rows)/(sorts (memory)+sorts (disk))
 
    --Instance Efficiency Percentages (Target 100%)
    --Buffer Nowait %:      
 
    --Redo NoWait %: 
 
    --Buffer  Hit   %:
    --?   
 
    --In-memory Sort %: 
    --sorts (memory)/(sorts (disk)+sorts (memory))
 
    --Library Hit   %: 
    --?
 
    --Soft Parse %:  
    --1-parse count (hard)/parse count (total)
 
    --Execute to Parse %:  
    --1-parse count (total)/execute count
 
    --Latch Hit %: 
    --?
 
    --Parse CPU to Parse Elapsd %: 
    --parse time cpu/parse time elapsed
 
    --% Non-Parse CPU:  
    --parse time cpu\?? 
 
  END get_sysstat;
END awr_hist_rpt;
/