{"id":2068,"date":"2024-05-25T10:54:38","date_gmt":"2024-05-25T02:54:38","guid":{"rendered":"http:\/\/oneai.eu.org\/?p=2068"},"modified":"2024-05-25T10:54:38","modified_gmt":"2024-05-25T02:54:38","slug":"%e8%be%be%e6%a2%a6%e6%95%b0%e6%8d%ae%e5%ba%93%e7%94%9f%e6%88%90awr%e6%8a%a5%e5%91%8a","status":"publish","type":"post","link":"https:\/\/oneai.eu.org\/?p=2068","title":{"rendered":"\u8fbe\u68a6\u6570\u636e\u5e93\u751f\u6210awr\u62a5\u544a"},"content":{"rendered":"<pre><code class=\"language-sql\">\/*\u538b\u6d4b\u8bd5\u524d \u5f00\u542f \u5feb\u7167 *\/\nSP_INIT_AWR_SYS(1); --\u5f00\u542fawr\u7cfb\u7edf\u5305\nCALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(60);   -- [10, 525600]  \u9ed8\u8ba4\u4e3a60\u5206\u949f  \u9700\u8981\u63d0\u524d\u8c03\u7528\u7cfb\u7edf\u8fc7\u7a0b\u5e76\u8bbe\u7f6e\u95f4\u9694\u65f6\u95f4\u3002awr\u62a5\u544a\u7684\u95f4\u9694\u65f6\u95f4\nDBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); --\u624b\u52a8\u521b\u5efa\u5feb\u7167\nSELECT * FROM SYS.WRM$_SNAPSHOT;  --\u67e5\u770b\u521b\u5efa\u7684\u5feb\u7167\u4fe1\u606f\uff0c\u5305\u62ec\u5feb\u7167 id\uff1a\n--\u67e5\u770b\/\u8f93\u51fahtml\u683c\u5f0f\u7684awr\u62a5\u544a\nSELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));  \n\nCALL DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2)\nSYS.AWR_REPORT_HTML(1,2,&#039;E:\\&#039;,&#039;AWR1.HTML&#039;); --\u628a snapshot \u7684 id \u5728 1~2 \u8303\u56f4\u5185\u7684 AWR \u5206\u6790\u62a5\u544a\u751f\u6210\u5230d \u76d8 awr1.html \u6587\u4ef6\u3002\n--\u5220\u9664\u5feb\u7167\n--CALL DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(22,32);\n--\u4fee\u6539\u5feb\u7167\n-- CALL DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440,30); --\u4fee\u6539 snapshot \u7684\u95f4\u9694\u65f6\u95f4\u4e3a 30 \u5206\u949f\u3001\u4fdd\u7559\u65f6\u95f4\u4e3a 1 \u5929\u3002\n--CALL DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440,60); \n--\u67e5\u8be2\u8bbe\u7f6e\u540e\u5feb\u7167\u53c2\u6570\nSELECT * FROM SYS.WRM$_WR_CONTROL;\n--\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u6e05\u7406\u5feb\u7167\nCALL DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY(); --\u6e05\u7406\u5168\u90e8 snapshot\u3002\n\nselect * from  SYS.WRM$_WR_CONTROL --\u8bb0\u5f55\u5feb\u7167\u7684\u76f8\u5173\u63a7\u5236\u4fe1\u606f\u3002\nselect * from SYS.WRM$_SNAPSHOT --\u8bb0\u5f55\u5feb\u7167\u7684\u76f8\u5173\u4fe1\u606f\u3002\nselect * from SYS.WRH$_RESOURCE_LIMIT --\u663e\u793a\u6240\u6709\u5feb\u7167\u4e2d\u8868\u3001\u7528\u6237\u7684\u7a7a\u95f4\u9650\u5236\u4fe1\u606f\u3002\n\/* \u6267\u884c\u8ba1\u5212  explain + sql \u8bed\u53e5\n explain SELECT A.MEMUSED,B.CPUUSED,C.THREADCOUNT,D.SESSIONCOUNT  FROM (SELECT STAT_VAL MEMUSED FROM SYS.V$SYSSTAT WHERE UPPER(NAME)=&#039;MEMORY USED BYTES&#039; ) A, (SELECT STAT_VAL CPUUSED FROM SYS.V$SYSSTAT WHERE UPPER(NAME)=&#039;OS DM DATABASE CPU RATE&#039;) B,(SELECT COUNT(*)THREADCOUNT FROM SYS.V$THREADS) C,(SELECT  COUNT(*) SESSIONCOUNT  FROM SYS.V$SESSIONS) D\n\n*\/\n--explain SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\/*\u538b\u6d4b\u8bd5\u524d \u5f00\u542f \u5feb\u7167 *\/ SP_INIT_AWR_SYS(1); &#8211;\u5f00\u542fawr\u7cfb\u7edf\u5305 CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(60); &#8212; [10, 525600] \u9ed8\u8ba4\u4e3a60\u5206\u949f \u9700\u8981\u63d0\u524d\u8c03\u7528\u7cfb\u7edf\u8fc7\u7a0b\u5e76\u8bbe\u7f6e\u95f4\u9694\u65f6\u95f4\u3002awr\u62a5\u544a\u7684\u95f4\u9694\u65f6\u95f4 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); &#8211;\u624b\u52a8\u521b\u5efa\u5feb\u7167 SELECT * FROM SYS.WRM$_SNAPSHOT; &#8211;\u67e5\u770b\u521b\u5efa\u7684\u5feb\u7167\u4fe1\u606f\uff0c\u5305\u62ec\u5feb\u7167 id\uff1a &#8211;\u67e5\u770b\/\u8f93\u51fahtml\u683c\u5f0f\u7684awr\u62a5\u544a SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2)); CALL DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2) SYS.AWR_REPORT_HTML(1,2,&#039;E:\\&#039;,&#039;AWR1.HTML&#039;); &#8211;\u628a snapshot \u7684 id \u5728 1~2 \u8303\u56f4\u5185\u7684 AWR \u5206\u6790\u62a5\u544a\u751f\u6210\u5230d \u76d8 awr1.html \u6587\u4ef6\u3002 &#8211;\u5220\u9664\u5feb\u7167 &#8211;CALL DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(22,32); &#8211;\u4fee\u6539\u5feb\u7167 &#8212; CALL DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440,30); &#8211;\u4fee\u6539 snapshot \u7684\u95f4\u9694\u65f6\u95f4\u4e3a 30 \u5206\u949f\u3001\u4fdd\u7559\u65f6\u95f4\u4e3a 1 \u5929\u3002 &#8211;CALL DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440,60); &#8211;\u67e5\u8be2\u8bbe\u7f6e\u540e\u5feb\u7167\u53c2\u6570 SELECT * FROM SYS.WRM$_WR_CONTROL; &#8211;\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u200b\u6e05\u7406\u5feb\u7167 CALL DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY(); &#8211;\u6e05\u7406\u5168\u90e8 snapshot\u3002 select * from SYS.WRM$_WR_CONTROL &#8211;\u8bb0\u5f55\u5feb\u7167\u7684\u76f8\u5173\u63a7\u5236\u4fe1\u606f\u3002 select * from SYS.WRM$_SNAPSHOT &#8211;\u8bb0\u5f55\u5feb\u7167\u7684\u76f8\u5173\u4fe1\u606f\u3002 select * from SYS.WRH$_RESOURCE_LIMIT &#8211;\u663e\u793a\u6240\u6709\u5feb\u7167\u4e2d\u8868\u3001\u7528\u6237\u7684\u7a7a\u95f4\u9650\u5236\u4fe1\u606f\u3002 \/* \u6267\u884c\u8ba1\u5212 explain + sql \u8bed\u53e5 explain SELECT A.MEMUSED,B.CPUUSED,C.THREADCOUNT,D.SESSIONCOUNT FROM (SELECT STAT_VAL MEMUSED FROM SYS.V$SYSSTAT WHERE UPPER(NAME)=&#039;MEMORY USED BYTES&#039; ) A, (SELECT STAT_VAL CPUUSED FROM SYS.V$SYSSTAT WHERE UPPER(NAME)=&#039;OS DM DATABASE CPU RATE&#039;) B,(SELECT COUNT(*)THREADCOUNT FROM SYS.V$THREADS) C,(SELECT COUNT(*) SESSIONCOUNT FROM SYS.V$SESSIONS) D *\/ &#8211;explain SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","footnotes":""},"categories":[100,80],"tags":[],"class_list":["post-2068","post","type-post","status-publish","format-standard","hentry","category-100","category-80"],"_links":{"self":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/2068","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2068"}],"version-history":[{"count":1,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/2068\/revisions"}],"predecessor-version":[{"id":2069,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/2068\/revisions\/2069"}],"wp:attachment":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}