{"id":554,"date":"2012-02-09T23:42:00","date_gmt":"2012-02-09T15:42:00","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=554"},"modified":"2012-02-10T16:24:39","modified_gmt":"2012-02-10T08:24:39","slug":"%e7%94%a8%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e5%b0%81%e8%a3%85awrrpt%e8%84%9a%e6%9c%ac%ef%bc%88%e4%ba%94%ef%bc%89","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=554","title":{"rendered":"\u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u4e94\uff09"},"content":{"rendered":"<p>\u505a\u4e86\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\uff0c\u5c01\u88c5\u4e86awr\u7684report\u7684\u529f\u80fd\u3002<br \/>\n\u6839\u636e11.2.0.3\u7684TYPE\u7684\u53d8\u5316\uff0c\u4fee\u6539\u4e86\u5b9a\u4e49\uff0c\u65b0\u589e\u4e00\u4e2a\u53d8\u91cf\u63a7\u5236\u662f\u5426\u751f\u6210RAC\u7684GLOBAL\u62a5\u544a\uff0c\u9ed8\u8ba4\u4e0d\u751f\u6210\u5168\u5c40\u62a5\u544a\u3002\u5c06DBNAME\u8f93\u5165\u6539\u53d8\u4e3aDBID\uff0c\u89e3\u51b3DBNAME\u540c\u540d\u95ee\u9898\u3002<br \/>\n\u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u4e00\uff09\uff1a<a href=\"http:\/\/yangtingkun.itpub.net\/post\/468\/515180\">http:\/\/yangtingkun.itpub.net\/post\/468\/515180<\/a><br \/>\n\u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u4e8c\uff09\uff1a<a href=\"http:\/\/yangtingkun.itpub.net\/post\/468\/515229\">http:\/\/yangtingkun.itpub.net\/post\/468\/515229<\/a><br \/>\n\u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u4e09\uff09\uff1a<a href=\"http:\/\/yangtingkun.itpub.net\/post\/468\/515271\">http:\/\/yangtingkun.itpub.net\/post\/468\/515271<\/a><br \/>\n\u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u56db\uff09\uff1a<a href=\"http:\/\/yangtingkun.itpub.net\/post\/468\/517487\">http:\/\/yangtingkun.itpub.net\/post\/468\/517487<\/a><br \/>\n\u4fee\u6539\u540e\u7684\u8fc7\u7a0b\u5982\u4e0b\uff1a<\/p>\n<pre lang='SQL'>SQL> CREATE OR REPLACE PROCEDURE P_AWR_REPORT (\r\n2 P_BEGIN IN VARCHAR2,\r\n3 P_END IN VARCHAR2,\r\n4 P_DIR IN VARCHAR2,\r\n5 P_DBID IN NUMBER DEFAULT NULL,\r\n6 P_PERINTERVAL IN BOOLEAN DEFAULT FALSE,\r\n7 P_GLOBAL IN BOOLEAN DEFAULT FALSE) AS\r\n8 TYPE T_VARCHAR IS TABLE OF VARCHAR2(8000 CHAR) INDEX BY BINARY_INTEGER;\r\n9 V_REPORT T_VARCHAR;\r\n10 V_FILE UTL_FILE.FILE_TYPE;\r\n11 TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;\r\n12 TYPE T_DATE IS TABLE OF DATE INDEX BY BINARY_INTEGER;\r\n13 V_INSTANCE T_NUM;\r\n14 V_SNAP_BEG T_NUM;\r\n15 V_SNAP_END T_NUM;\r\n16 V_BEGIN_DATE T_DATE;\r\n17 V_END_DATE T_DATE;\r\n18 V_BEGIN NUMBER;\r\n19 V_END NUMBER;\r\n20 V_DBID NUMBER;\r\n21 V_DBNAME V$DATABASE.NAME%TYPE;\r\n22 V_VERSION V$VERSION.BANNER%TYPE;\r\n23 BEGIN\r\n24 \r\n25 IF P_DBID IS NOT NULL THEN\r\n26 SELECT DISTINCT DBID, DB_NAME\r\n27 INTO V_DBID, V_DBNAME\r\n28 FROM DBA_HIST_DATABASE_INSTANCE\r\n29 WHERE DBID = P_DBID;\r\n30 ELSE\r\n31 SELECT DBID, NAME\r\n32 INTO V_DBID, V_DBNAME\r\n33 FROM V$DATABASE;\r\n34 END IF;\r\n35 \r\n36 SELECT DISTINCT INSTANCE_NUMBER,\r\n37 FIRST_VALUE(SNAP_ID) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_BEGIN, 'YYYYMMDDHH24MISS'))),\r\n38 FIRST_VALUE(END_INTERVAL_TIME) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_BEGIN, 'YYYYMMDDHH24MISS')))\r\n39 BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG, V_BEGIN_DATE\r\n40 FROM DBA_HIST_SNAPSHOT\r\n41 WHERE DBID = V_DBID\r\n42 ORDER BY INSTANCE_NUMBER;\r\n43 \r\n44 FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP\r\n45 BEGIN\r\n46 SELECT DISTINCT FIRST_VALUE(SNAP_ID) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_END, 'YYYYMMDDHH24MISS'))),\r\n47 FIRST_VALUE(END_INTERVAL_TIME) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_END, 'YYYYMMDDHH24MISS')))\r\n48 INTO V_SNAP_END(I), V_END_DATE(I)\r\n49 FROM DBA_HIST_SNAPSHOT\r\n50 WHERE SNAP_ID > V_SNAP_BEG(I)\r\n51 AND DBID = V_DBID\r\n52 AND INSTANCE_NUMBER = V_INSTANCE(I);\r\n53 EXCEPTION\r\n54 WHEN NO_DATA_FOUND THEN\r\n55 V_INSTANCE.DELETE(I);\r\n56 END;\r\n57 END LOOP;\r\n58 \r\n59 IF V_INSTANCE.COUNT = 1\r\n60 THEN\r\n61 V_BEGIN := V_SNAP_BEG(1);\r\n62 IF P_PERINTERVAL = FALSE\r\n63 THEN\r\n64 V_END := V_SNAP_END(1);\r\n65 ELSE\r\n66 V_END := V_SNAP_BEG(1) + 1;\r\n67 SELECT END_INTERVAL_TIME\r\n68 INTO V_END_DATE(1)\r\n69 FROM DBA_HIST_SNAPSHOT\r\n70 WHERE DBID = V_DBID\r\n71 AND INSTANCE_NUMBER = 1\r\n72 AND SNAP_ID = V_END;\r\n73 END IF;\r\n74 WHILE(V_END <= V_SNAP_END(1)) LOOP\r\n75 V_FILE := UTL_FILE.FOPEN(\r\n76 P_DIR,\r\n77 'awr_' || V_DBNAME || '_' || V_INSTANCE(1) || '_'\r\n78 || TO_CHAR(V_BEGIN_DATE(1), 'YYYYMMDD_HH24MISS') || '_'\r\n79 || TO_CHAR(V_END_DATE(1), 'YYYYMMDD_HH24MISS') || '.html',\r\n80 'w',\r\n81 32767);\r\n82 \r\n83 SELECT OUTPUT\r\n84 BULK COLLECT INTO V_REPORT\r\n85 FROM TABLE(\r\n86 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(\r\n87 V_DBID,\r\n88 V_INSTANCE(1),\r\n89 V_BEGIN,\r\n90 V_END,\r\n91 0));\r\n92 FOR I IN 1..V_REPORT.COUNT LOOP\r\n93 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));\r\n94 END LOOP;\r\n95 UTL_FILE.FCLOSE(V_FILE);\r\n96 V_BEGIN := V_BEGIN + 1;\r\n97 V_END := V_END + 1;\r\n98 V_BEGIN_DATE(1) := V_END_DATE(1);\r\n99 SELECT MAX(END_INTERVAL_TIME)\r\n100 INTO V_END_DATE(1)\r\n101 FROM DBA_HIST_SNAPSHOT\r\n102 WHERE DBID = V_DBID\r\n103 AND INSTANCE_NUMBER = 1\r\n104 AND SNAP_ID = V_END;\r\n105 END LOOP;\r\n106 ELSE\r\n107 SELECT BANNER\r\n108 INTO V_VERSION\r\n109 FROM V$VERSION\r\n110 WHERE BANNER LIKE 'CORE%';\r\n111 IF (TO_NUMBER(LTRIM(SUBSTR(V_VERSION, 5, INSTR(V_VERSION, '.', 1) - 5), CHR(9))) > 11\r\n112 OR (TO_NUMBER(LTRIM(SUBSTR(V_VERSION, 5, INSTR(V_VERSION, '.', 1) - 5), CHR(9))) = 11\r\n113 AND TO_NUMBER(SUBSTR(V_VERSION, INSTR(V_VERSION, '.', 1) + 1, INSTR(V_VERSION, '.', 1, 2) - INSTR(V_VERSION, '.', 1))) = 2))\r\n114 AND P_GLOBAL\r\n115 THEN\r\n116 V_BEGIN := V_SNAP_BEG(1);\r\n117 IF P_PERINTERVAL = FALSE\r\n118 THEN\r\n119 V_END := V_SNAP_END(1);\r\n120 ELSE\r\n121 V_END := V_SNAP_BEG(1) + 1;\r\n122 SELECT END_INTERVAL_TIME\r\n123 INTO V_END_DATE(1)\r\n124 FROM DBA_HIST_SNAPSHOT\r\n125 WHERE DBID = V_DBID\r\n126 AND INSTANCE_NUMBER = 1\r\n127 AND SNAP_ID = V_END;\r\n128 END IF;\r\n129 WHILE(V_END <= V_SNAP_END(1)) LOOP\r\n130 V_FILE := UTL_FILE.FOPEN(\r\n131 P_DIR,\r\n132 'awr_' || V_DBNAME || '_' || 'RAC' || '_'\r\n133 || TO_CHAR(V_BEGIN_DATE(1), 'YYYYMMDD_HH24MISS') || '_'\r\n134 || TO_CHAR(V_END_DATE(1), 'YYYYMMDD_HH24MISS') || '.html',\r\n135 'w',\r\n136 32767);\r\n137 \r\n138 EXECUTE IMMEDIATE\r\n139 'SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:DBID, '''', :BEGIN, :END, 0))'\r\n140 BULK COLLECT INTO V_REPORT\r\n141 USING V_DBID, V_BEGIN, V_END;\r\n142 \r\n143 FOR I IN 1..V_REPORT.COUNT LOOP\r\n144 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));\r\n145 END LOOP;\r\n146 UTL_FILE.FCLOSE(V_FILE);\r\n147 V_BEGIN := V_BEGIN + 1;\r\n148 V_END := V_END + 1;\r\n149 V_BEGIN_DATE(1) := V_END_DATE(1);\r\n150 SELECT MAX(END_INTERVAL_TIME)\r\n151 INTO V_END_DATE(1)\r\n152 FROM DBA_HIST_SNAPSHOT\r\n153 WHERE DBID = V_DBID\r\n154 AND INSTANCE_NUMBER = 1\r\n155 AND SNAP_ID = V_END;\r\n156 END LOOP;\r\n157 ELSE\r\n158 FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP\r\n159 V_BEGIN := V_SNAP_BEG(I);\r\n160 IF P_PERINTERVAL = FALSE\r\n161 THEN\r\n162 V_END := V_SNAP_END(I);\r\n163 ELSE\r\n164 V_END := V_SNAP_BEG(I) + 1;\r\n165 SELECT END_INTERVAL_TIME\r\n166 INTO V_END_DATE(I)\r\n167 FROM DBA_HIST_SNAPSHOT\r\n168 WHERE DBID = V_DBID\r\n169 AND INSTANCE_NUMBER = V_INSTANCE(I)\r\n170 AND SNAP_ID = V_END;\r\n171 END IF;\r\n172 WHILE(V_END <= V_SNAP_END(I)) LOOP\r\n173 V_FILE := UTL_FILE.FOPEN(\r\n174 P_DIR,\r\n175 'awr_' || V_DBNAME || '_' || V_INSTANCE(I) || '_'\r\n176 || TO_CHAR(V_BEGIN_DATE(I), 'YYYYMMDD_HH24MISS') || '_'\r\n177 || TO_CHAR(V_END_DATE(I), 'YYYYMMDD_HH24MISS') || '.html',\r\n178 'w',\r\n179 32767);\r\n180 \r\n181 SELECT OUTPUT\r\n182 BULK COLLECT INTO V_REPORT\r\n183 FROM TABLE(\r\n184 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(\r\n185 V_DBID,\r\n186 V_INSTANCE(I),\r\n187 V_BEGIN,\r\n188 V_END,\r\n189 0));\r\n190 FOR I IN 1..V_REPORT.COUNT LOOP\r\n191 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));\r\n192 END LOOP;\r\n193 UTL_FILE.FCLOSE(V_FILE);\r\n194 V_BEGIN := V_BEGIN + 1;\r\n195 V_END := V_END + 1;\r\n196 V_BEGIN_DATE(I) := V_END_DATE(I);\r\n197 SELECT MAX(END_INTERVAL_TIME)\r\n198 INTO V_END_DATE(I)\r\n199 FROM DBA_HIST_SNAPSHOT\r\n200 WHERE DBID = V_DBID\r\n201 AND INSTANCE_NUMBER = V_INSTANCE(I)\r\n202 AND SNAP_ID = V_END;\r\n203 END LOOP;\r\n204 END LOOP;\r\n205 END IF;\r\n206 END IF;\r\n207 END;\r\n208 \/\r\nProcedure created.<\/pre>\n<p>\u8fd0\u884c\u8fc7\u7a0b\uff1a<\/p>\n<pre lang='SQL'>SQL> exec p_awr_report('20120205100000', '20120205120000', 'ORA11G_DIR', 1972453558, false, true)\r\nPL\/SQL procedure successfully completed.\r\nSQL> host ls -l *20120205*.html\r\n-rw-r--r-- 1 ora11g oinstall 564770 Feb 10 16:19 awr_ECARD_RAC_20120205_100035_20120205_120002.html\r\nSQL> exec p_awr_report('20120205100000', '20120205120000', 'ORA11G_DIR', 1972453558)\r\nPL\/SQL procedure successfully completed.\r\nSQL> host ls -l *20120205*.html\r\n-rw-r--r-- 1 ora11g oinstall 642609 Feb 10 16:20 awr_ECARD_1_20120205_100035_20120205_120002.html\r\n-rw-r--r-- 1 ora11g oinstall 619464 Feb 10 16:20 awr_ECARD_2_20120205_100035_20120205_120002.html\r\n-rw-r--r-- 1 ora11g oinstall 564770 Feb 10 16:19 awr_ECARD_RAC_20120205_100035_20120205_120002.html<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u505a\u4e86\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\uff0c\u5c01\u88c5\u4e86awr\u7684report\u7684\u529f\u80fd\u3002 \u6839\u636e11.2.0.3\u7684TYPE\u7684\u53d8\u5316\uff0c\u4fee\u6539\u4e86\u5b9a\u4e49\uff0c\u65b0\u589e\u4e00\u4e2a\u53d8\u91cf\u63a7\u5236\u662f\u5426\u751f\u6210RAC\u7684GLOBAL\u62a5\u544a\uff0c\u9ed8\u8ba4\u4e0d\u751f\u6210\u5168\u5c40\u62a5\u544a\u3002\u5c06DBNAME\u8f93\u5165\u6539\u53d8\u4e3aDBID\uff0c\u89e3\u51b3DBNAME\u540c\u540d\u95ee\u9898\u3002 \u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u4e00\uff09\uff1ahttp:\/\/yangtingkun.itpub.net\/post\/468\/515180 \u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u4e8c\uff09\uff1ahttp:\/\/yangtingkun.itpub.net\/post\/468\/515229 \u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u4e09\uff09\uff1ahttp:\/\/yangtingkun.itpub.net\/post\/468\/515271 \u7528\u5b58\u50a8\u8fc7\u7a0b\u5c01\u88c5awrrpt\u811a\u672c\uff08\u56db\uff09\uff1ahttp:\/\/yangtingkun.itpub.net\/post\/468\/517487 \u4fee\u6539\u540e\u7684\u8fc7\u7a0b\u5982\u4e0b\uff1a 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_DBID IN NUMBER DEFAULT NULL, 6 P_PERINTERVAL IN BOOLEAN DEFAULT FALSE, 7 P_GLOBAL &hellip; <a href=\"https:\/\/yangtingkun.net\/?p=554\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[371,446,448],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-8W","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/554"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=554"}],"version-history":[{"count":1,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/554\/revisions"}],"predecessor-version":[{"id":555,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/554\/revisions\/555"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=554"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=554"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=554"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}