ITPub博客

再次遇到ORA-04030的错误

原创 作者:尛样儿 时间:2015-08-12 12:04:11 1 删除 编辑
<span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp; 客户的一套Oracle 11.2.0.3 for AIX 6.1的RAC环境每天一直有ORA-04030的报错,能够确定的是<span style="color:#E53333;">物理内存充足,操作系统oracle用户ulimit没有任何限制,PGA的大小跟此报错没有关系(增大PGA或者缩小PGA的值</span><span style="color:#E53333;">都会报这个错)</span>。<br /> <br /> <strong><span style="font-size:18px;">一.告警日志文件。</span></strong><span style="font-size:18px;"></span><br /> 数据库ALERT日志报错如下:</span><br /> <span style="font-size:14px;font-family:'Courier New';">......</span><br /> <span style="font-size:14px;font-family:'Courier New';">2015-08-10 22:29:16.438000 +08:00</span><br /> <span style="font-size:14px;font-family:'Courier New';">Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_133659/ynsyn13_ora_14352442_i133659.trc:</span><br /> <span style="font-size:14px;font-family:'Courier New';">ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)</span><br /> <span style="font-size:14px;font-family:'Courier New';">ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)</span><br /> <span style="font-size:14px;font-family:'Courier New';">Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/trace/ynsyn13_ora_14352442.trc &nbsp;(incident=133661):</span><br /> <span style="font-size:14px;font-family:'Courier New';">ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)</span><br /> <span style="font-size:14px;font-family:'Courier New';">ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)</span><br /> <span style="font-size:14px;font-family:'Courier New';">Incident details in: /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_133661/ynsyn13_ora_14352442_i133661.trc</span><br /> <span style="font-size:14px;font-family:'Courier New';">Use ADRCI or Support Workbench to package the incident.</span><br /> <span style="font-size:14px;font-family:'Courier New';">See Note 411.1 at My Oracle Support for error and packaging details.</span><br /> <span style="font-size:14px;font-family:'Courier New';">Archived Log entry 139978 added for thread 1 sequence 161599 ID 0x515a23a dest 1:</span><br /> <span style="font-size:14px;font-family:'Courier New';">Dumping diagnostic data in directory=[cdmp_20150810222917], requested by (instance=1, osid=14352442), summary=[incident=133660].</span><br /> <span style="font-size:14px;font-family:'Courier New';">2015-08-10 22:29:18.455000 +08:00</span><br /> <span style="font-size:14px;font-family:'Courier New';">Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_133659/ynsyn13_ora_14352442_i133659.trc:</span><br /> <span style="font-size:14px;font-family:'Courier New';">ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)</span><br /> <span style="font-size:14px;font-family:'Courier New';">ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)</span><br /> <span style="font-size:14px;font-family:'Courier New';">......</span><br /> <br /> <strong><span style="font-size:18px;font-family:'Courier New';">二.trace文件。</span></strong><br /> <span style="font-size:16px;font-family:'Courier New';">trace文件的内容如下:</span><br /> <span style="font-size:14px;font-family:'Courier New';">Dump file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_145646/ynsyn13_ora_11075592_i145646.trc</span><br /> <span style="font-size:14px;font-family:'Courier New';">Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production</span><br /> <span style="font-size:14px;font-family:'Courier New';">With the Partitioning, Automatic Storage Management, OLAP, Data Mining</span><br /> <span style="font-size:14px;font-family:'Courier New';">and Real Application Testing options</span><br /> <span style="font-size:14px;font-family:'Courier New';">ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1</span><br /> <span style="font-size:14px;font-family:'Courier New';">System name: AIX</span><br /> <span style="font-size:14px;font-family:'Courier New';">Node name: lpar2</span><br /> <span style="font-size:14px;font-family:'Courier New';">Release: 1</span><br /> <span style="font-size:14px;font-family:'Courier New';">Version: 6</span><br /> <span style="font-size:14px;font-family:'Courier New';">Machine: 00F94D394C00</span><br /> <span style="font-size:14px;font-family:'Courier New';">Instance name: ynsyn13</span><br /> <span style="font-size:14px;font-family:'Courier New';">Redo thread mounted by this instance: 1</span><br /> <span style="font-size:14px;font-family:'Courier New';">Oracle process number: 38</span><br /> <span style="font-size:14px;font-family:'Courier New';">Unix process pid: 11075592, image: oracle@lpar2</span><br /> <br /> <br /> <span style="font-size:14px;font-family:'Courier New';">*** 2015-08-11 02:37:33.105</span><br /> <span style="font-size:14px;font-family:'Courier New';">*** SESSION ID:(434.341) 2015-08-11 02:37:33.105</span><br /> <span style="font-size:14px;font-family:'Courier New';">*** CLIENT ID:() 2015-08-11 02:37:33.105</span><br /> <span style="font-size:14px;font-family:'Courier New';">*** SERVICE NAME:(ynsyn13) 2015-08-11 02:37:33.105</span><br /> <span style="font-size:14px;font-family:'Courier New';">*** MODULE NAME:(SQL*Plus) 2015-08-11 02:37:33.105</span><br /> <span style="font-size:14px;font-family:'Courier New';">*** ACTION NAME:() 2015-08-11 02:37:33.105</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp;</span><br /> <span style="font-size:14px;font-family:'Courier New';">Dump continued from file: /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/trace/ynsyn13_ora_11075592.trc</span><br /> <span style="font-size:14px;font-family:'Courier New';">ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)</span><br /> <br /> <br /> <span style="font-size:14px;font-family:'Courier New';">========= Dump for incident 145646 (ORA 4030) ========</span><br /> <span style="font-size:14px;font-family:'Courier New';">----- Beginning of Customized Incident Dump(s) -----</span><br /> <span style="font-size:14px;font-family:'Courier New';">=======================================</span><br /> <span style="font-size:14px;font-family:'Courier New';">TOP 10 MEMORY USES FOR THIS PROCESS</span><br /> <span style="font-size:14px;font-family:'Courier New';">---------------------------------------</span><br /> <span style="font-size:14px;font-family:'Courier New';">39% &nbsp; 43 MB, 907 chunks: "kllcqas:kllsltba &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;" &nbsp;SQL</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QERHJ hash-joi &nbsp;ds=1110841a0 &nbsp;dsprt=110bb1268</span><br /> <span style="font-size:14px;font-family:'Courier New';">29% &nbsp; 33 MB, 562 chunks: "permanent memory &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;" &nbsp;SQL</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sort subheap &nbsp; &nbsp;ds=111083eb8 &nbsp;dsprt=110bb1268</span><br /> <span style="font-size:14px;font-family:'Courier New';">12% &nbsp; 13 MB, 907 chunks: "free memory &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; " &nbsp;SQL</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QERHJ hash-joi &nbsp;ds=1110841a0 &nbsp;dsprt=110bb1268</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp;7% 8160 KB, &nbsp; 9 chunks: "QERHJ list array &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;" &nbsp;SQL</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QERHJ hash-joi &nbsp;ds=1110841a0 &nbsp;dsprt=110bb1268</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp;4% 4096 KB, &nbsp; 1 chunk : "HT buckets &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;" &nbsp;SQL</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QERHJ hash-joi &nbsp;ds=1110841a0 &nbsp;dsprt=110bb1268</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp;3% 2931 KB, &nbsp;29 chunks: "kllcqc:kllcqslt &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; " &nbsp;SQL</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QERHJ hash-joi &nbsp;ds=1110841a0 &nbsp;dsprt=110bb1268</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp;2% 2048 KB, &nbsp;16 chunks: "QERHJ Bit vector &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;" &nbsp;SQL</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QERHJ hash-joi &nbsp;ds=1110841a0 &nbsp;dsprt=110bb1268</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp;1% 1184 KB, 213 chunks: "free memory &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; " &nbsp;</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pga heap &nbsp; &nbsp; &nbsp; &nbsp;ds=110004c20 &nbsp;dsprt=0</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp;1% &nbsp;836 KB, &nbsp;25 chunks: "permanent memory &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;" &nbsp;</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pga heap &nbsp; &nbsp; &nbsp; &nbsp;ds=110004c20 &nbsp;dsprt=0</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp;1% &nbsp;660 KB, 1442 chunks: "free memory &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; " &nbsp;</span><br /> <span style="font-size:14px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;session heap &nbsp; &nbsp;ds=110954758 &nbsp;dsprt=110101b80</span><br /> <span style="font-size:14px;font-family:'Courier New';"><span style="font-size:16px;color:#E53333;"><br /> &lt;&lt;&lt;&lt;这个地方显示的是该进程(服务器进程)使用的PGA内存情况,可以看出该进程的PGA内存分配已经接近100MB。</span><br /> ......</span><br /> <br /> <span style="font-size:18px;font-family:'Courier New';"><strong>三.数据库参数配置。</strong></span><br /> <span style="font-size:18px;font-family:'Courier New';"><strong></strong></span><br /> <span style="font-size:16px;font-family:'Courier New';">数据库实例的配置参数如下:</span><br /> <span style="font-size:16px;font-family:'Courier New';">SELECT a.ksppinm Param,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b.ksppstvl SessionVal,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c.ksppstvl InstanceVal,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a.ksppdesc Descr</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; FROM x$ksppi a, x$ksppcv b, x$ksppsv c</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp;WHERE &nbsp; &nbsp; a.indx = b.indx</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND a.indx = c.indx</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND a.ksppinm LIKE '/_%' ESCAPE '/' and a.ksppinm='_smm_max_size'</span><br /> <span style="font-size:16px;font-family:'Courier New';">ORDER BY 1</span><br /> <span style="font-size:16px;font-family:'Courier New';">/</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';">PARAM</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">SESSIONVAL</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">INSTANCEVAL</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">DESCR</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">_smm_max_size</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">51200</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">51200</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">maximum work area size in auto mode (serial)</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">&lt;&lt;&lt;&lt;自动模式下,单个进程最大的work area大小。</span><span style="color:#E53333;"></span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; SELECT a.ksppinm Param,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b.ksppstvl SessionVal,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; c.ksppstvl InstanceVal,</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a.ksppdesc Descr</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 5 &nbsp; &nbsp; &nbsp;FROM x$ksppi a, x$ksppcv b, x$ksppsv c</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 6 &nbsp; &nbsp; WHERE &nbsp; &nbsp; a.indx = b.indx</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND a.indx = c.indx</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND a.ksppinm LIKE '/_%' ESCAPE '/' and a.ksppinm='_pga_max_size'</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp; 9 &nbsp;ORDER BY 1</span><br /> <span style="font-size:16px;font-family:'Courier New';">&nbsp;10 &nbsp;/</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">PARAM</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">SESSIONVAL</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">INSTANCEVAL</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';">DESCR</span><br /> <span style="font-size:16px;font-family:'Courier New';">--------------------------------------------------------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">_pga_max_size</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">104857600</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">104857600</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">Maximum size of the PGA memory for one process</span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">&lt;&lt;&lt;&lt; 一个进程最大的PGA内存为100MB。</span><br /> <br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; show parameter pga</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TYPE &nbsp; &nbsp; &nbsp; &nbsp;VALUE</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------------ ----------- ------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">_pga_max_size &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;big integer 100M &nbsp; &nbsp;&lt;&lt;&lt;&lt; 初始化参数中限制了_pga_max_size大小。</span><br /> <span style="font-size:16px;font-family:'Courier New';">pga_aggregate_target &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; big integer 2G</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt;&nbsp;</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; show parameter workarea</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TYPE &nbsp; &nbsp; &nbsp; &nbsp;VALUE</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------------ ----------- ------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">workarea_size_policy &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string &nbsp; &nbsp; &nbsp;AUTO</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt;&nbsp;</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt; show parameter target</span><br /> <br /> <br /> <span style="font-size:16px;font-family:'Courier New';">NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TYPE &nbsp; &nbsp; &nbsp; &nbsp;VALUE</span><br /> <span style="font-size:16px;font-family:'Courier New';">------------------------------------ ----------- ------------------------------</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">memory_max_target &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;big integer 0</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">memory_target &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;big integer 0</span><br /> <span style="font-size:16px;font-family:'Courier New';color:#E53333;">pga_aggregate_target &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; big integer 2G</span><br /> <span style="font-size:16px;font-family:'Courier New';">sga_target &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; big integer 15G</span><br /> <span style="font-size:16px;font-family:'Courier New';">SQL&gt;&nbsp;<br /> </span><span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:18px;font-family:'Courier New';"><strong>四.pga_aggregate_target和</strong></span><span style="font-size:18px;font-family:'Courier New';"><strong>WORKAREA_SIZE_POLICY参数的含义。</strong></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span> <h1 class="sect1" id="insertedID0" style="font-size:21px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:#DDDDDD;width:1147px;font-family:Tahoma, sans-serif;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <span style="font-size:18px;font-family:'Courier New';">&gt;PGA_AGGREGATE_TARGET</span> </h1> <div class="inftblinformal" style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <table class="Informal" title="PGA_AGGREGATE_TARGET initialization parameter" summary="This table is described in the preceding text" dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0" style="font-size:small;"> <colgroup><col width="25%" /><col width="*" /></colgroup> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t364" style="vertical-align:bottom;"> <span style="font-size:16px;font-family:'Courier New';">Property</span> </th> <th align="left" valign="bottom" id="r1c2-t364" style="vertical-align:bottom;"> <span style="font-size:16px;font-family:'Courier New';">Description</span> </th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t364" headers="r1c1-t364" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Parameter type</span> </td> <td align="left" headers="r2c1-t364 r1c2-t364" style="vertical-align:top;"> <span style="font-size:16px;font-family:'Courier New';">Big integer</span> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t364" headers="r1c1-t364" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Syntax</span> </td> <td align="left" headers="r3c1-t364 r1c2-t364" style="vertical-align:top;"> <code><span style="font-size:16px;font-family:'Courier New';">PGA_AGGREGATE_TARGET =</span></code><span style="font-size:16px;font-family:'Courier New';">&nbsp;</span><code><span class="codeinlineitalic" style="font-style:italic;font-size:16px;font-family:'Courier New';">integer</span></code><span style="font-size:16px;font-family:'Courier New';">&nbsp;</span><code><span style="font-size:16px;font-family:'Courier New';">[K | M | G]</span></code> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t364" headers="r1c1-t364" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Default value</span> </td> <td align="left" headers="r4c1-t364 r1c2-t364" style="vertical-align:top;"> <span style="font-size:16px;font-family:'Courier New';">10 MB or 20% of the size of the SGA, whichever is greater</span> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t364" headers="r1c1-t364" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Modifiable</span> </td> <td align="left" headers="r5c1-t364 r1c2-t364" style="vertical-align:top;"> <code><span style="font-size:16px;font-family:'Courier New';">ALTER SYSTEM</span></code> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r6c1-t364" headers="r1c1-t364" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Range of values</span> </td> <td align="left" headers="r6c1-t364 r1c2-t364" style="vertical-align:top;"> <span style="font-size:16px;font-family:'Courier New';">Minimum: 10 MB</span> <p style="margin-top:0px;margin-bottom:0px;"> <span style="font-size:16px;font-family:'Courier New';">Maximum: 4096 GB - 1</span> </p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r7c1-t364" headers="r1c1-t364" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Basic</span> </td> <td align="left" headers="r7c1-t364 r1c2-t364" style="vertical-align:top;"> <span style="font-size:16px;font-family:'Courier New';">Yes</span> </td> </tr> </tbody> </table> <br /> </div> <p style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">PGA_AGGREGATE_TARGET</span></code><span style="font-size:16px;font-family:'Courier New';">&nbsp;specifies the target aggregate PGA memory available to all server processes attached to the instance.</span> </p> <p style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>Setting&nbsp;</strong></span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>PGA_AGGREGATE_TARGET</strong></span></code><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>&nbsp;to a nonzero value has the effect of automatically setting the&nbsp;</strong></span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>WORKAREA_SIZE_POLICY</strong></span></code><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>&nbsp;parameter to&nbsp;</strong></span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>AUTO</strong></span></code><span style="font-size:16px;font-family:'Courier New';"><strong>.</strong> This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.</span> </p> <p style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>Setting&nbsp;</strong></span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>PGA_AGGREGATE_TARGET</strong></span></code><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>&nbsp;to 0 automatically sets the&nbsp;</strong></span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>WORKAREA_SIZE_POLICY</strong></span></code><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>&nbsp;parameter to&nbsp;</strong></span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>MANUAL</strong></span></code><span style="font-size:16px;font-family:'Courier New';">. This means that SQL workareas are sized using the&nbsp;</span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">*_AREA_SIZE</span></code><span style="font-size:16px;font-family:'Courier New';">&nbsp;parameters.</span> </p> <p style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <span style="font-size:16px;font-family:'Courier New';">Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.</span> </p> <p style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <span style="font-size:16px;font-family:'Courier New';">When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to</span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">PGA_AGGREGATE_TARGET</span></code><span style="font-size:16px;font-family:'Courier New';">.</span> </p> <h1 class="sect1" id="insertedID0" style="font-size:21px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:#DDDDDD;width:1164px;font-family:Tahoma, sans-serif;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <span style="font-size:18px;font-family:'Courier New';"><br /> </span> </h1> <h1 class="sect1" id="insertedID0" style="font-size:21px;border-bottom-width:1px;border-bottom-style:solid;border-bottom-color:#DDDDDD;width:1164px;font-family:Tahoma, sans-serif;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <span style="font-size:18px;font-family:'Courier New';">&gt;WORKAREA_SIZE_POLICY</span> </h1> <div class="inftblinformal" style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <table class="Informal" title="WORKAREA_SIZE_POLICY initialization parameter" summary="This table is described in the preceding text" dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0" style="font-size:small;"> <colgroup><col width="25%" /><col width="*" /></colgroup> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t510" style="vertical-align:bottom;"> <span style="font-size:16px;font-family:'Courier New';">Property</span> </th> <th align="left" valign="bottom" id="r1c2-t510" style="vertical-align:bottom;"> <span style="font-size:16px;font-family:'Courier New';">Description</span> </th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t510" headers="r1c1-t510" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Parameter type</span> </td> <td align="left" headers="r2c1-t510 r1c2-t510" style="vertical-align:top;"> <span style="font-size:16px;font-family:'Courier New';">String</span> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t510" headers="r1c1-t510" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Syntax</span> </td> <td align="left" headers="r3c1-t510 r1c2-t510" style="vertical-align:top;"> <code><span style="font-size:16px;font-family:'Courier New';">WORKAREA_SIZE_POLICY = { AUTO | MANUAL }</span></code> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t510" headers="r1c1-t510" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Default value</span> </td> <td align="left" headers="r4c1-t510 r1c2-t510" style="vertical-align:top;"> <code><span style="font-size:16px;font-family:'Courier New';">AUTO</span></code> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t510" headers="r1c1-t510" style="vertical-align:top;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Modifiable</span> </td> <td align="left" headers="r5c1-t510 r1c2-t510" style="vertical-align:top;"> <code><span style="font-size:16px;font-family:'Courier New';">ALTER SESSION</span></code><span style="font-size:16px;font-family:'Courier New';">,&nbsp;</span><code><span style="font-size:16px;font-family:'Courier New';">ALTER SYSTEM</span></code> </td> </tr> </tbody> </table> <br /> </div> <p style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">WORKAREA_SIZE_POLICY</span></code><span style="font-size:16px;font-family:'Courier New';">&nbsp;specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.</span> </p> <p style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <span class="bold" style="font-size:16px;font-family:'Courier New';">Values:</span> </p> <ul style="font-family:Tahoma, sans-serif;font-size:small;line-height:normal;white-space:normal;widows:auto;background-color:#FFFFFF;"> <li> <p> <code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">AUTO</span></code> </p> <p> <span style="font-size:16px;font-family:'Courier New';">Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used by the system, the target PGA memory set in&nbsp;</span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">PGA_AGGREGATE_TARGET</span></code><span style="font-size:16px;font-family:'Courier New';">, and the requirement of each individual operator.</span> </p> </li> <li> <p> <code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">MANUAL</span></code> </p> <p> <span style="font-size:16px;font-family:'Courier New';">The sizing of work areas is manual and based on the values of the *</span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">_AREA_SIZE</span></code><span style="font-size:16px;font-family:'Courier New';">&nbsp;parameter corresponding to the operation (for example, a sort uses&nbsp;</span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">SORT_AREA_SIZE</span></code><span style="font-size:16px;font-family:'Courier New';">). Specifying&nbsp;</span><code style="font-size:12px;"><span style="font-size:16px;font-family:'Courier New';">MANUAL</span></code><span style="font-size:16px;font-family:'Courier New';">&nbsp;may result in sub-optimal performance and poor PGA memory utilization.</span> </p> </li> </ul> <span style="font-size:16px;font-family:'Courier New';"></span><span style="font-size:16px;font-family:'Courier New';color:#E53333;"><strong>实践证明,可以将PGA_AGGREGATE_TARGET设置为非0值,而将WORKAREA_SIZE_POLICY设置为MANUAL;</strong></span><br /> <br /> <span style="font-size:18px;font-family:'Courier New';"><strong>五.</strong></span><span style="font-size:18px;font-family:'Courier New';"><strong>How To Super-Size Work Area Memory Size Used By Sessions? (Doc ID 453540.1)</strong></span><span style="font-size:18px;font-family:'Courier New';"><strong></strong></span><br /> <p> <b><span style="font-size:16px;">In this Document</span></b> </p> <table> <tbody> <tr> <td width="0"> </td> <td> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=312447618955362&amp;id=453540.1&amp;_adf.ctrl-state=9ybipf1yn_75#GOAL"><span style="font-size:16px;">Goal</span></a> </td> </tr> </tbody> </table> <table> <tbody> <tr> <td width="0"> </td> <td> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=312447618955362&amp;id=453540.1&amp;_adf.ctrl-state=9ybipf1yn_75#FIX"><span style="font-size:16px;">Solution</span></a> </td> </tr> </tbody> </table> <table> <tbody> <tr> <td width="0"> </td> <td> <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=312447618955362&amp;id=453540.1&amp;_adf.ctrl-state=9ybipf1yn_75#REF"><span style="font-size:16px;">References</span></a> </td> </tr> </tbody> </table> <hr size="1" /> <p> <br /> </p> <h2 class="km"> <span style="font-size:16px;">Applies to:</span> </h2> <span style="font-size:16px;">Oracle Database - Enterprise Edition - Version 10.2.0.1 and later</span><br /> <span style="font-size:16px;">Information in this document applies to any platform.</span><br /> <span style="font-size:16px;"> ***Checked for relevance on 08-Jul-2015***</span><br /> <h2 class="km"> <span style="font-size:16px;">Goal</span> </h2> <p> <span style="font-size:16px;">How to super-size the work area memory size that can be used by any session in the database?</span> </p> <h2 class="km"> <span style="font-size:16px;">Solution</span> </h2> <p> <span style="font-size:16px;">With the automatic PGA management mode, you can control the size for the work area sizes, to be able perform big sort operations or hash joins in memory and avoid messages like:</span> </p> <div class="kmnotebox"> <span style="font-size:16px;">kxhfWrite: hash-join is spilling to disk</span> </div> <p> <br /> <span style="font-size:16px;">You have to set the following parameters to proper values:</span><br /> <br /> <span style="font-size:16px;">1. PGA_AGGREGATE_TARGET</span> </p> <ul> <li> <span style="font-size:16px;">should be set to five times the desired work area size</span> </li> </ul> <p> <br /> <span style="font-size:16px;">2. _PGA_MAX_SIZE</span> </p> <ul> <li> <span style="font-size:16px;">should be set in minimum of twice the desired work area size. The default value is 200Mb.</span> </li> </ul> <p> <br /> <span style="font-size:16px;">3. _SMM_MAX_SIZE</span> </p> <ul> <li> <span style="font-size:16px;">normally this parameter is not needed but maybe under certain circumstances</span> </li> <li> <span style="font-size:16px;">if set it should be equal to the desired work area size (in kb !)</span> </li> </ul> <p> <br /> <span style="font-size:16px;">Example:</span><br /> <span style="font-size:16px;">If you like to use a sort area size of 2GB for a special operation (e.g for the creating of an index on a large table) you could set the values as follows:</span> </p> <div class="kmcodeblock"> <span style="font-size:16px;">PGA_AGGREGATE TARGET = 10G</span><br /> <span style="font-size:16px;">"_PGA_MAX_SIZE" = 2G</span><br /> <span style="font-size:16px;">"_SMM_MAX_SIZE" = 2097152</span> </div> <p> <br /> <span style="font-size:16px;">Dynamically, the values must be changed for the full instance using the "alter system" command. It is not allowed changes the above parameters for one session.</span><br /> <br /> <span style="font-size:16px;">Please note that there is an internal limitation that a single workarea size is limited to 2GB.&nbsp; But you can manually set _pga_max_size above 2gb if you have the fix for bug 17951233.</span><br /> <br /> <em><span style="font-size:16px;">_PGA_MAX_SIZE is set up to 2GB (1GB limit for 32-bit) per bug </span></em><span style="font-size:16px;">3946308</span><br /> <br /> <span style="font-size:16px;">Also please be careful that the above changes are very dangerous because the values affect all sessions in the database. If you need to have granular control of the work area for a single session, then it is better to set at the session level WORKAREA_SIZE_POLICY=MANUAL and *_AREA_SIZE as high as needed.</span> </p> <div class="kmnotebox"> <strong><span style="font-size:16px;">NOTE:</span></strong><br /> <span style="font-size:16px;">PGA_AGGREGATE_TARGET can only be used when using ASMM with Oracle 10g and higher. </span><br /> <span style="font-size:16px;">When using AMM with Oracle 11g and higher, the PGA size is also automatically managed.</span> </div> <span style="font-size:16px;"><br /> MOS文章写得很清楚,到此应该明白该问题的原因,以及处理该问题的方法了。<br /> </span><span style="font-size:16px;font-family:'Courier New';"><br /> &nbsp; &nbsp;之前的一个案例《</span><span style="font-size:16px;font-family:'Courier New';">WORKAREA_SIZE_POLICY参数引起的ORA-04030错误</span><span style="font-size:16px;font-family:'Courier New';">》</span><span style="font-size:16px;font-family:'Courier New';">:</span><a href="http://blog.itpub.net/23135684/viewspace-712772/" target="_blank"><span style="font-size:16px;font-family:'Courier New';">http://blog.itpub.net/23135684/viewspace-712772/</span></a><span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';"></span><br /> <span style="font-size:16px;font-family:'Courier New';">--end--</span> <span style="font-size:16px;"></span><br />
请登录后发表评论 登录
全部评论
Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

注册时间:2010-01-05

  • 博文量
    461
  • 访问量
    5178483