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

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

透過輸入指定時間點和目錄名稱，就可以在指定的位置生成awr的html報告，對於RAC環境會同時生成所有節點的awr報告。

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

過程已建立。

建立一個目錄，就可以執行這個過程：

SQL> CREATE DIRECTORY D_OUTPUT AS 'D:\SQL';

目錄已建立。

SQL> BEGIN
  2  P_AWR_REPORT(TO_DATE('2010-12-31 10', 'YYYY-MM-DD HH24'),
  3  TO_DATE('2010-12-31 11', 'YYYY-MM-DD HH24'),
  4  'D_OUTPUT');
  5  END;
  6  /

PL/SQL 過程已成功完成。

檢查對應的目錄，可以看到，report檔案已經建立：

D:\>cd sql

D:\sql>dir
 驅動器 D 中的卷沒有標籤。
 卷的序列號是 F4E9-218A

 D:\sql 的目錄

2011/01/04  23:40   

D:\sql>type awr_ytk102_1_118_119.html|more