再次遇到ORA-04030的错误-阿里云开发者社区

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

再次遇到ORA-04030的错误

热门文章

最新文章

相关电子书