{"id":708,"date":"2012-04-01T23:42:52","date_gmt":"2012-04-01T15:42:52","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=708"},"modified":"2012-04-04T01:13:50","modified_gmt":"2012-04-03T17:13:50","slug":"%e5%b0%86%e6%8c%87%e5%ae%9asql%e7%9a%84%e6%89%a7%e8%a1%8c%e8%ae%a1%e5%88%92%e4%bb%8e%e5%85%b1%e4%ba%ab%e6%b1%a0%e5%88%a0%e9%99%a4","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=708","title":{"rendered":"\u5c06\u6307\u5b9aSQL\u7684\u6267\u884c\u8ba1\u5212\u4ece\u5171\u4eab\u6c60\u5220\u9664"},"content":{"rendered":"<p>\u5982\u679cOracle\u7684\u4f18\u5316\u5668\u4ea7\u751f\u4e86\u67d0\u79cd\u9519\u8bef\u7684\u6267\u884c\u8ba1\u5212\uff0c\u6216\u8005\u6211\u4eec\u5e0c\u671bOracle\u5bf9\u4e8e\u67d0\u4e2aSQL\u91cd\u65b0\u8fdb\u884c\u5206\u6790\uff0c\u90a3\u4e48\u5c31\u9700\u8981\u8fd9\u4e2aSQL\u7684\u6267\u884c\u8ba1\u5212\u5728\u5171\u4eab\u6c60\u4e2d\u8fc7\u671f\uff0c\u800c\u7b80\u5355\u7684\u65b9\u6cd5\u572810.2.0.4\u4ee5\u540e\u624d\u51fa\u73b0\u3002<br \/>\n\u5bf9\u4e8e\u4ee5\u524d\u7684\u7248\u672c\u800c\u8a00\uff0c\u6700\u663e\u800c\u6613\u89c1\u7684\u65b9\u6cd5\u83ab\u8fc7\u4e8e\u76f4\u63a5\u5237\u65b0\u5171\u4eab\u6c60\uff0c\u4f46\u662f\u5982\u679c\u662f\u6570\u636e\u5e93\u4e2d\u7edd\u5927\u90e8\u5206\u7684SQL\u90fd\u5b58\u5728\u95ee\u9898\uff0c\u90a3\u4e48\u8fd9\u79cd\u65b9\u6cd5\u65e0\u53ef\u539a\u975e\uff0c\u4e5f\u53ef\u80fd\u662f\u89c1\u6548\u6700\u5feb\u7684\u65b9\u6cd5\uff0c\u800c\u5982\u679c\u6570\u636e\u5e93\u4e2d\u4ec5\u4ec5\u662f\u4e2a\u522b\u7684SQL\u5b58\u5728\u95ee\u9898\uff0c\u90a3\u4e48\u8fd9\u79cd\u65b9\u6cd5\u5c31\u8fc7\u4e8e\u66b4\u529b\u4e86\u3002<\/p>\n<pre lang='SQL'>SQL> select count(*) from dual;\r\n  COUNT(*)\r\n----------\r\n         1\r\nSQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations\r\n  2  from v$sqlarea\r\n  3  where sql_text = 'select count(*) from dual';\r\nSQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS\r\n------------- ---------------- ---------- ---------- ---------- ----------- -------------\r\n4m94ckmu16f9k 00000000B6C61FC0 4094900530          1          1           1             0\r\nSQL> select count(*) from v$sqlarea;\r\n  COUNT(*)\r\n----------\r\n      3061\r\nSQL> alter system flush shared_pool;\r\nSystem altered.\r\nSQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations\r\n  2  from v$sqlarea\r\n  3  where sql_text = 'select count(*) from dual';\r\nno rows selected\r\nSQL> select count(*) from v$sqlarea;\r\n  COUNT(*)\r\n----------\r\n        37<\/pre>\n<p>\u4e3a\u4e86\u4e00\u4e2aSQL\u800c\u6e05\u7a7a\u6574\u4e2a\u5171\u4eab\u6c60\uff0c\u8fd9\u4e2a\u4ee3\u4ef7\u786e\u5b9e\u592a\u5927\u4e86\uff0c\u4f55\u51b5\u5bf9\u4e8e\u4e00\u4e2a\u7e41\u5fd9\u7684OLTP\u7cfb\u7edf\u800c\u8a00\uff0c\u8fd9\u4e2a\u5237\u65b0\u5171\u4eab\u6c60\u7684\u64cd\u4f5c\u6240\u5e26\u6765\u7684\u98ce\u9669\u548c\u540e\u679c\u4e0e\u76f4\u63a5\u5173\u95ed\u6570\u636e\u5e93\u76f8\u6bd4\uff0c\u4e5f\u6ca1\u6709\u592a\u5927\u7684\u5dee\u522b\u3002<br \/>\n\u90a3\u4e48\u6709\u6ca1\u6709\u7ec6\u7c92\u5ea6\u4e00\u70b9\u7684\u529e\u6cd5\u5462\uff0c\u5176\u5b9e\u65b9\u6cd5\u6709\u5f88\u591a\uff0c\u76f8\u5173\u8868\u4e0a\u4efb\u4f55\u7684DDL\u90fd\u4f1a\u5bfc\u81f4SQL\u6267\u884c\u8ba1\u5212\u7684\u5931\u6548\uff0c\u4f46\u662fDDL\u672c\u8eab\u98ce\u9669\u5c31\u6bd5\u7adf\u9ad8\uff0c\u5982\u679c\u60f3\u8981\u5bf9\u7cfb\u7edf\u5f71\u54cd\u6700\u5c0f\uff0c\u90a3\u4e48\u8fd9\u4e2aDDL\u5c31\u975eGRANT\u83ab\u5c5e\u3002\u53ea\u9700\u8981\u5f53\u524d\u7528\u6237\u5c06\u8fd9\u4e2a\u8868\u7684\u6743\u9650\u6388\u6743\u7ed9\u81ea\u5df1\uff0c\u5c31\u53ef\u4ee5\u8fbe\u5230\u60f3\u8981\u7684\u6548\u679c\uff1a<\/p>\n<pre lang='SQL'>SQL> select count(*) from dual;\r\n  COUNT(*)\r\n----------\r\n         1\r\nSQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations\r\n  2  from v$sqlarea\r\n  3  where sql_text = 'select count(*) from dual';\r\nSQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS\r\n------------- ---------------- ---------- ---------- ---------- ----------- -------------\r\n4m94ckmu16f9k 00000000B6C61FC0 4094900530          1          1           1             0\r\nSQL> select 1 from dual;\r\n         1\r\n----------\r\n         1\r\nSQL> select * from dual;\r\nD\r\n-\r\nX\r\nSQL> select 'a' from dual;\r\n'\r\n-\r\na\r\nSQL> select count(1) from dual;\r\n  COUNT(1)\r\n----------\r\n         1\r\nSQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations\r\n  2  from v$sqlarea\r\n  3  where lower(sql_text) like '%dual%';\r\nSQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS\r\n------------- ---------------- ---------- ---------- ---------- ----------- -------------\r\ngr7s3j0cg8pr6 00000000B6A5A470  418666214          1          1           1             0\r\n40p7rprfbt1as 00000000B69BDC38 3703342424          1          1           1             0\r\n520mkxqpf15q8 00000000B6DD9610 2866845384          1          1           1             0\r\nak90gdq0udv37 00000000B6E3C6B0 2175200359          2          2           2             1\r\n4m94ckmu16f9k 00000000B6C61FC0 4094900530          1          1           1             0\r\na5ks9fhw2v9s1 00000000B698DA88  942515969          1          1           1             0\r\n800hwktjz3zuc 00000000B6999268 1676803916          1          1           1             0\r\n7 rows selected.\r\nSQL> grant select on dual to sys;\r\ngrant select on dual to sys\r\n                        *\r\nERROR at line 1:\r\nORA-01749: you may not GRANT\/REVOKE privileges to\/from yourself\r\n\r\nSQL> grant select on dual to public;\r\nGrant succeeded.\r\nSQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations\r\n  2  from v$sqlarea\r\n  3  where lower(sql_text) like '%dual%';\r\nSQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS\r\n------------- ---------------- ---------- ---------- ---------- ----------- -------------\r\ngr7s3j0cg8pr6 00000000B6A5A470  418666214          2          1           2             0\r\nak90gdq0udv37 00000000B6E3C6B0 2175200359          2          2           2             1<\/pre>\n<p>\u5bf9\u4e8e\u5176\u4ed6\u7528\u6237\u800c\u8a00\uff0c\u90fd\u53ef\u4ee5\u4f7f\u7528\u5c06\u8868\u7684\u67e5\u8be2\u6743\u9650\u6388\u6743\u7ed9OWNER\u672c\u8eab\u7684\u65b9\u6cd5\uff0c\u4f46\u662f\u6d4b\u8bd5\u7528\u6237\u672c\u8eab\u4e3aSYS\uff0c\u56e0\u6b64\u9700\u8981\u5176\u4ed6\u7528\u6237\u6388\u6743\uff0c\u65b9\u4fbf\u8d77\u89c1\u4f7f\u7528\u4e86\u6388\u6743\u7ed9PUBLIC\u7684\u65b9\u5f0f\u3002\u53ef\u4ee5\u770b\u5230\uff0c\u8fd9\u79cd\u65b9\u5f0f\u540c\u6837\u53ef\u4ee5\u751f\u6548\uff0c\u4f46\u662f\u4ecd\u7136\u5b58\u5728\u6253\u51fb\u9762\u8fc7\u5927\u7684\u95ee\u9898\u3002\u5bf9\u4e8e\u7cfb\u7edf\u4e2d\u4e00\u4e2a\u9891\u7e41\u8bbf\u95ee\u7684\u8868\uff0c\u5f88\u53ef\u80fd\u8fd9\u4e2a\u6388\u6743\u7684\u64cd\u4f5c\uff0c\u5bfc\u81f4\u5c11\u5219\u51e0\u5341\uff0c\u591a\u5219\u51e0\u767e\u4e2aSQL\u90fd\u662f\u5931\u6548\uff0c\u8fd9\u4e2a\u98ce\u9669\u4ecd\u7136\u4e0d\u53ef\u5c0f\u89d1\u3002<br \/>\n\u90a3\u4e48\u5bf9\u4e8e\u5c31\u6ca1\u6709\u4e00\u4e2a\u53ef\u4ee5\u5c06\u7c92\u5ea6\u63a7\u5236\u5728SQL\u672c\u8eab\u4e0a\u7684\u65b9\u6cd5\u5417\uff1f\u572811g\u4e2d\uff0cOracle\u7684DBMS_SHARED_POOL\u5305\u65b0\u589e\u4e86PURGE\u529f\u80fd\uff0c\u53ef\u4ee5\u5b8c\u7f8e\u7684\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\uff0c\u8fd9\u4e2a\u65b9\u6cd5\u572810.2.0.4\u548c10.2.0.5\u8865\u4e01\u96c6\u4e2d\u4e5f\u88ab\u6dfb\u52a0\u8fdb\u6765\uff0c\u4f7f\u5f9710.2\u7684\u9ad8\u7248\u672c\u540c\u6837\u53ef\u4ee5\u5b9e\u73b0\u8fd9\u4e2a\u529f\u80fd\uff0c\u4f7f\u7528\u65b9\u6cd5\u5f88\u7b80\u5355\uff1a<\/p>\n<pre lang='SQL'>SQL> select count(*) from dual;\r\n  COUNT(*)\r\n----------\r\n         1\r\nSQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations\r\n  2  from v$sqlarea\r\n  3  where sql_text = 'select count(*) from dual';\r\nSQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS\r\n------------- ---------------- ---------- ---------- ---------- ----------- -------------\r\n4m94ckmu16f9k 00000000B6C61FC0 4094900530          1          2           1             1\r\nSQL> select 1 from dual;\r\n         1\r\n----------\r\n         1\r\nSQL> select * from dual;\r\nD\r\n-\r\nX\r\nSQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations\r\n  2  from v$sqlarea\r\n  3  where lower(sql_text) like '%dual%';\r\nSQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS\r\n------------- ---------------- ---------- ---------- ---------- ----------- -------------\r\ngr7s3j0cg8pr6 00000000B6A5A470  418666214          3          1           3             0\r\n520mkxqpf15q8 00000000B6DD9610 2866845384          1          2           1             1\r\nak90gdq0udv37 00000000B6E3C6B0 2175200359          3          2           3             1\r\n4m94ckmu16f9k 00000000B6C61FC0 4094900530          1          2           1             1\r\na5ks9fhw2v9s1 00000000B698DA88  942515969          1          2           1             1\r\nSQL> exec dbms_shared_pool.purge('00000000B6C61FC0,4094900530', 'c')\r\nPL\/SQL procedure successfully completed.\r\nSQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations\r\n  2  from v$sqlarea\r\n  3  where lower(sql_text) like '%dual%';\r\nSQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS\r\n------------- ---------------- ---------- ---------- ---------- ----------- -------------\r\ngr7s3j0cg8pr6 00000000B6A5A470  418666214          4          1           4             0\r\n520mkxqpf15q8 00000000B6DD9610 2866845384          1          2           1             1\r\nak90gdq0udv37 00000000B6E3C6B0 2175200359          3          2           3             1\r\na5ks9fhw2v9s1 00000000B698DA88  942515969          1          2           1             1<\/pre>\n<p>\u8fc7\u7a0bPURGE\u7684\u7b2c\u4e00\u4e2a\u53c2\u6570\u4e3aV$SQLAREA\u4e2d\u7528\u9017\u53f7\u5206\u9694\u7684ADDRESS\u5217\u548cHASH_VALUE\u5217\u7684\u503c\uff0c\u7b2c\u4e8c\u4e2a\u53c2\u6570\u2019c\u2019\u8868\u793aPURGE\u7684\u5bf9\u8c61\u662fCURSOR\uff0c\u4e0d\u8fc7\u5b9e\u9645\u4e0a\u8fd9\u91cc\u53ef\u4ee5\u4f7f\u7528\u9664\u4e86P\uff08PROCEDURE\/FUNCTION\/PACKAGE\uff09\u3001T\uff08TYPE\uff09\u3001R\uff08TRIGGER\uff09\u548cQ\uff08SEQUENCE\uff09\u7684\u4efb\u4f55\u503c<br \/>\n\u4f7f\u7528\u8fd9\u79cd\u65b9\u6cd5\uff0c\u5c31\u53ef\u4ee5\u7cbe\u786e\u7684\u5c06\u4e00\u4e2aSQL\u4ece\u5171\u4eab\u6c60\u4e2d\u5220\u9664\uff0c\u4ece\u800c\u4f7f\u5f97Oracle\u4e3a\u8fd9\u4e2aSQL\u91cd\u65b0\u751f\u6210\u6267\u884c\u8ba1\u5212\u3002\u8fd9\u79cd\u65b9\u6cd5\u53ea\u9488\u5bf9\u5355\u4e2aSQL\u8bed\u53e5\uff0c\u4f7f\u5f97\u89e3\u51b3\u95ee\u9898\u7684\u540c\u65f6\u4e0d\u4f1a\u9020\u6210\u4efb\u4f55\u7684\u8bef\u4f24\u3002<br \/>\n\u4e0d\u8fc7\u9700\u8981\u6ce8\u610f\u4e00\u70b9\uff0c\u572810.2.0.4\u4e2d\uff0c\u867d\u7136PURGE\u8fc7\u7a0b\u5df2\u7ecf\u5b58\u5728\uff0c\u4f46\u662f\u8981\u4f7f\u8fd9\u4e2a\u8fc7\u7a0b\u53ef\u4ee5\u771f\u6b63\u7684\u751f\u6548\uff0c\u8fd8\u5fc5\u987b\u8bbe\u7f6e\u4e00\u4e2aEVENT\uff1a<\/p>\n<pre lang='SQL'>SQL> alter system set event = '5614566 trace name context forever' scope = spfile;\r\nSystem altered.<\/pre>\n<p>\u8bbe\u7f6eEVENT\u540e\u9700\u8981\u91cd\u542f\uff0cDBMS_SHARED_POOL\u7684PURGE\u624d\u53ef\u4ee5\u751f\u6548\u3002\u4e5f\u5c31\u662f\u8bf4\uff0c\u9664\u975e\u63d0\u524d\u8fdb\u884c\u8fc7\u8bbe\u7f6e\uff0c\u5426\u5219\u8fd9\u4e2aPURGE\u7684\u529f\u80fd\u5bf9\u4e8e\u4e00\u4e2a\u4ea7\u54c1\u73af\u5883\u800c\u8a00\uff0c\u5fc5\u987b\u572810.2.0.5\u4ee5\u4e0a\u7248\u672c\u624d\u53ef\u4ee5\u4f7f\u7528\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5982\u679cOracle\u7684\u4f18\u5316\u5668\u4ea7\u751f\u4e86\u67d0\u79cd\u9519\u8bef\u7684\u6267\u884c\u8ba1\u5212\uff0c\u6216\u8005\u6211\u4eec\u5e0c\u671bOracle\u5bf9\u4e8e\u67d0\u4e2aSQL\u91cd\u65b0\u8fdb\u884c\u5206\u6790\uff0c\u90a3\u4e48\u5c31\u9700\u8981\u8fd9\u4e2aSQL\u7684\u6267\u884c\u8ba1\u5212\u5728\u5171\u4eab\u6c60\u4e2d\u8fc7\u671f\uff0c\u800c\u7b80\u5355\u7684\u65b9\u6cd5\u572810.2.0.4\u4ee5\u540e\u624d\u51fa\u73b0\u3002 \u5bf9\u4e8e\u4ee5\u524d\u7684\u7248\u672c\u800c\u8a00\uff0c\u6700\u663e\u800c\u6613\u89c1\u7684\u65b9\u6cd5\u83ab\u8fc7\u4e8e\u76f4\u63a5\u5237\u65b0\u5171\u4eab\u6c60\uff0c\u4f46\u662f\u5982\u679c\u662f\u6570\u636e\u5e93\u4e2d\u7edd\u5927\u90e8\u5206\u7684SQL\u90fd\u5b58\u5728\u95ee\u9898\uff0c\u90a3\u4e48\u8fd9\u79cd\u65b9\u6cd5\u65e0\u53ef\u539a\u975e\uff0c\u4e5f\u53ef\u80fd\u662f\u89c1\u6548\u6700\u5feb\u7684\u65b9\u6cd5\uff0c\u800c\u5982\u679c\u6570\u636e\u5e93\u4e2d\u4ec5\u4ec5\u662f\u4e2a\u522b\u7684SQL\u5b58\u5728\u95ee\u9898\uff0c\u90a3\u4e48\u8fd9\u79cd\u65b9\u6cd5\u5c31\u8fc7\u4e8e\u66b4\u529b\u4e86\u3002 SQL> select count(*) from dual; COUNT(*) &#8212;&#8212;&#8212;- 1 SQL> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations 2 from v$sqlarea 3 where sql_text = &#8216;select count(*) from dual&#8217;; SQL_ID ADDRESS HASH_VALUE EXECUTIONS LOADS PARSE_CALLS INVALIDATIONS &#8212;&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;- &hellip; <a href=\"https:\/\/yangtingkun.net\/?p=708\">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":[588,591,592,484,246,593],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-bq","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/708"}],"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=708"}],"version-history":[{"count":1,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/708\/revisions"}],"predecessor-version":[{"id":709,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/708\/revisions\/709"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=708"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=708"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=708"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}