再次遇到ORA-04030的错误

最新推荐文章于 2024-04-03 16:30:18 发布

转载最新推荐文章于 2024-04-03 16:30:18 发布

· 464 阅读

文章标签：

客户的一套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参数的含义。

>PGA_AGGREGATE_TARGET

Property	Description
Parameter type	Big integer
Syntax	`PGA_AGGREGATE_TARGET =` `integer` `[K \| M \| G]`
Default value	10 MB or 20% of the size of the SGA, whichever is greater
Modifiable	`ALTER SYSTEM`
Range of values	Minimum: 10 MB Maximum: 4096 GB - 1
Basic	Yes

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. 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 PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE 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 toPGA_AGGREGATE_TARGET.

>WORKAREA_SIZE_POLICY

Property	Description
Parameter type	String
Syntax	`WORKAREA_SIZE_POLICY = { AUTO \| MANUAL }`
Default value	`AUTO`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM`

WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.

Values:

AUTO

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 PGA_AGGREGATE_TARGET, and the requirement of each individual operator.
MANUAL

The sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter corresponding to the operation (for example, a sort uses SORT_AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization.

实践证明，可以将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

Goal

Solution

References

Applies to:

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***

Goal

How to super-size the work area memory size that can be used by any session in the database?

Solution

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:

kxhfWrite: hash-join is spilling to disk

You have to set the following parameters to proper values:

1. PGA_AGGREGATE_TARGET

should be set to five times the desired work area size

2. _PGA_MAX_SIZE

should be set in minimum of twice the desired work area size. The default value is 200Mb.

3. _SMM_MAX_SIZE

normally this parameter is not needed but maybe under certain circumstances
if set it should be equal to the desired work area size (in kb !)

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:

PGA_AGGREGATE TARGET = 10G
"_PGA_MAX_SIZE" = 2G
"_SMM_MAX_SIZE" = 2097152

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.

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.