用儲存過程封裝awrrpt指令碼（二）

做了一個儲存過程，封裝了awr的report的功能。

增加了生成所有間隔報告的功能。

用儲存過程封裝awrrpt指令碼（一）：http://yangtingkun.itpub.net/post/468/515180

在原有的基礎上，增加了一個P_ PERINTERVAL引數，預設為FALSE，當設定為TRUE後，會生成開始時間到結束時間內所有INTERVAL對應的報告。

SQL> CREATE OR REPLACE PROCEDURE P_AWR_REPORT (
  2   P_BEGIN IN DATE,
  3   P_END IN DATE,
  4   D_DIR IN VARCHAR2,
  5   P_PERINTERVAL IN BOOLEAN DEFAULT FALSE) AS
  6   TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER;
  7   V_REPORT T_VARCHAR;
  8   V_FILE UTL_FILE.FILE_TYPE;
  9   TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 10   V_INSTANCE T_NUM;
 11   V_SNAP_BEG T_NUM;
 12   V_SNAP_END T_NUM;
 13   V_BEGIN NUMBER;
 14   V_END NUMBER;
 15   V_DBID NUMBER;
 16   V_DBNAME V$DATABASE.NAME%TYPE;
 17  BEGIN
 18   SELECT DBID, NAME
 19   INTO V_DBID, V_DBNAME
 20   FROM V$DATABASE;
 21  
 22   SELECT INSTANCE_NUMBER, MAX(SNAP_ID)
 23   BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG
 24   FROM DBA_HIST_SNAPSHOT
 25   WHERE END_INTERVAL_TIME <= P_BEGIN
 26   AND DBID = V_DBID
 27   GROUP BY INSTANCE_NUMBER
 28   ORDER BY INSTANCE_NUMBER;
 29   SELECT DISTINCT INSTANCE_NUMBER,
 30    FIRST_VALUE(SNAP_ID) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - P_BEGIN))
 31   BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG
 32   FROM DBA_HIST_SNAPSHOT
 33   WHERE DBID = V_DBID
 34   ORDER BY INSTANCE_NUMBER;
 35  
 36  
 37   FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
 38    BEGIN
 39     SELECT DISTINCT FIRST_VALUE(SNAP_ID) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - P_END))
 40     INTO V_SNAP_END(I)
 41     FROM DBA_HIST_SNAPSHOT
 42     WHERE SNAP_ID > V_SNAP_BEG(I)
 43     AND DBID = V_DBID
 44     AND INSTANCE_NUMBER = V_INSTANCE(I);
 45    EXCEPTION
 46     WHEN NO_DATA_FOUND THEN
 47      V_INSTANCE.DELETE(I);
 48    END;
 49   END LOOP;
 50 
 51   IF V_INSTANCE.COUNT = 1
 52   THEN
 53     V_BEGIN := V_SNAP_BEG(1);
 54    IF P_PERINTERVAL = FALSE
 55     THEN
 56      V_END := V_SNAP_END(1);
 57     ELSE
 58      V_END := V_SNAP_BEG(1) + 1;
 59     END IF;
 60    WHILE(V_END <= V_SNAP_END(1)) LOOP
 61     V_FILE := UTL_FILE.FOPEN(
 62      D_DIR,
 63      'awr_' || V_DBNAME || '_' || V_INSTANCE(1) || '_' || V_BEGIN || '_' || V_END || '.html',
 64      'w',
 65      32767);
 66     
 67     SELECT OUTPUT
 68     BULK COLLECT INTO V_REPORT
 69     FROM TABLE(
 70      DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
 71       V_DBID,
 72       V_INSTANCE(1),
 73       V_BEGIN,
 74       V_END,
 75       0));
 76     FOR I IN 1..V_REPORT.COUNT LOOP
 77      UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
 78     END LOOP;
 79     UTL_FILE.FCLOSE(V_FILE);
 80     V_BEGIN := V_BEGIN + 1;
 81     V_END := V_END + 1;
 82    END LOOP;
 83   ELSE
 84    FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
 85      V_BEGIN := V_SNAP_BEG(I);
 86     IF P_PERINTERVAL = FALSE
 87      THEN
 88       V_END := V_SNAP_END(I);
 89      ELSE
 90       V_END := V_SNAP_BEG(I) + 1;
 91      END IF;
 92     WHILE(V_END <= V_SNAP_END(I)) LOOP
 93      V_FILE := UTL_FILE.FOPEN(
 94       D_DIR,
 95       'awr_' || V_DBNAME || '_' || V_INSTANCE(I) || '_' || V_BEGIN || '_' || V_END || '.html',
 96       'w',
 97       32767);
 98   
 99      SELECT OUTPUT
100      BULK COLLECT INTO V_REPORT
101      FROM TABLE(
102       DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
103        V_DBID,
104        V_INSTANCE(I),
105        V_BEGIN,
106        V_END,
107        0));
108      FOR I IN 1..V_REPORT.COUNT LOOP
109       UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
110      END LOOP;
111      UTL_FILE.FCLOSE(V_FILE);
112      V_BEGIN := V_BEGIN + 1;
113      V_END := V_END + 1;
114     END LOOP;
115    END LOOP;
116   END IF;
117  END;
118  /

過程已建立。

下面可以在RAC環境下執行這個過程：

SQL> select instance_number, instance_name from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 rac1
              2 rac2

SQL> begin
  2  p_awr_report(to_date('2011-1-5 6', 'yyyy-mm-dd hh24'),
  3  to_date('2011-1-5-10', 'yyyy-mm-dd hh24'),
  4  'OBASE',
  5  true);
  6  end;
  7  /

PL/SQL procedure successfully completed.

檢查報告生成情況：

$ ls -l awr_RAC*   
-rw-r--r--   1 oracle   dba       304328 Jan  5 10:20 awr_RAC_1_4710_4711.html
-rw-r--r--   1 oracle   dba       304974 Jan  5 10:20 awr_RAC_1_4711_4712.html
-rw-r--r--   1 oracle   dba       307920 Jan  5 10:20 awr_RAC_1_4712_4713.html
-rw-r--r--   1 oracle   dba       314526 Jan  5 10:20 awr_RAC_1_4713_4714.html
-rw-r--r--   1 oracle   dba       306115 Jan  5 10:20 awr_RAC_1_4714_4715.html
-rw-r--r--   1 oracle   dba       308022 Jan  5 10:20 awr_RAC_1_4715_4716.html
-rw-r--r--   1 oracle   dba       309380 Jan  5 10:20 awr_RAC_1_4716_4717.html
-rw-r--r--   1 oracle   dba       313959 Jan  5 10:20 awr_RAC_1_4717_4718.html
-rw-r--r--   1 oracle   dba       294249 Jan  5 10:20 awr_RAC_2_4710_4711.html
-rw-r--r--   1 oracle   dba       291091 Jan  5 10:20 awr_RAC_2_4711_4712.html
-rw-r--r--   1 oracle   dba       290666 Jan  5 10:21 awr_RAC_2_4712_4713.html
-rw-r--r--   1 oracle   dba       291962 Jan  5 10:21 awr_RAC_2_4713_4714.html
-rw-r--r--   1 oracle   dba       299375 Jan  5 10:21 awr_RAC_2_4714_4715.html
-rw-r--r--   1 oracle   dba       290683 Jan  5 10:21 awr_RAC_2_4715_4716.html
-rw-r--r--   1 oracle   dba       292306 Jan  5 10:21 awr_RAC_2_4716_4717.html
-rw-r--r--   1 oracle   dba       284839 Jan  5 10:21 awr_RAC_2_4717_4718.html