oracle aix 11.2.0.3,Oracle 11.2.0.3 Database for AIX bug导致ORA-04030的报错

根据我以往的经验，在AIX平台Oracle Database数据库较其他平台更容易报ORA-04030的错误，PGA的参数有时候设置小了反而不报错误。帮助客户将一个Oracle 11.2.0.3 Restart Database数据库的服务器更换到一台新采购的服务器，新服务器分区的内存要比原有服务器的内存少差不多一半，进行多次Oracle数据库实例初始化内存参数调整之后，在执行同一条SQL语句时依然会报ORA-04030的错误：

2014-11-03 22:50:44.074000 +08:00

Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_48299/ynsyn13_ora_26149036_i48299.trc:

ORA-04030: 69040  (pga heap,kgh stack) ?                                         ORA-04030: 15224  (QERHJ hash-joi,kllcqas:kllsltba) ?                                                                                               Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/trace/ynsyn13_ora_26149036.trc  (incident=48301):

ORA-04030: 24504  (pga heap,kco buffer) ?                                          ORA-04030: 15224  (QERHJ hash-joi,kllcqas:kllsltba) ?                                                                                                Incident details in: /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_48301/ynsyn13_ora_26149036_i48301.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Dumping diagnostic data in directory=[cdmp_20141103225044], requested by (instance=1, osid=26149036), summary=[incident=48300].

Sweep [inc][48301]: completed

Sweep [inc][48300]: completed

于是在MOS上进行了一番搜索，找到如下一篇文章：

ORA-4030: Out Of Process Memory (QERHJ hash-joi,QERHJ list array) Raised When Using _PGA_MAX_SIZE (Doc ID 1471103.1)

In this Document

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later

Information in this document applies to any platform.

***Checked for currency 24-July-2014***

Symptoms

A session crashes with:

ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)

Review of the instance parameters set reveals:

"_pga_max_size"=614400 KB

Cause

The cause of this problem has been identified in:

- ORA-04030: OUT OF PROCESS MEMORY WHEN TRYING TO ALLOCATE 262168 BYTES (QERGH HAS

suspended as related to:

unpublished Bug:9506362 - ORA-04030: OUT OF PROCESS MEMORY (QERHJ HASH-JOI,KLLCQAS:KLLSLTBA)

Both bugs have been suspended due to lack of reproducibility.

Solution

As the bugs have been suspended due to lack of information to perform

analysis, the only possible workaround is to set the _PGA_MAX_SIZE

instance parameter to a smaller value.

This parameter determines

the maximum size which can be used for per-process PGA memory. The

default value is 200MB and the range of valid values is from 10MB up to

4TB-1.

The per-process PGA memory can be limited by setting the

_PGA_MAX_SIZE to a smaller value which internally forces the hash-join

to use the less memory and avoid the ORA-4030, like in:

SQL> alter system set "_pga_max_size"=100M;

根据文章的建议，调整了_pga_max_size隐藏参数后，执行相同的SQL语句不再报ORA-04030的错误。

另外，采用共享模式连接数据库也是避免出现ORA-04030的一种很好的方法。PGA是由堆栈信息和UGA组成；UGA包含了会话信息、游标信息、SQL工作区等内容，占据了PGA的大部分空间，采用共享模式连接到数据库，进程的UGA信息是放在SGA中的Shared Pool中，如果实例在启动的时候将SGA完全的固定到内存中(利用大页技术，Linux平台的HugePage，AIX平台的Large Page，设置数据库实例初始化参数pre_page_sga=TRUE,lock_sga=TRUE)，实例在启动后不会有swap in/out，SGA占用的实际内存也不会发生变化，那么就不会有ORA-04030的错误报出。

--end--