某地市公安局户籍系统数据库是Oracle RAC 11.2.0.4,运行在IBM P720上(操作系统AIX 6.1),64G内存4颗4核CPU,使用ASM。这套系统与人社局的社保保障卡系统进行数据交换。

这是数据库服务器的一个配置情况，出现的问题如下:

业务人员反馈说在执行某个特定模块时间隙性报错（也就是在查询条件所查询的数据量不同时可能会出现这种问题)ORA-04030: 在尝试分配 123416 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足，其它模块SQL执行正常。

当出现这种问题时，要开发人员在客户现场使用PL/SQL与SQL*Plus执行该报错SQL，仍然报错。看见到这种错误信息，第一时间可能想到的原因又是什么。

对于ORA-04030: 在尝试分配 123416 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足这种信息，我当时的第一反应是，是不是物理内存不足所引起的。

所以诊断也就是从检查系统内存开始。

通过检查系统物理内存与分页情况，系统资源是充足的。不仅物理内存有空闲，且根本就没有使用分页。

上图中是该数据库的内存设置情况,从输出信息可以看到没有使用memory_max_target，memory_target，设置了sga_target与sga_max_size为16G,并且指定了db_cache_size与shared_pool_size的最小值，pga_aggregate_target为8G,操作系统的物理内存总大小是64G,所以Oracle使用的内存不到物理内存的一半。所以不应该是因为内存资源不足所引起的。

因为pga_aggregate_target指定了所有会话相关的服务器进程所使用的PGA总大小，但每个会话相关的

服务器进程可以使用的pga最大内存是由隐含参数_pga_max_size所控制的，它的默认大小是 200MB,

它的取值范围从10MB up to 4TB-1。

于是检查这两个隐含参数：

这里_pga_max_size它的大小是1310M。workarea_size_policy 设置为 AUTO,并且在自动模式下PGA中的SQL工作区所能使用的内存大小由隐含参数_smm_max_size控制，这里它的大小为128M。

如是将_smm_max_size设置为500M，再执行该SQL语句。

但执行结果仍然报错。

如是尝试使用手动PGA管理，workarea_size_policy设置为manual,将hash_area_size,sort_area_size设置为500M,再执行该SQL语句。

还是一样报错。

如果设置4030 trace name heapdump level 536870917'事件来获取进程转储信息。

从生成的跟踪文件中可以看到如下信息：

如上信息可以看到这个会话进程所分配的私有SQL区域（Private SQL Area）：为111M。

从以上信息可以看到会话进程的内存分配top 10部分的详细信息，其中ERHJ hash-joi,kllcqas:kllsltba部分分配了 86M内存，并且从blksz=262144 分配的内存块个数，可知会话进程分配的PGA大小为262144个内存块，而AIX上每个内存块的大小是512Byte,所以是128M=262144/2/1024。从siz=116391936/1024/1024=111M,Freeable num=0 siz=0 Free num=0 siz=0

这就与前面分配的私有SQL区域大小111M联系起来了

从以上信息可以看到整个数据库实例实例分配的PGA私有SQL区域（Private SQL Area）总大小是420M,执行该SQL语句的会话进程私有SQL区域大小是111M。而PGA的总大小设置为8G,使用手动PGA管理，并且将hash_area_size,sort_area_size设置为500M，为何在分配11M内存后就不能分配内存了?

因为之前的测试一直都是通过TNS服务名来连接数据库，如是使用SQL*Plus来（通过OS认证与本地连接）来执行，结果执行成功。原因在哪里呢？

通过OS认证与本地连接来执行是没有通过监听的，基本可以确认与监听是有关系的。

rac中的监听是由clusterware来管理，并且从11g开始，安装clusterware从oracle用户分离给grid用户。

如是，我想到了会影响进程使用内存资源的shell limits，检查一下用户的shell limits情况。

从上面的资源限制信息可以看到,这里只给oracle用户设置了资源限制参数，grid用户并没有。

在没有设置的情况下，用户就会使用默认值。从默认值可以看到，软件文件大小fsize=2097151个数据块,软件核心文件大小core=2097151个数据块，每个进程的CPU使用时间cpu==-1没有限制，软件数据段大小data=262144个数据块，软件真实内存使用大小rss=-1没有限制，软件堆段大小stack=65536个数据块，软件文件描述限制nofiles=2000。

而且AIX的每个数据块大小是512 byte.之前的跟踪文件分配的PGA内存为262144个数据块与这里的data=262144是相关的。所以通过监听程序生成的服务器进程最多只能分配262144个数据块的内存(也就是128M)。

这就是为什么，只要查询的数据量大，需要使用的内存超过这个限制就是出现ORA-04030: 在尝试分配 123416 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足的真正原因。

大家会想，为什么都给Oracle用户设置了shell limits，为什么没有给grid用户设置了。

oracle 的aix平台安装手册中是有shell limits配置的说明。

后来问了安装这套RAC的工程师，得到的说法是，他所看到的RAC安装攻略中没有给grid用户设置，只有给oracle用户设置。

解决方法也很简单，就是对grid用户设置shell limits,然后重启监听程序。