{"id":348,"date":"2011-12-02T23:08:55","date_gmt":"2011-12-02T15:08:55","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=348"},"modified":"2011-12-02T23:08:55","modified_gmt":"2011-12-02T15:08:55","slug":"11g%e6%94%b6%e9%9b%86%e7%bb%9f%e8%ae%a1%e4%bf%a1%e6%81%af%e5%bc%95%e5%8f%91wri_optstat_histhead_history%e8%a1%a8%e7%9a%84%e5%88%a0%e9%99%a4","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=348","title":{"rendered":"11g\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u5f15\u53d1wri$_optstat_histhead_history\u8868\u7684\u5220\u9664"},"content":{"rendered":"<p>\u5728\u67e5\u770b\u4e00\u4e2a\u5ba2\u623711.2.0.2\u7684AWR\u62a5\u544a\u65f6\uff0c\u53d1\u73b0\u4e86\u8fd9\u4e2a\u95ee\u9898\u3002<\/p>\n<p>\u5728SQL ORDER<br \/>\nBY ELASPED TIME\u8868\u4e2d\uff0c\u524d\u4e24\u9879\u7684\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n<table border=\"1\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td>\n<p align=\"center\"><strong>Elapsed<br \/>\nTime (s)<\/strong><\/p>\n<\/td>\n<td>\n<p align=\"center\"><strong>Executions<br \/>\n<\/strong><\/p>\n<\/td>\n<td>\n<p align=\"center\"><strong>%Total<\/strong><\/p>\n<\/td>\n<td>\n<p align=\"center\"><strong>%CPU<\/strong><\/p>\n<\/td>\n<td>\n<p align=\"center\"><strong>%IO<\/strong><\/p>\n<\/td>\n<td>\n<p align=\"center\"><strong>SQL<br \/>\nId<\/strong><\/p>\n<\/td>\n<td>\n<p align=\"center\"><strong>SQL<br \/>\nModule<\/strong><\/p>\n<\/td>\n<td>\n<p align=\"center\"><strong>SQL<br \/>\nText<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p align=\"right\">10,752.79<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"right\">0<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"right\">25.86<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"right\">98.20<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"right\">7.49<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"left\"><a href=\"file:\/\/\/C:\\Users\\lenovo\\AppData\\Local\\Temp\\awr-DMPORA2-logical.html#4sff1qphjvbwn\"><strong>4sff1qphjvbwn<\/strong><\/a><\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"left\">SQL*Plus\n<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"left\">BEGIN<br \/>\ndbms_stats.gather_table_&#8230;<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p align=\"right\">10,752.75<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"right\">13<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"right\">25.86<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"right\">98.20<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"right\">7.49<\/p>\n<\/td>\n<td valign=\"top\">\n<p align=\"left\"><a href=\"file:\/\/\/C:\\Users\\lenovo\\AppData\\Local\\Temp\\awr-DMPORA2-logical.html#bqn2h1xrmhmht\"><strong>bqn2h1xrmhmht<\/strong><\/a><\/p>\n<\/td>\n<td valign=\"top\"><\/td>\n<td valign=\"top\">\n<p align=\"left\">delete<br \/>\nfrom sys.wri$_optstat_h&#8230;<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u53ef\u4ee5\u770b\u5230\uff0c\u8fd92\u4e24\u4e2a\u8bed\u53e5\u5728\u82b1\u8d39\u65f6\u95f4\uff0c\u5360\u7528CPU\u4ee5\u53caIO\u7684\u767e\u5206\u6bd4\u4e0a\u60ca\u4eba\u7684\u4e00\u81f4\uff0c\u8fd9\u7edd\u5bf9\u4e0d\u662f\u4e00\u4e2a\u5076\u7136\u7684\u4e8b\u4ef6\u3002<\/p>\n<p>\u7b2c\u4e00\u4e2aSQL\u662f\u6536\u96c6\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\uff0c\u663e\u7136\u8fd9\u662f\u5728SQLPLUS\u91cc\u9762\u6267\u884c\u7684\u64cd\u4f5c\uff0c\u5176\u6267\u884c\u6b21\u6570\u662f0\uff0c\u8bf4\u660e\u5728\u91c7\u6837\u65f6\u95f4\u5185\uff0c\u8fd9\u4e2a\u64cd\u4f5c\u8fd8\u6ca1\u6709\u5b8c\u6210\u3002\u6839\u636eElspsed Time\u4f30\u7b97\uff0c\u8fd9\u4e2aSQL\u5df2\u7ecf\u8fd0\u884c\u4e863\u4e2a\u5c0f\u65f6\u3002<\/p>\n<p>\u800c\u7b2c\u4e8c\u4e2aSQL\u662f\u5728\u5220\u9664wri$_optstat_histhead_history\u8868\uff0c\u4ece\u8868\u540d\u4e0a\u770b\uff0c\u5e94\u8be5\u548cawr\u7684\u5386\u53f2\u7edf\u8ba1\u4fe1\u606f\u6709\u5173\uff0c\u5176\u6267\u884c\u6b21\u6570\u662f13\u6b21\u3002\u4e00\u4e2a\u5408\u7406\u7684\u63a8\u65ad\u5c31\u662f\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u4f1a\u5bfc\u81f4wri$_optstat_histhead_history\u8868\u7684\u5220\u9664\u64cd\u4f5c\uff0c\u800c\u8fd9\u4e2a\u5220\u9664\u64cd\u4f5c\u53ef\u80fd\u5f02\u5e38\u7f13\u6162\uff0c\u4ece\u800c\u5f15\u53d1\u4e86\u8fd9\u4e2a\u95ee\u9898\u3002<\/p>\n<p>\u67e5\u8be2MOS\uff0c\u679c\u7136\u53d1\u73b0\u662f\u4e00\u4e2abug\uff1aBug 10279045 &#8211; Slow Statistics purging<br \/>\n(SYSAUX grows) [ID 10279045.8]\uff0c\u5bfc\u81f4\u95ee\u9898\u7684\u539f\u56e0\u662f\u5728\u8fdb\u884c\u7edf\u8ba1\u4fe1\u606f\u7684PURGE\u64cd\u4f5c\u65f6\uff0c\u5220\u9664wri$_optstat_histhead_history\u8868\u4ea7\u751f\u4e86\u957f\u65f6\u95f4\u7684\u5168\u8868\u626b\u63cf\u64cd\u4f5c\u3002<\/p>\n<p>\u786e\u8ba4\u5f71\u54cd\u7684\u7248\u672c\u5305\u62ec11.1.0.7\u300111.2.0.1\u548c11.2.0.2\uff0c\u8fd9\u4e2a\u95ee\u9898\u572811.2.0.3\u4e2d\u88abfixed\u3002<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5728\u67e5\u770b\u4e00\u4e2a\u5ba2\u623711.2.0.2\u7684AWR\u62a5\u544a\u65f6\uff0c\u53d1\u73b0\u4e86\u8fd9\u4e2a\u95ee\u9898\u3002 \u5728SQL ORDER BY ELASPED TIME\u8868\u4e2d\uff0c\u524d\u4e24\u9879\u7684\u7ed3\u679c\u5982\u4e0b\uff1a Elapsed Time (s) Executions %Total %CPU %IO SQL Id SQL Module SQL Text 10,752.79 0 25.86 98.20 7.49 4sff1qphjvbwn SQL*Plus BEGIN dbms_stats.gather_table_&#8230; 10,752.75 13 25.86 98.20 7.49 bqn2h1xrmhmht delete from sys.wri$_optstat_h&#8230; \u53ef\u4ee5\u770b\u5230\uff0c\u8fd92\u4e24\u4e2a\u8bed\u53e5\u5728\u82b1\u8d39\u65f6\u95f4\uff0c\u5360\u7528CPU\u4ee5\u53caIO\u7684\u767e\u5206\u6bd4\u4e0a\u60ca\u4eba\u7684\u4e00\u81f4\uff0c\u8fd9\u7edd\u5bf9\u4e0d\u662f\u4e00\u4e2a\u5076\u7136\u7684\u4e8b\u4ef6\u3002 \u7b2c\u4e00\u4e2aSQL\u662f\u6536\u96c6\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\uff0c\u663e\u7136\u8fd9\u662f\u5728SQLPLUS\u91cc\u9762\u6267\u884c\u7684\u64cd\u4f5c\uff0c\u5176\u6267\u884c\u6b21\u6570\u662f0\uff0c\u8bf4\u660e\u5728\u91c7\u6837\u65f6\u95f4\u5185\uff0c\u8fd9\u4e2a\u64cd\u4f5c\u8fd8\u6ca1\u6709\u5b8c\u6210\u3002\u6839\u636eElspsed Time\u4f30\u7b97\uff0c\u8fd9\u4e2aSQL\u5df2\u7ecf\u8fd0\u884c\u4e863\u4e2a\u5c0f\u65f6\u3002 &hellip; <a href=\"https:\/\/yangtingkun.net\/?p=348\">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":[5],"tags":[195,255,256],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-5C","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/348"}],"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=348"}],"version-history":[{"count":1,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/348\/revisions"}],"predecessor-version":[{"id":349,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/348\/revisions\/349"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=348"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=348"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=348"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}