ORA-04031导致RAC实例当掉案例

        这个案例是发生在朋友管理的RAC服务器上，讨论这个案例前首先讨论一下经常遇到的ORA-04030和ORA-04031的区别。

ORA-04030: out of process memory when trying to allocate string bytes (string,string)
Cause: Operating system process private memory has been exhausted
Action: none

ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

        从上面对错误的描述不难看出，ORA-04030是无法分配private memory而报错，ORA-04031是无法分配shared memory而报错。也就是说，报ORA-04030是因为服务器没法为服务器进程分配PGA空间导致的问题，ORA-04031是SGA中的共享池没法分配内存导致的问题。

1.环境描述：
操作系统：AIX 5.3
Oracle RAC版本：Oracle RAC 10.2.0.4
内存大小：128GB
SGA设置为：28GB

2.报错信息及分析：
在cwgkvpd1_smon_201004.trc SMON进程跟踪文件中发现如下的报错：
===============================
End 4031 Diagnostic Information
===============================
*** 2011-11-28 17:57:07.387
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","sql area","tmp")
*** 2011-11-28 17:57:07.398
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","sql area","tmp")
*** 2011-11-28 17:57:07.993
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","sql area","tmp")
*** 2011-11-28 17:57:08.267
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","sql area","tmp")

据朋友说，该问题导致的问题是SMON进程挂掉了，但是PMON进程是活动的。

在cwgkvpd1_cjq0_181876.trc的跟踪文件中也发现了类似的错误：
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","sql area","tmp")
*** 2011-11-28 17:57:04.609
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select i.obj#,i.ts#,i.file#,...","sql area","tmp")
*** 2011-11-28 17:57:09.619
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","sql area","tmp")
*** 2011-11-28 17:57:11.502
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select i.obj#,i.ts#,i.file#,...","sql area","tmp")

        ORA-04031的错误最大的可能是由于共享池中的碎片过多造成的，于是让朋友查了下SQL的软解析命令率，查询的结果是69.2%，再查询V$SGA_TARGET_ADVICE视图，发现SGA的大小设置可以满足需求，那么可以初步断定是由于应用程序没有绑定变量导致大量的硬解析发生，致使共享池中的碎片过多，在SMON进程执行内部操作的时候无法分配请求大小的共享池空间导致的问题。
        建议就是调整应用，使用绑定变量或适当增加SGA的大小，为共享池设定一个最小值，减少问题发生的几率。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-712452/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-712452/