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

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

增加了對指定資料庫生成報告的功能，修改了輸入引數為字串型別，修改輸出AWR檔案包含日期資訊。

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

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

修改後的過程如下：

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

Procedure created.

預設狀態下執行：

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TEST08

SQL> EXEC P_AWR_REPORT('20101115080000', '20101115100000', 'D_OUTPUT')

PL/SQL procedure successfully completed.

檢查報告生成情況：

[oracle@yans1 ~]$ cd /home/oracle
[oracle@yans1 ~]$ ls *.html
awr_TEST08_1_20110114010035_20110114030037.html

如果指定資料庫名生成報告：

SQL> EXEC P_AWR_REPORT('20101115080000', '20101115100000', 'D_OUTPUT', 'TESTRAC')

PL/SQL procedure successfully completed.

檢查報告資訊：

[oracle@yans1 ~]$ ls *.html
awr_TEST08_1_20110114010035_20110114030037.html  awr_TESTRAC_1_20101115080048_20101115100018.html  awr_TESTRAC_2_20101115081753_20101115101824.html