Oracle AWR报告的实现

AWR的本质是通过定时采样，收集数据库的性能数据，然后通过报表进行直观展现，以下摘录一些生成报表的SQL，可以据此了解一下Oracle AWR的内部展现机制。

在使用awrrpt.sql生成报告时，会调用awrrpti.sql脚本，该脚本的主要部分为：

   select output from table(dbms_workload_repository.
      &fn_name( :dbid,:inst_num,:bid,:eid,:rpt_options ));

这里的fn_name是根据用户输入的选项来选择调用AWR_REPORT_HTML或AWR_REPORT_TEXT函数。

AWR_REPORT_HTML函数的内容如下，其主要内容是调用了另外一个过程DBMS_SWRF_REPORT_INTERNAL：

  FUNCTION AWR_REPORT_HTML(L_DBID     IN NUMBER, 
                       L_INST_NUM IN NUMBER, 
                       L_BID      IN NUMBER, 
                       L_EID      IN NUMBER,
                       L_OPTIONS  IN NUMBER DEFAULT 0)
  RETURN AWRRPT_HTML_TYPE_TABLE PIPELINED 
  IS
    I NUMBER;
    ARR DBMS_SWRF_REPORT_INTERNAL.OUTPUT_TABLE;
  BEGIN
    ARR := DBMS_SWRF_REPORT_INTERNAL.AWR_REPORT_MAIN(L_DBID, L_INST_NUM, L_BID, L_EID, 
                 L_OPTIONS, DBMS_SWRF_REPORT_INTERNAL.TRUE_I);

    FOR I IN ARR.FIRST..ARR.LAST LOOP
        PIPE ROW(AWRRPT_HTML_TYPE(SUBSTR(ARR(I), 1, 
                 DBMS_SWRF_REPORT_INTERNAL.HTML_REPORT_LINESIZE)));
    END LOOP;

    DBMS_SWRF_REPORT_INTERNAL.REPORT_CLEANUP();

  END AWR_REPORT_HTML;

这里的AWR_REPORT_MAIN是最重要的一个函数：

  FUNCTION AWR_REPORT_MAIN(L_DBID     IN NUMBER, 
                           L_INST_NUM IN NUMBER, 
                           L_BID      IN NUMBER, 
                           L_EID      IN NUMBER,
                           L_OPTIONS  IN NUMBER DEFAULT OPT_NULL,
                           TO_HTML    IN BINARY_INTEGER DEFAULT TRUE_I)
  RETURN OUTPUT_TABLE;

该函数的主要内容如下：

  FUNCTION AWR_REPORT_MAIN(L_DBID     IN NUMBER, 
                           L_INST_NUM IN NUMBER, 
                           L_BID      IN NUMBER, 
                           L_EID      IN NUMBER,
                           L_OPTIONS  IN NUMBER DEFAULT OPT_NULL,
                           TO_HTML    IN BINARY_INTEGER DEFAULT TRUE_I)
  RETURN OUTPUT_TABLE  --返回一个输出表内容
  IS
  BEGIN

    
    REPORT_CLEANUP();

    
    CHECK_PARAMETERS(L_DBID,L_INST_NUM,L_BID,L_EID);    
    
    SET_THRESHOLD;    


    REPORT_INIT(L_DBID, L_INST_NUM, L_BID, L_EID, RPT_STATS, RPT_PARAMS, 
                RPT_TIME_VALS); --报表初始化

    IF (TO_HTML = TRUE_I) THEN
       BEGIN_REPORT_TAGS('AWR Report');
    END IF;

    REPORT_SUMMARY(L_DBID, L_INST_NUM, 
                   L_BID, L_EID, L_OPTIONS, TO_HTML, FALSE_I); --生成报表概要

   DISPLAY_SUBTREES_OF(MAIN_REPT, TO_HTML, L_OPTIONS, L_DBID,
                       L_INST_NUM, L_BID, L_EID);  --生成报表其他部分
   DISPLAY_SUBTREES_OF(RAC_REPT, TO_HTML, L_OPTIONS, L_DBID,
                       L_INST_NUM, L_BID, L_EID); --生成报表的RAC部分信息
   
   APPEND_ROW(END_REPORT);

   IF (TO_HTML = TRUE_I) THEN
      END_REPORT_TAGS();
   END IF;

   RETURN RPT_ROWS;

  END AWR_REPORT_MAIN;

接下来数据库就会在主要的过程中调用各种SQL生成报表的各部分内容，如以下就是展现等待事件的查询：

     SELECT EVENT, WAITS, TIME, 
            DECODE(WAITS, NULL, TO_NUMBER(NULL), 
                          0,    TO_NUMBER(NULL),
                          TIME/WAITS*1000) AVGWT,
            PCTWTT, WAIT_CLASS
      FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
             FROM (SELECT E.EVENT_NAME                           EVENT,
                          E.TOTAL_WAITS - NVL(B.TOTAL_WAITS,0)   WAITS,
                          (E.TIME_WAITED_MICRO - 
                           NVL(B.TIME_WAITED_MICRO,0)) / 1000000 TIME,
                          100 * (E.TIME_WAITED_MICRO - 
                                        NVL(B.TIME_WAITED_MICRO,0)) /
                                 RPT_STATS(STAT_DBTIME)          PCTWTT,
                          E.WAIT_CLASS                           WAIT_CLASS
                    FROM DBA_HIST_SYSTEM_EVENT B,
                         DBA_HIST_SYSTEM_EVENT E
                    WHERE B.SNAP_ID(+)          = L_BID
                      AND E.SNAP_ID             = L_EID
                      AND B.DBID(+)             = L_DBID
                      AND E.DBID                = L_DBID
                      AND B.INSTANCE_NUMBER(+)  = L_INST_NUM
                      AND E.INSTANCE_NUMBER     = L_INST_NUM
                      AND B.EVENT_ID(+)         = E.EVENT_ID
                      AND E.TOTAL_WAITS         > NVL(B.TOTAL_WAITS,0)
                      AND E.WAIT_CLASS         != 'Idle'
                   UNION ALL
                   SELECT 'CPU time'                             EVENT,
                          TO_NUMBER(NULL)                        WAITS,
                          RPT_STATS(STAT_CPU_TIME)/1000000       TIME,
                          100 * RPT_STATS(STAT_CPU_TIME) / 
                                         RPT_STATS(STAT_DBTIME)  PCTWTT,
                          NULL                                   WAIT_CLASS
                    FROM DUAL
                    WHERE RPT_STATS(STAT_CPU_TIME) > 0)
             ORDER BY TIME DESC, WAITS DESC)
      WHERE ROWNUM <= TOP_N_EVENTS;

各类SQL不再过多介绍，了解了AWR的展现原理，我们就可以灵活的去分析各种AWR数据，获得更有价值的信息。