再次遇到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--

时间： 2024-10-12 11:57:15

再次遇到ORA-04030的错误

再次遇到ORA-04030的错误的相关文章

tomcat-关于hibernate的问题，一个项目刚导入时可以运行，但在果断时间后再次运行就报500错误

ORACLE的ORA－1693错误以及表和索引的表空间的移动

Yaf中Action再次Dispatch导致PHP段错误Segmentation fault

XMPPStream::connectToHost:onPort:withTimeout:error: ,每次进入后台几分钟后再次打开程序回报这个错误

plsql连接oracle数据库报ora 12154错误解决方法_oracle

Oracle的常见错误及解决办法

Oracle11.1.0.7版本的ASSM与ORA-04030错误

该内存不能为read或written错误的解决

XP系统出现“内存不能为read”错误

让Windows XP不再出现内存读写错误