{"id":266,"date":"2018-07-20T14:15:24","date_gmt":"2018-07-20T06:15:24","guid":{"rendered":"http:\/\/www.inhhh.com\/blog\/?p=266"},"modified":"2018-07-20T19:22:50","modified_gmt":"2018-07-20T11:22:50","slug":"oracel-%e5%a4%87%e4%bb%bd%e6%95%b0%e6%8d%ae%e5%ba%93%e9%94%99%e8%af%af-ora-3041-%e8%a7%a3%e5%86%b3%e5%8a%9e%e6%b3%95","status":"publish","type":"post","link":"http:\/\/www.inhhh.com\/blog\/?p=266","title":{"rendered":"ORACEL \u5907\u4efd\u6570\u636e\u5e93\u9519\u8bef ORA-3041 \u89e3\u51b3\u529e\u6cd5"},"content":{"rendered":"<p>\u8f6c\u81ea\uff1ahttps:\/\/blog.csdn.net\/tom0421\/article\/details\/693985<\/p>\n<p>\u70b9\u8bc4\uff1a\u6587\u5355\u5199\u5f97\u5f88\u8be6\u7ec6\uff0c<\/p>\n<h2>\u8bca\u65ad\u5e76\u89e3\u51b3ORA-04031 \u9519\u8bef<\/h2>\n<p>\u5f53\u6211\u4eec\u5728\u5171\u4eab\u6c60\u4e2d\u8bd5\u56fe\u5206\u914d\u5927\u7247\u7684\u8fde\u7eed\u5185\u5b58\u5931\u8d25\u7684\u65f6\u5019\uff0cOracle\u9996\u5148\u6e05\u9664\u6c60\u4e2d\u5f53\u524d\u6ca1\u4f7f\u7528\u7684\u6240\u6709\u5bf9\u8c61\uff0c\u4f7f\u7a7a\u95f2\u5185\u5b58\u5757\u5408\u5e76\u3002\u5982\u679c\u4ecd\u7136\u6ca1\u6709\u8db3\u591f\u5927\u5355\u4e2a\u7684\u5927\u5757\u5185\u5b58\u6ee1\u8db3\u8bf7\u6c42,\u5c31\u4f1a\u4ea7\u751fORA-04031 \u9519\u8bef\u3002<\/p>\n<p>\u5f53\u8fd9\u4e2a\u9519\u8bef\u51fa\u73b0\u7684\u65f6\u5019\u4f60\u5f97\u5230\u7684\u9519\u8bef\u89e3\u91ca\u4fe1\u606f\u7c7b\u4f3c\u5982\u4e0b:<\/p>\n<blockquote>\n<pre>04031, 00000, \"unable to allocate %s bytes of shared memory (\/\"%s\/\",\/\"%s\/\",\/\"%s\/\",\/\"%s\/\")\" \r\n\/\/ *Cause: More shared memory is needed than was allocated in the shared \r\n\/\/ pool. \r\n\/\/ *Action: If the shared pool is out of memory, either use the \r\n\/\/ dbms_shared_pool package to pin large packages, \r\n\/\/ reduce your use of shared memory, or increase the amount of \r\n\/\/ available shared memory by increasing the value of the \r\n\/\/ INIT.ORA parameters \"shared_pool_reserved_size\" and \r\n\/\/ \"shared_pool_size\". \r\n\/\/ If the large pool is out of memory, increase the INIT.ORA \r\n\/\/ parameter \"large_pool_size\".\r\n\r\n<\/pre>\n<\/blockquote>\n<h2><a name=\"t1\"><\/a>1.\u5171\u4eab\u6c60\u76f8\u5173\u7684\u5b9e\u4f8b\u53c2\u6570<\/h2>\n<p>\u5728\u7ee7\u7eed\u4e4b\u524d\uff0c\u6709\u5fc5\u8981\u7406\u89e3\u4e0b\u9762\u7684\u5b9e\u4f8b\u53c2\u6570:<\/p>\n<blockquote>\n<ul>\n<li>SHARED_POOL_SIZE<br \/>\n\u8fd9\u4e2a\u53c2\u6570\u6307\u5b9a\u4e86\u5171\u4eab\u6c60\u7684\u5927\u5c0f\uff0c\u5355\u4f4d\u662f\u5b57\u8282\u3002\u53ef\u4ee5\u63a5\u53d7\u6570\u5b57\u503c\u6216\u8005\u6570\u5b57\u540e\u9762\u8ddf\u4e0a\u540e\u7f00&#8221;K&#8221; \u6216 &#8220;M&#8221; \u3002&#8221;K&#8221;\u4ee3\u8868\u5343\u5b57\u8282, &#8220;M&#8221;\u4ee3\u8868\u5146\u5b57\u8282\u3002<\/li>\n<li>SHARED_POOL_RESERVED_SIZE<br \/>\n\u6307\u5b9a\u4e86\u4e3a\u5171\u4eab\u6c60\u5185\u5b58\u4fdd\u7559\u7684\u7528\u4e8e\u5927\u7684\u8fde\u7eed\u8bf7\u6c42\u7684\u5171\u4eab\u6c60\u7a7a\u95f4\u3002\u5f53\u5171\u4eab\u6c60\u788e\u7247\u5f3a\u5236\u4f7f Oracle \u67e5\u627e\u5e76\u91ca\u653e\u5927\u5757\u672a\u4f7f\u7528\u7684\u6c60\u6765\u6ee1\u8db3\u5f53\u524d\u7684\u8bf7\u6c42\u7684\u65f6\u5019\uff0c\u8fd9\u4e2a\u53c2\u6570\u548cSHARED_POOL_RESERVED_MIN_ALLOC \u53c2\u6570\u4e00\u8d77\u53ef\u4ee5\u7528\u6765\u907f\u514d\u6027\u80fd\u4e0b\u964d\u3002<\/li>\n<li>\u8fd9\u4e2a\u53c2\u6570\u7406\u60f3\u7684\u503c\u5e94\u8be5\u5927\u5230\u8db3\u4ee5\u6ee1\u8db3\u4efb\u4f55\u5bf9\u4fdd\u7559\u5217\u8868\u4e2d\u5185\u5b58\u7684\u8bf7\u6c42\u626b\u63cf\u800c\u65e0\u9700\u4ece\u5171\u4eab\u6c60\u4e2d\u5237\u65b0\u5bf9\u8c61\u3002\u65e2\u7136\u64cd\u4f5c\u7cfb\u7edf\u5185\u5b58\u53ef\u4ee5\u9650\u5236\u5171\u4eab\u6c60\u7684\u5927\u5c0f\uff0c\u4e00\u822c\u6765\u8bf4\uff0c\u4f60\u5e94\u8be5\u8bbe\u5b9a\u8fd9\u4e2a\u53c2\u6570\u4e3a SHARED_POOL_SIZE \u53c2\u6570\u7684 10% \u5927\u5c0f\u3002<\/li>\n<li>SHARED_POOL_RESERVED_MIN_ALLOC \u8fd9\u4e2a\u53c2\u6570\u7684\u503c\u63a7\u5236\u4fdd\u7559\u5185\u5b58\u7684\u5206\u914d\u3002\u5982\u679c\u4e00\u4e2a\u8db3\u591f\u5c3a\u5bf8\u7684\u5927\u5757\u5185\u5b58\u5728\u5171\u4eab\u6c60\u7a7a\u95f2\u5217\u8868\u4e2d\u6ca1\u80fd\u627e\u5230\uff0c\u5185\u5b58\u5c31\u4ece\u4fdd\u7559\u5217\u8868\u4e2d\u5206\u914d\u4e00\u5757\u6bd4\u8fd9\u4e2a\u503c\u5927\u7684\u7a7a\u95f4\u3002\u9ed8\u8ba4\u7684\u503c\u5bf9\u4e8e\u5927\u591a\u6570\u7cfb\u7edf\u6765\u8bf4\u90fd\u8db3\u591f\u4e86\u3002\u5982\u679c\u4f60\u52a0\u5927\u8fd9\u4e2a\u503c\uff0c\u90a3\u4e48Oracle \u670d\u52a1\u5668\u5c06\u5141\u8bb8\u4ece\u8fd9\u4e2a\u4fdd\u7559\u5217\u8868\u4e2d\u66f4\u5c11\u7684\u5206\u914d\u5e76\u4e14\u5c06\u4ece\u5171\u4eab\u6c60\u5217\u8868\u4e2d\u8bf7\u6c42\u66f4\u591a\u7684\u5185\u5b58\u3002\u8fd9\u4e2a\u53c2\u6570\u5728Oracle 8i \u548c\u66f4\u9ad8\u7684\u7248\u672c\u4e2d\u662f\u9690\u85cf\u7684\u3002\u63d0\u4ea4\u5982\u4e0b\u7684\u8bed\u53e5\u67e5\u627e\u8fd9\u4e2a\u53c2\u6570\u503c:\n<pre>SELECT   nam.ksppinm NAME, val.ksppstvl VALUE\r\n    FROM x$ksppi nam, x$ksppsv val\r\n   WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%shared%'\r\nORDER BY 1;\r\n<\/pre>\n<p>10g \u6ce8\u91ca\uff1aOracle 10g \u7684\u4e00\u4e2a\u65b0\u7279\u6027\u53eb\u505a &#8220;\u81ea\u52a8\u5185\u5b58\u7ba1\u7406&#8221; \u5141\u8bb8DBA\u4fdd\u7559\u4e00\u4e2a\u5171\u4eab\u5185\u5b58\u6c60\u6765\u5206shared pool,buffer cache, java pool \u548clarge pool\u3002\u4e00\u822c\u6765\u8bf4\uff0c\u5f53\u6570\u636e\u5e93\u9700\u8981\u5206\u914d\u4e00\u4e2a\u5927\u7684\u5bf9\u8c61\u5230\u5171\u4eab\u6c60\u4e2d\u5e76\u4e14\u4e0d\u80fd\u627e\u5230\u8fde\u7eed\u7684\u53ef\u7528\u7a7a\u95f4\uff0c\u5c06\u81ea\u52a8\u4f7f\u7528\u5176\u4ed6SGA\u7ed3\u6784\u7684\u7a7a\u95f2\u7a7a\u95f4\u6765\u589e\u52a0\u5171\u4eab\u6c60\u7684\u5927\u5c0f \u3002\u65e2\u7136\u7a7a\u95f4\u5206\u914d\u662fOracle\u81ea\u52a8\u7ba1\u7406\u7684\uff0cora-4031\u51fa\u9519\u7684\u53ef\u80fd\u6027\u5c06\u5927\u5927\u964d\u4f4e\u3002\u81ea\u52a8\u5185\u5b58\u7ba1\u7406\u5728\u521d\u59cb\u5316\u53c2\u6570SGA_TARGET\u5927\u4e8e0\u7684\u65f6\u5019\u88ab\u6fc0\u6d3b\u3002\u5f53\u524d\u8bbe\u5b9a\u53ef\u4ee5\u901a\u8fc7\u67e5\u8be2v$sga_dynamic_components \u89c6\u56fe\u83b7\u5f97\u3002\u8bf7\u53c2\u800310g\u7ba1\u7406\u624b\u518c\u4ee5\u5f97\u5230\u66f4\u591a\u5185\u5bb9 \u3002<\/li>\n<\/ul>\n<\/blockquote>\n<h2><a name=\"t2\"><\/a>2.\u8bca\u65adORA-04031 \u9519\u8bef<\/h2>\n<p>\u6ce8\uff1a\u5927\u591a\u6570\u7684\u5e38\u89c1\u7684 ORA-4031 \u7684\u4ea7\u751f\u90fd\u548c SHARED POOL SIZE \u6709\u5173\uff0c\u8fd9\u7bc7\u6587\u7ae0\u4e2d\u7684\u8bca\u65ad\u6b65\u9aa4\u5927\u591a\u90fd\u662f\u5173\u4e8e\u5171\u4eab\u6c60\u7684\u3002\u3000\u5bf9\u4e8e\u5176\u5b83\u65b9\u9762\u5982Large_pool\u6216\u662fJava_pool\uff0c\u5185\u5b58\u5206\u914d\u7b97\u6cd5\u90fd\u662f\u76f8\u4f3c\u7684\uff0c\u4e00\u822c\u6765\u8bf4\u90fd\u662f\u56e0\u4e3a\u7ed3\u6784\u4e0d\u591f\u5927\u9020\u6210\u3002<\/p>\n<p>ORA-04031 \u53ef\u80fd\u662f\u56e0\u4e3a SHARED POOL \u4e0d\u591f\u5927\uff0c\u6216\u662f\u56e0\u4e3a\u788e\u7247\u95ee\u9898\u5bfc\u81f4\u6570\u636e\u5e93\u4e0d\u80fd\u627e\u5230\u8db3\u591f\u5927\u7684\u5185\u5b58\u5757\u3002<\/p>\n<p>ORA-04031 \u9519\u8bef\u901a\u5e38\u662f\u56e0\u4e3a\u5e93\u9ad8\u901f\u7f13\u51b2\u4e2d\u6216\u5171\u4eab\u6c60\u4fdd\u7559\u7a7a\u95f4\u4e2d\u7684\u788e\u7247\u3002 \u5728\u52a0\u5927\u5171\u4eab\u6c60\u5927\u5c0f\u7684\u65f6 \u5019\u8003\u8651\u8c03\u6574\u5e94\u7528,\u4f7f\u7528\u5171\u4eab\u7684SQL \u5e76\u4e14\u8c03\u6574\u5982\u4e0b\u7684\u53c2\u6570\uff1a<\/p>\n<blockquote>\n<pre>SHARED_POOL_SIZE,\r\nSHARED_POOL_RESERVED_SIZE,\r\nSHARED_POOL_RESERVED_MIN_ALLOC.\r\n<\/pre>\n<\/blockquote>\n<p>\u9996\u5148\u5224\u5b9a\u662f\u5426ORA-04031 \u9519\u8bef\u662f\u7531\u5171\u4eab\u6c60\u4fdd\u7559\u7a7a\u95f4\u4e2d\u7684\u5e93\u9ad8\u901f\u7f13\u51b2\u7684\u788e\u7247\u4ea7\u751f\u7684\u3002\u63d0\u4ea4\u4e0b\u7684\u67e5\u8be2\uff1a<\/p>\n<blockquote>\n<pre>SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures,\r\n       last_failure_size\r\n  FROM v$shared_pool_reserved;\r\n<\/pre>\n<\/blockquote>\n<p>\u5982\u679c:<\/p>\n<blockquote><p>REQUEST_FAILURES &gt; 0 \u5e76\u4e14 LAST_FAILURE_SIZE &gt; SHARED_POOL_RESERVED_MIN_ALLOC<\/p><\/blockquote>\n<p>\u90a3\u4e48ORA-04031 \u9519\u8bef\u5c31\u662f\u56e0\u4e3a\u5171\u4eab\u6c60\u4fdd\u7559\u7a7a\u95f4\u7f3a\u5c11\u8fde\u7eed\u7a7a\u95f4\u6240\u81f4\u3002\u8981\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898,\u53ef\u4ee5\u8003\u8651\u52a0\u5927SHARED_POOL_RESERVED_MIN_ALLOC \u6765\u964d\u4f4e\u7f13\u51b2\u8fdb\u5171 \u4eab\u6c60\u4fdd\u7559\u7a7a\u95f4\u7684\u5bf9\u8c61\u6570\u76ee\uff0c\u5e76\u589e\u5927 SHARED_POOL_RESERVED_SIZE \u548c SHARED_POOL_SIZE \u6765\u52a0\u5927\u5171\u4eab\u6c60\u4fdd\u7559\u7a7a\u95f4\u7684\u53ef\u7528\u5185\u5b58\u3002<\/p>\n<p>\u5982\u679c\uff1a<\/p>\n<blockquote><p>REQUEST_FAILURES &gt; 0 \u5e76\u4e14 LAST_FAILURE_SIZE &lt; SHARED_POOL_RESERVED_MIN_ALLOC<\/p><\/blockquote>\n<p>\u6216\u8005<\/p>\n<blockquote><p>REQUEST_FAILURES \u7b49\u4e8e0 \u5e76\u4e14 LAST_FAILURE_SIZE &lt; SHARED_POOL_RESERVED_MIN_ALLOC<\/p><\/blockquote>\n<p>\u90a3\u4e48\u662f\u56e0\u4e3a\u5728\u5e93\u9ad8\u901f\u7f13\u51b2\u7f3a\u5c11\u8fde\u7eed\u7a7a\u95f4\u5bfc\u81f4ORA-04031 \u9519\u8bef\u3002<\/p>\n<p>\u7b2c\u4e00\u6b65\u5e94\u8be5\u8003\u8651\u964d\u4f4eSHARED_POOL_RESERVED_MIN_ALLOC \u4ee5\u653e\u5165\u66f4\u591a\u7684\u5bf9\u8c61\u5230\u5171\u4eab\u6c60\u4fdd\u7559\u7a7a\u95f4\u4e2d\u5e76\u4e14\u52a0\u5927SHARED_POOL_SIZE\u3002<\/p>\n<h2><a name=\"t3\"><\/a>3.\u89e3\u51b3ORA-04031 \u9519\u8bef<\/h2>\n<ul>\n<li><strong>ORACLE BUG<\/strong>Oracle\u63a8\u8350\u5bf9\u4f60\u7684\u7cfb\u7edf\u6253\u4e0a\u6700\u65b0\u7684PatchSet\u3002\u5927\u591a\u6570\u7684ORA-04031\u9519\u8bef\u90fd\u548cBUG \u76f8\u5173\uff0c\u53ef\u4ee5\u901a\u8fc7\u4f7f\u7528\u8fd9\u4e9b\u8865\u4e01\u6765\u907f\u514d\u3002\u4e0b\u9762\u8868\u4e2d\u603b\u7ed3\u548c\u548c\u8fd9\u4e2a\u9519\u8bef\u76f8\u5173\u7684\u6700\u5e38\u89c1\u7684BUG\u3001\u53ef\u80fd\u7684\u73af\u5883\u548c\u4fee\u8865\u8fd9\u4e2a\u95ee\u9898\u7684\u8865\u4e01\u3002<br \/>\n<table>\n<tbody>\n<tr>\n<td><strong>BUG<\/strong><\/td>\n<td><strong>\u63cf\u8ff0<\/strong><\/td>\n<td><strong>Workaround<\/strong><\/td>\n<td><strong>Fixed<\/strong><\/td>\n<\/tr>\n<tr>\n<td>&lt;Bug:1397603&gt;<\/td>\n<td>ORA-4031\/SGA memory leak of PERMANENT memory occurs for buffer handles<\/td>\n<td>_db_handles_cached = 0<\/td>\n<td>901\/ 8172<\/td>\n<\/tr>\n<tr>\n<td>&lt;Bug:1640583&gt;<\/td>\n<td>ORA-4031 due to leak \/ cache buffer chain contention from AND-EQUAL access<\/td>\n<td>Not available<\/td>\n<td>8171\/901<\/td>\n<\/tr>\n<tr>\n<td>&lt;Bug:1318267&gt;<\/td>\n<td>INSERT AS SELECT statements may<br \/>\nnot be shared when they should be<br \/>\nif TIMED_STATISTICS. It can lead to ORA-4031<\/td>\n<td>_SQLEXEC_PROGRESSION_COST=0<\/td>\n<td>8171\/8200<\/td>\n<\/tr>\n<tr>\n<td>&lt;Bug:1193003&gt;<\/td>\n<td>Cursors may not be shared in 8.1<br \/>\nwhen they should be<\/td>\n<td>Not available<\/td>\n<td>8162\/8170\/ 901<\/td>\n<\/tr>\n<tr>\n<td>&lt;Bug:2104071&gt;<\/td>\n<td>ORA-4031\/excessive &#8220;miscellaneous&#8221; shared pool usage possible. (many PINS)<\/td>\n<td>None-&gt; This is known to affect the XML parser.<\/td>\n<td>8174, 9013, 9201<\/td>\n<\/tr>\n<tr>\n<td>&lt;Note:263791.1&gt;<\/td>\n<td>Several number of BUGs related to ORA-4031 erros were fixed in the 9.2.0.5 patchset<\/td>\n<td>Not available<\/td>\n<td>9205<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<li><strong>\u7f16\u8bd1Java\u4ee3\u7801\u65f6\u51fa\u73b0\u7684ORA-4031<\/strong>\u5728\u4f60\u7f16\u8bd1Java\u4ee3\u7801\u7684\u65f6\u5019\u5982\u679c\u5185\u5b58\u6ea2\u51fa\uff0c\u4f60\u4f1a\u770b\u5230\u9519\u8bef\uff1a\n<pre>A SQL exception occurred while compiling: : \r\nORA-04031: unable to allocate bytes of shared memory \r\n(\"shared pool\",\"unknown object\",\"joxlod: init h\", \"JOX: ioc_allocate_pal\") \r\n<\/pre>\n<p>\u89e3\u51b3\u529e\u6cd5\u662f\u5173\u95ed\u6570\u636e\u5e93\u7136\u540e\u628a\u53c2\u6570 JAVA_POOL_SIZE \u8bbe\u5b9a\u4e3a\u4e00\u4e2a\u8f83\u5927\u7684\u503c\u3002\u8fd9\u91cc\u9519\u8bef\u4fe1\u606f\u4e2d\u63d0\u5230\u7684 &#8220;shared pool&#8221; \u5176\u5b9e\u5171\u4eab\u5168\u5c40\u533a(SGA)\u6ea2\u51fa\u7684\u8bef\u5bfc\uff0c\u5e76\u4e0d\u8868\u793a\u4f60\u9700\u8981\u589e\u52a0SHARED_POOL_SIZE\uff0c\u76f8\u53cd\uff0c\u4f60\u5fc5\u987b\u52a0\u5927 JAVA_POOL_SIZE \u53c2\u6570\u7684\u503c\uff0c\u7136\u540e\u91cd\u542f\u52a8\u7cfb\u7edf\uff0c\u518d\u8bd5\u4e00\u4e0b\u3002\u53c2\u8003: &lt;Bug:2736601&gt; \u3002<\/li>\n<li><strong>\u5c0f\u7684\u5171\u4eab\u6c60\u5c3a\u5bf8<\/strong>\u5f88\u591a\u60c5\u51b5\u4e0b\uff0c\u5171\u4eab\u6c60\u8fc7\u5c0f\u80fd\u591f\u5bfc\u81f4ORA-04031\u9519\u8bef\u3002\u4e0b\u9762\u4fe1\u606f\u6709\u52a9\u4e8e\u4f60\u8c03\u6574\u5171\u4eab\u6c60\u5927\u5c0f\uff1a\n<ul>\n<li><strong>\u5e93\u9ad8\u901f\u7f13\u51b2\u547d\u4e2d\u7387<\/strong>\u547d\u4e2d\u7387\u6709\u52a9\u4e8e\u4f60\u8861\u91cf\u5171\u4eab\u6c60\u7684\u4f7f\u7528\uff0c\u6709\u591a\u5c11\u8bed\u53e5\u9700\u8981\u88ab\u89e3\u6790\u800c\u4e0d\u662f\u91cd\u7528\u3002\u4e0b\u9762\u7684SQL\u8bed\u53e5\u6709\u52a9\u4e8e\u4f60\u8ba1\u7b97\u5e93\u9ad8\u901f\u7f13\u51b2\u7684\u547d\u4e2d\u7387\uff1a\n<pre>SELECT SUM(PINS) \"EXECUTIONS\", \r\n            SUM(RELOADS) \"CACHE MISSES WHILE EXECUTING\" \r\n            FROM V$LIBRARYCACHE; \r\n<\/pre>\n<p>\u5982\u679c\u4e22\u5931\u8d85\u8fc71%,\u90a3\u4e48\u5c1d\u8bd5\u901a\u8fc7\u52a0\u5927\u5171\u4eab\u6c60\u7684\u5927\u5c0f\u6765\u51cf\u5c11\u5e93\u9ad8\u901f\u7f13\u51b2\u4e22\u5931\u3002<\/li>\n<li><strong>\u5171\u4eab\u6c60\u5927\u5c0f\u8ba1\u7b97<\/strong>\u8981\u8ba1\u7b97\u6700\u9002\u5408\u4f60\u5de5\u4f5c\u8d1f\u8f7d\u7684\u5171\u4eab\u6c60\u5927\u5c0f\uff0c\u8bf7\u53c2\u8003\uff1a&lt;Note:1012046.6&gt;: HOW TO CALCULATE YOUR SHARED POOL SIZE.<\/li>\n<\/ul>\n<\/li>\n<li><strong>\u5171\u4eab\u6c60\u788e\u7247<\/strong>\u6bcf\u4e00\u6b21\uff0c\u9700\u8981\u88ab\u6267\u884c\u7684SQL \u6216\u8005PL\/SQL \u8bed\u53e5\u7684\u89e3\u6790\u5f62\u5f0f\u8f7d\u5165\u5171\u4eab\u6c60\u4e2d\u90fd\u9700\u8981\u4e00\u5757\u7279\u5b9a\u7684\u8fde\u7eed\u7684\u7a7a\u95f4\u3002\u6570\u636e\u5e93\u8981\u626b\u63cf\u7684\u7b2c\u4e00\u4e2a\u8d44\u6e90\u5c31\u662f\u5171\u4eab\u6c60\u4e2d\u7684\u7a7a\u95f2\u53ef\u7528\u5185\u5b58\u3002\u4e00\u65e6\u7a7a\u95f2\u5185\u5b58\u8017\u5c3d\uff0c\u6570\u636e\u5e93\u8981\u67e5\u627e\u4e00\u5757\u5df2\u7ecf\u5206\u914d\u4f46\u8fd8\u6ca1\u4f7f\u7528\u7684\u5185\u5b58\u51c6\u5907\u91cd\u7528\u3002\u5982\u679c\u8fd9\u6837\u7684\u786e\u5207\u5c3a\u5bf8\u7684\u5927\u5757\u5185\u5b58\u4e0d\u53ef\u7528\uff0c\u5c31\u7ee7\u7eed\u6309\u7167\u5982\u4e0b\u6807\u51c6\u5bfb\u627e\uff1a\n<ul>\n<li>\u5927\u5757(chunk)\u5927\u5c0f\u6bd4\u8bf7\u6c42\u7684\u5927\u5c0f\u5927<\/li>\n<li>\u7a7a\u95f4\u662f\u8fde\u7eed\u7684<\/li>\n<li>\u5927\u5757\u5185\u5b58\u662f\u53ef\u7528\u7684(\u800c\u4e0d\u662f\u6b63\u5728\u4f7f\u7528\u7684)<\/li>\n<\/ul>\n<p>\u8fd9\u6837\u5927\u5757\u7684\u5185\u5b58\u88ab\u5206\u5f00\uff0c\u5269\u4f59\u7684\u6dfb\u52a0\u5230\u76f8\u5e94\u7684\u7a7a\u95f2\u7a7a\u95f4\u5217\u8868\u4e2d\u3002\u5f53\u6570\u636e\u5e93\u4ee5\u8fd9\u79cd\u65b9\u5f0f\u64cd\u4f5c\u4e00\u6bb5\u65f6\u95f4\u4e4b\u540e\uff0c\u5171\u4eab\u6c60\u7ed3\u6784\u5c31\u4f1a\u51fa\u73b0\u788e\u7247\u3002<\/p>\n<p>\u5f53\u5171\u4eab\u6c60\u5b58\u5728\u788e\u7247\u7684\u95ee\u9898,\u5206\u914d\u4e00\u7247\u7a7a\u95f2\u7684\u7a7a\u95f4\u5c31\u4f1a\u82b1\u8d39\u66f4\u591a\u7684\u65f6\u95f4,\u6570\u636e\u5e93\u6027\u80fd\u4e5f\u4f1a\u4e0b\u964d(\u6574\u4e2a\u64cd\u4f5c\u7684\u8fc7\u7a0b\u4e2d\uff0c&#8221;chunk allocation&#8221;\u88ab\u4e00\u4e2a\u53eb\u505a&#8221;shared pool latch&#8221; \u7684\u95e9\u6240\u63a7\u5236) \u6216\u8005\u662f\u51fa\u73b0 ORA-04031 \u9519\u8beferrors (\u5728\u6570\u636e\u5e93\u4e0d\u80fd\u627e\u5230\u4e00\u4e2a\u8fde\u7eed\u7684\u7a7a\u95f2\u5185\u5b58\u5757\u7684\u65f6\u5019)\u3002<\/p>\n<blockquote><p>\u53c2\u8003 &lt;Note:61623.1&gt;: \u53ef\u4ee5\u5f97\u5230\u5173\u4e8e\u5171\u4eab\u6c60\u788e\u7247\u7684\u8be6\u7ec6\u8ba8\u8bba\u3002<\/p><\/blockquote>\n<p>\u5982\u679cSHARED_POOL_SIZE \u8db3\u591f\u5927\uff0c\u5927\u591a\u6570\u7684 ORA-04031 \u9519\u8bef\u90fd\u662f\u7531\u5171\u4eab\u6c60\u4e2d\u7684\u52a8\u6001SQL \u788e\u7247\u5bfc\u81f4\u7684\u3002\u53ef\u80fd\u7684\u539f\u56e0\u5982\u4e0b\uff1a<\/p>\n<ul>\n<li>\u975e\u5171\u4eab\u7684SQL<\/li>\n<li>\u751f\u6210\u4e0d\u5fc5\u8981\u7684\u89e3\u6790\u8c03\u7528 (\u8f6f\u89e3\u6790)<\/li>\n<li>\u6ca1\u6709\u4f7f\u7528\u7ed1\u5b9a\u53d8\u91cf<\/li>\n<\/ul>\n<p>\u8981\u51cf\u5c11\u788e\u7247\u7684\u4ea7\u751f\u4f60\u9700\u8981\u786e\u5b9a\u662f\u524d\u9762\u63cf\u53d9\u7684\u51e0\u79cd\u53ef\u80fd\u7684\u56e0\u7d20\u3002\u53ef\u4ee5\u91c7\u53d6\u5982\u4e0b\u7684\u4e00\u4e9b\u65b9\u6cd5\uff0c\u5f53\u7136\u4e0d\u53ea\u5c40\u9650\u4e8e\u8fd9\u51e0\u79cd: \u5e94\u7528\u8c03\u6574\u3001\u6570\u636e\u5e93\u8c03\u6574\u6216\u8005\u5b9e\u4f8b\u53c2\u6570\u8c03\u6574\u3002<\/p>\n<blockquote><p>\u8bf7\u53c2\u8003 &lt;Note:62143.1&gt;\uff0c\u63cf\u8ff0\u4e86\u6240\u6709\u7684\u8fd9\u4e9b\u7ec6\u8282\u5185\u5bb9\u3002\u8fd9\u4e2a\u6ce8\u91ca\u8fd8\u5305\u62ec\u4e86\u5171\u4eab\u6c60\u5982\u4f55\u5de5\u4f5c\u7684\u7ec6\u8282\u3002<\/p><\/blockquote>\n<p>\u4e0b\u9762\u7684\u89c6\u56fe\u6709\u52a9\u4e8e\u4f60\u6807\u660e\u5171\u4eab\u6c60\u4e2d\u975e\u5171\u4eab\u7684SQL\/PLSQL\uff1a<\/p>\n<ul>\n<li><strong>V$SQLAREA \u89c6\u56fe<\/strong>\u8fd9\u4e2a\u89c6\u56fe\u4fdd\u5b58\u4e86\u5728\u6570\u636e\u5e93\u4e2d\u6267\u884c\u7684SQL \u8bed\u53e5\u548cPL\/SQL \u5757\u7684\u4fe1\u606f\u3002\u4e0b\u9762\u7684SQL \u8bed\u53e5\u53ef\u4ee5\u663e\u793a\u7ed9\u4f60\u5e26\u6709literal \u7684\u8bed\u53e5\u6216\u8005\u662f\u5e26\u6709\u7ed1\u5b9a\u53d8\u91cf\u7684\u8bed\u53e5\uff1a\n<pre>SELECT   SUBSTR (sql_text, 1, 40) \"SQL\", COUNT (*),\r\n         SUM (executions) \"TotExecs\"\r\n    FROM v$sqlarea\r\n   WHERE executions &lt; 5\r\nGROUP BY SUBSTR (sql_text, 1, 40)\r\n  HAVING COUNT (*) &gt; 30\r\nORDER BY 2;\r\n<\/pre>\n<p>\u6ce8: Having \u540e\u7684\u6570\u503c &#8220;30&#8221; \u53ef\u4ee5\u6839\u636e\u9700\u8981\u8c03\u6574\u4ee5\u5f97\u5230\u66f4\u4e3a\u8be6\u7ec6\u7684\u4fe1\u606f\u3002<\/li>\n<li><strong>X$KSMLRU \u89c6\u56fe<\/strong>\u8fd9\u4e2a\u56fa\u5b9a\u8868x$ksmlru \u8ddf\u8e2a\u5171\u4eab\u6c60\u4e2d\u5bfc\u81f4\u5176\u5b83\u5bf9\u8c61\u6362\u51fa(age out)\u7684\u5e94\u7528\u3002\u8fd9\u4e2a\u56fa\u5b9a\u8868\u53ef\u4ee5\u7528\u6765\u6807\u8bb0\u662f\u4ec0\u4e48\u5bfc\u81f4\u4e86\u5927\u7684\u5e94\u7528\u3002\u5982\u679c\u5f88\u591a\u5bf9\u8c61\u5728\u5171\u4eab\u6c60\u4e2d\u90fd\u88ab\u9636\u6bb5\u6027\u7684\u5237\u65b0\u53ef\u80fd\u5bfc\u81f4\u54cd\u5e94\u65f6\u95f4\u95ee\u9898\u5e76\u4e14\u6709\u53ef\u80fd\u5728\u5bf9\u8c61\u91cd\u8f7d\u5165\u5171\u4eab\u6c60\u4e2d\u7684\u65f6\u5019\u5bfc\u81f4\u5e93\u9ad8\u901f\u7f13\u51b2\u95e9\u7ade\u4e89\u95ee\u9898\u3002\u5173\u4e8e\u8fd9\u4e2ax$ksmlru \u8868\u7684\u4e00\u4e2a\u4e0d\u5bfb\u5e38\u7684\u5730\u65b9\u5c31\u662f\u5982\u679c\u6709\u4eba\u4ece\u8868\u4e2d\u9009\u53d6\u5185\u5bb9\u8fd9\u4e2a\u8868\u7684\u5185\u5bb9\u5c31\u4f1a\u88ab\u64e6\u9664\u3002\u8fd9\u6837\u8fd9\u4e2a\u56fa\u5b9a\u8868\u53ea\u5b58\u50a8\u66fe\u7ecf\u53d1\u751f\u7684\u6700\u5927\u7684\u5206\u914d\u3002\u8fd9\u4e2a\u503c\u5728\u9009\u62e9\u540e\u88ab\u91cd\u65b0\u8bbe\u5b9a\u8fd9\u6837\u63a5\u4e0b\u6765\u7684\u5927\u7684\u5206\u914d\u53ef\u4ee5\u88ab\u6807\u8bb0\uff0c\u5373\u4f7f\u5b83\u4eec\u4e0d\u5982\u5148\u524d\u7684\u5206\u914d\u8fc7\u7684\u5927\u3002\u56e0\u4e3a\u8fd9\u6837\u7684\u91cd\u7f6e\uff0c\u5728\u67e5\u8be2\u63d0\u4ea4\u540e\u7684\u7ed3\u679c\u4e0d\u53ef\u4ee5\u518d\u6b21\u5f97\u5230\uff0c\u4ece\u8868\u4e2d\u7684\u8f93\u51fa\u7684\u7ed3\u679c\u5e94\u8be5\u5c0f\u5fc3\u7684\u4fdd\u5b58\u3002\u76d1\u89c6\u8fd9\u4e2a\u56fa\u5b9a\u8868\u8fd0\u884c\u5982\u4e0b\u64cd\u4f5c\uff1a\n<pre>SELECT * FROM X$KSMLRU WHERE ksmlrsiz &gt; 0;\r\n<\/pre>\n<p>\u8fd9\u4e2a\u8868\u53ea\u53ef\u4ee5\u7528SYS\u7528\u6237\u767b\u5f55\u8fdb\u884c\u67e5\u8be2\u3002<\/li>\n<li><strong>X$KSMSP \u89c6\u56fe (\u7c7b\u4f3c\u5806Heapdump\u4fe1\u606f)<\/strong>\u4f7f\u7528\u8fd9\u4e2a\u89c6\u56fe\u80fd\u627e\u51fa\u5f53\u524d\u5206\u914d\u7684\u7a7a\u95f2\u7a7a\u95f4\uff0c\u6709\u52a9\u4e8e\u7406\u89e3\u5171\u4eab\u6c60\u788e\u7247\u7684\u7a0b\u5ea6\u3002\u5982\u6211\u4eec\u5728\u524d\u9762\u7684\u63cf\u8ff0\uff0c\u67e5\u627e\u4e3a\u6e38\u6807\u5206\u914d\u7684\u8db3\u591f\u7684\u5927\u5757\u5185\u5b58\u7684\u7b2c\u4e00\u4e2a\u5730\u65b9\u662f\u7a7a\u95f2\u5217\u8868( free list)\u3002 \u4e0b\u9762\u7684\u8bed\u53e5\u663e\u793a\u4e86\u7a7a\u95f2\u5217\u8868\u4e2d\u7684\u5927\u5757\u5185\u5b58:\n<pre>SELECT   '0 (&lt;140)' bucket, ksmchcls, 10 * TRUNC (ksmchsiz \/ 10) \"From\",\r\n         COUNT (*) \"Count\", MAX (ksmchsiz) \"Biggest\",\r\n         TRUNC (AVG (ksmchsiz)) \"AvgSize\", TRUNC (SUM (ksmchsiz)) \"Total\"\r\n    FROM x$ksmsp\r\n   WHERE ksmchsiz &lt; 140 AND ksmchcls = 'free'\r\nGROUP BY ksmchcls, 10 * TRUNC (ksmchsiz \/ 10)\r\nUNION ALL\r\nSELECT   '1 (140-267)' bucket, ksmchcls, 20 * TRUNC (ksmchsiz \/ 20),\r\n         COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) \"AvgSize\",\r\n         TRUNC (SUM (ksmchsiz)) \"Total\"\r\n    FROM x$ksmsp\r\n   WHERE ksmchsiz BETWEEN 140 AND 267 AND ksmchcls = 'free'\r\nGROUP BY ksmchcls, 20 * TRUNC (ksmchsiz \/ 20)\r\nUNION ALL\r\nSELECT   '2 (268-523)' bucket, ksmchcls, 50 * TRUNC (ksmchsiz \/ 50),\r\n         COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) \"AvgSize\",\r\n         TRUNC (SUM (ksmchsiz)) \"Total\"\r\n    FROM x$ksmsp\r\n   WHERE ksmchsiz BETWEEN 268 AND 523 AND ksmchcls = 'free'\r\nGROUP BY ksmchcls, 50 * TRUNC (ksmchsiz \/ 50)\r\nUNION ALL\r\nSELECT   '3-5 (524-4107)' bucket, ksmchcls, 500 * TRUNC (ksmchsiz \/ 500),\r\n         COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) \"AvgSize\",\r\n         TRUNC (SUM (ksmchsiz)) \"Total\"\r\n    FROM x$ksmsp\r\n   WHERE ksmchsiz BETWEEN 524 AND 4107 AND ksmchcls = 'free'\r\nGROUP BY ksmchcls, 500 * TRUNC (ksmchsiz \/ 500)\r\nUNION ALL\r\nSELECT   '6+ (4108+)' bucket, ksmchcls, 1000 * TRUNC (ksmchsiz \/ 1000),\r\n         COUNT (*), MAX (ksmchsiz), TRUNC (AVG (ksmchsiz)) \"AvgSize\",\r\n         TRUNC (SUM (ksmchsiz)) \"Total\"\r\n    FROM x$ksmsp\r\n   WHERE ksmchsiz &gt;= 4108 AND ksmchcls = 'free'\r\nGROUP BY ksmchcls, 1000 * TRUNC (ksmchsiz \/ 1000);\r\n<\/pre>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><a name=\"t4\"><\/a><strong>4. ORA-04031 \u9519\u8bef\u4e0e Large Pool<\/strong><\/h2>\n<p>\u5927\u6c60\u662f\u4e2a\u53ef\u9009\u7684\u5185\u5b58\u533a\uff0c\u4e3a\u4ee5\u4e0b\u7684\u64cd\u4f5c\u63d0\u4f9b\u5927\u5185\u5b58\u5206\u914d\uff1a<\/p>\n<ul>\n<li>MTS\u4f1a\u8bdd\u5185\u5b58\u548c Oracle XA \u63a5\u53e3<\/li>\n<li>Oracle \u5907\u4efd\u4e0e\u6062\u590d\u64cd\u4f5c\u548cI\/O\u670d\u52a1\u5668\u8fdb\u7a0b\u7528\u7684\u5185\u5b58(\u7f13\u51b2)<\/li>\n<li>\u5e76\u884c\u6267\u884c\u6d88\u606f\u7f13\u51b2<\/li>\n<\/ul>\n<p>\u5927\u6c60\u6ca1\u6709LRU\u5217\u8868\u3002\u8fd9\u548c\u5171\u4eab\u6c60\u4e2d\u7684\u4fdd\u7559\u7a7a\u95f4\u4e0d\u540c\uff0c\u4fdd\u7559\u7a7a\u95f4\u548c\u5171\u4eab\u6c60\u4e2d\u5176\u4ed6\u5206\u914d\u7684\u5185\u5b58\u4f7f\u7528\u540c\u6837\u7684LRU\u5217\u8868\u3002\u5927\u5757\u5185\u5b58\u4ece\u4e0d\u4f1a\u6362\u51fa\u5927\u6c60\u4e2d\uff0c\u5185\u5b58\u5fc5\u987b\u662f\u663e\u5f0f\u7684\u88ab\u6bcf\u4e2a\u4f1a\u8bdd\u5206\u914d\u5e76\u91ca\u653e\u3002\u4e00\u4e2a\u8bf7\u6c42\u5982\u679c\u6ca1\u6709\u8db3\u591f\u7684\u5185\u5b58\uff0c\u5c31\u4f1a\u4ea7\u751f\u7c7b\u4f3c\u8fd9\u6837\u7684\u4e00\u4e2aORA-4031\u9519\u8bef\uff1a<\/p>\n<blockquote>\n<pre>ORA-04031: unable to allocate XXXX bytes of shared memory \r\n(\"large pool\",\"unknown object\",\"session heap\",\"frame\") \r\n<\/pre>\n<\/blockquote>\n<p>\u8fd9\u4e2a\u9519\u8bef\u53d1\u751f\u65f6\u5019\u53ef\u4ee5\u68c0\u67e5\u51e0\u4ef6\u4e8b\u60c5\uff1a<\/p>\n<ul>\n<li><strong>1- \u4f7f\u7528\u5982\u4e0b\u8bed\u53e5\u68c0\u67e5 V$SGASTAT \uff0c\u5f97\u77e5\u4f7f\u7528\u548c\u7a7a\u95f2\u7684\u5185\u5b58\uff1a<\/strong>\n<pre>SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool'; \r\n<\/pre>\n<\/li>\n<li><strong>2- \u4f60\u8fd8\u53ef\u4ee5\u91c7\u7528 heapdump level 32 \u6765 dump \u5927\u6c60\u7684\u5806\u5e76\u68c0\u67e5\u7a7a\u95f2\u7684\u5927\u5757\u5185\u5b58\u7684\u5927\u5c0f<\/strong>\u4ece\u5927\u6c60\u5206\u914d\u7684\u5185\u5b58\u5982\u679c\u662fLARGE_POOL_MIN_ALLOC \u5b50\u8282\u7684\u6574\u5757\u6570\u6709\u52a9\u4e8e\u907f\u514d\u788e\u7247\u3002\u4efb\u4f55\u8bf7\u6c42\u5206\u914d\u5c0f\u4e8eLARGE_POOL_MIN_ALLOC \u5927\u5757\u5c3a\u5bf8\u90fd\u5c06\u5206\u914dLARGE_POOL_MIN_ALLOC\u7684\u5927\u5c0f\u3002\u4e00\u822c\u6765\u8bf4\uff0c\u4f60\u4f1a\u770b\u5230\u4f7f\u7528\u5927\u6c60\u7684\u65f6\u5019\u76f8\u5bf9\u5171\u4eab\u6c60\u6765\u8bf4\u8981\u7528\u5230\u66f4\u591a\u7684\u5185\u5b58\u3002\u901a\u5e38\u8981\u89e3\u51b3\u5927\u6c60\u4e2d\u7684ORA-4031\u9519\u8bef\u5fc5\u987b\u589e\u52a0 LARGE_POOL_SIZE \u7684\u5927\u5c0f\u3002<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2><a name=\"t5\"><\/a>5. ORA-04031 \u548c\u5171\u4eab\u6c60\u5237\u65b0<\/h2>\n<p>\u6709\u4e00\u4e9b\u6280\u5de7\u4f1a\u63d0\u9ad8\u6e38\u6807\u7684\u5171\u4eab\u80fd\u529b\uff0c\u4ece\u800c\u5171\u4eab\u6c60\u788e\u7247\u548cORA-4031\u90fd\u4f1a\u51cf\u5c11\u3002<strong>\u6700\u4f73\u9014\u5f84\u662f\u8c03\u6574\u5e94\u7528\u4f7f\u7528\u7ed1\u5b9a\u53d8\u91cf<\/strong>\u3002\u53e6\u5916\u5728\u5e94\u7528\u4e0d\u80fd\u8c03\u6574\u7684\u65f6\u5019\u8003\u8651\u4f7f\u7528CURSOR_SHARING\u53c2\u6570\u548cFORCE\u4e0d\u540c\u7684\u503c\u6765\u505a\u5230 (\u8981\u6ce8\u610f\u90a3\u4f1a\u5bfc\u81f4\u6267\u884c\u8ba1\u5212\u6539\u53d8\uff0c\u6240\u4ee5\u5efa\u8bae\u5148\u5bf9\u5e94\u7528\u8fdb\u884c\u6d4b\u8bd5)\u3002\u5f53\u4e0a\u8ff0\u6280\u5de7\u90fd\u4e0d\u53ef\u4ee5\u7528\u7684\u65f6\u5019\uff0c\u5e76\u4e14\u788e\u7247\u95ee\u9898\u5728\u7cfb\u7edf\u4e2d\u6bd4\u8f83\u4e25\u91cd\uff0c\u5237\u65b0\u5171\u4eab\u6301\u53ef\u80fd\u6709\u52a9\u4e8e\u51cf\u8f7b\u788e\u7247\u95ee\u9898\u3002\u4f46\u662f\uff0c\u5fc5\u987b\u52a0\u4ee5\u5982\u4e0b\u8003\u8651\uff1a<\/p>\n<ul>\n<li>\u5237\u65b0\u5c06\u5bfc\u81f4\u6240\u6709\u6ca1\u88ab\u4f7f\u7528\u7684\u6e38\u6807\u4ece\u5171\u4eab\u6c60\u5220\u9664\u3002\u8fd9\u6837\uff0c\u5728\u5171\u4eab\u6c60\u5237\u65b0\u4e4b\u540e\uff0c\u5927\u591a\u6570SQL\u548cPL\/SQL\u6e38\u6807\u5fc5\u987b\u88ab\u786c\u89e3\u6790\u3002\u8fd9\u5c06\u63d0\u9ad8CPU\u7684\u4f7f\u7528\uff0c\u4e5f\u4f1a\u52a0\u5927Latch\u7684\u6d3b\u52a8\u3002<\/li>\n<li>\u5f53\u5e94\u7528\u7a0b\u5e8f\u6ca1\u6709\u4f7f\u7528\u7ed1\u5b9a\u53d8\u91cf\u5e76\u88ab\u8bb8\u591a\u7528\u6237\u8fdb\u884c\u7c7b\u4f3c\u7684\u64cd\u4f5c\u7684\u65f6\u5019(\u5982\u5728OLTP\u7cfb\u7edf\u4e2d) \uff0c\u5237\u65b0\u4e4b\u540e\u5f88\u5feb\u8fd8\u4f1a\u51fa\u73b0\u788e\u7247\u95ee\u9898\u3002\u6240\u4ee5\u5171\u4eab\u6c60\u5bf9\u8bbe\u8ba1\u7cdf\u7cd5\u7684\u5e94\u7528\u7a0b\u5e8f\u6765\u8bf4\u4e0d\u662f\u89e3\u51b3\u529e\u6cd5\u3002<\/li>\n<li>\u5bf9\u4e00\u4e2a\u5927\u7684\u5171\u4eab\u6c60\u5237\u65b0\u53ef\u80fd\u4f1a\u5bfc\u81f4\u7cfb\u7edf\u6302\u8d77\uff0c\u5c24\u5176\u662f\u5b9e\u4f8b\u7e41\u5fd9\u7684\u65f6\u5019\uff0c\u63a8\u8350\u5728\u975e\u9ad8\u5cf0\u7684\u65f6\u5019\u5237\u65b0<\/li>\n<\/ul>\n<h2><a name=\"t6\"><\/a>6. ORA-04031\u9519\u8bef\u7684\u9ad8\u7ea7\u5206\u6790<\/h2>\n<p>\u5982\u679c\u524d\u8ff0\u7684\u8fd9\u4e9b\u6280\u672f\u5185\u5bb9\u90fd\u4e0d\u80fd\u89e3\u51b3ORA-04031 \u9519\u8bef\uff0c\u53ef\u80fd\u9700\u8981\u989d\u5916\u7684\u8ddf\u8e2a\u4fe1\u606f\u6765\u5f97\u5230\u95ee\u9898\u53d1\u751f\u7684\u5171\u4eab\u6c60\u7684\u5feb\u7167\u3002<\/p>\n<p>\u8c03\u6574init.ora\u53c2\u6570\u6dfb\u52a0\u5982\u4e0b\u7684\u4e8b\u4ef6\u5f97\u5230\u8be5\u95ee\u9898\u7684\u8ddf\u8e2a\u4fe1\u606f\uff1a<\/p>\n<pre>event = \"4031 trace name errorstack level 3\" \r\nevent = \"4031 trace name HEAPDUMP level 3\" \r\n<\/pre>\n<p>\u5982\u679c\u95ee\u9898\u53ef\u91cd\u73b0\uff0c\u8be5\u4e8b\u4ef6\u53ef\u8bbe\u5b9a\u5728\u4f1a\u8bdd\u5c42\uff0c\u5728\u6267\u884c\u95ee\u9898\u8bed\u53e5\u4e4b\u524d\u4f7f\u7528\u5982\u4e0b\u7684\u8bed\u53e5\uff1a<\/p>\n<pre>SQL&gt; alter session set events '4031 trace name errorstack level 3'; \r\nSQL&gt; alter session set events '4031 trace name HEAPDUMP level 3'; \r\n<\/pre>\n<p>\u628a\u8fd9\u4e2a\u8ddf\u8e2a\u6587\u4ef6\u53d1\u7ed9Oracle\u652f\u6301\u4eba\u5458\u8fdb\u884c\u6392\u9519\u3002<\/p>\n<p>\u91cd\u8981\u6807\u6ce8: Oracle 9.2.0.5 \u548cOracle 10g \u7248\u672c\u4e2d\uff0c\u6bcf\u6b21\u5728\u53d1\u751fORA-4031 \u9519\u8bef\u7684\u65f6\u5019\u4f1a\u81ea\u52a8\u521b\u5efa\u4e00\u4e2a\u8ddf\u8e2a\u6587\u4ef6\uff0c\u53ef\u4ee5\u5728user_dump_dest \u76ee\u5f55\u4e2d\u627e\u5230\u3002\u5982\u679c\u4f60\u7684\u7cfb\u7edf\u662f\u4e0a\u8ff0\u7684\u7248\u672c\uff0c\u4f60\u4e0d\u9700\u8981\u518d\u8fdb\u884c\u524d\u9762\u63cf\u8ff0\u4e2d\u7684\u6b65\u9aa4\u3002<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>====================<\/p>\n<p>\u53e6\u5916\u7b80\u5355\u7684\u529e\u6cd5\u662f\u76f4\u63a5\u4fee\u6539stream pool<\/p>\n<p>alter system flush shared_pool;<\/p>\n<p>\u6e05\u7a7aShared pool:<\/p>\n<p>alter system flush shared_pool;<\/p>\n<p>\u4f46\u4e3a\u4e86\u6574\u4e2a\u5185\u5b58\u7684\u6e05\u7a7a\uff0c\u53ea\u597d\u5c06\u6574\u4e2aOracle RAC\u73af\u5883\u7684\u5b9e\u4f8b\u548c\u6570\u636e\u5e93\u90fd\u5173\u673a\uff0c\u518d\u91cd\u65b0\u542f\u52a8\u3002<\/p>\n<p>\u53ef\u4ee5\u91c7\u7528\u6e05\u7a7abuffer cache\u7684\u65b9\u5f0f\u6765\u505a\uff1a<\/p>\n<p>alter system flush buffer_cache;<\/p>\n<p>alter system set streams_pool_size=48m scope=both<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u8f6c\u81ea\uff1ahttps:\/\/blog.csdn.net\/tom0421\/article\/details\/693985<\/p>\n<div class=\"more-link\">\n\t\t\t\t <a href=\"http:\/\/www.inhhh.com\/blog\/?p=266\" class=\"link-btn theme-btn\"><span>Read More <\/span> <i class=\"fa fa-caret-right\"><\/i><\/a>\n\t\t\t<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[50,49],"class_list":["post-266","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-0ra-3041","tag-oracle"],"_links":{"self":[{"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/266","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=266"}],"version-history":[{"count":4,"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/266\/revisions"}],"predecessor-version":[{"id":595,"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/266\/revisions\/595"}],"wp:attachment":[{"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=266"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.inhhh.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}