用存储过程封装awrrpt脚本（二）

最新推荐文章于 2024-11-12 10:56:30 发布

ckawt40802

最新推荐文章于 2024-11-12 10:56:30 发布

阅读量92

点赞数

文章标签：数据库

做了一个存储过程，封装了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