ORA-04031错误导致shared_pool问题

环境描述：

数据库版本：Oracle 11.2.0.1

操作系统：AIX 7.1

物理内存：64G

双节点 RAC

发生故障现象：客户端无法连接数据库，数据库无响应

 

 

检查报警日志为如下错误信息：

Sun Aug 20 06:00:22 2017
Errors in file /u01/app/oracle/diag/rdbms/dspdb/DSPDB1/trace/DSPDB1_dbrm_12583066.trc  (incident=5404
47):
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","unknown object","KGSKI sche
dule","KGKP classinst")
Incident details in: /u01/app/oracle/diag/rdbms/dspdb/DSPDB1/incident/incdir_540447/DSPDB1_dbrm_12583
066_i540447.trc
Sun Aug 20 06:00:24 2017
Errors in file /u01/app/oracle/diag/rdbms/dspdb/DSPDB1/trace/DSPDB1_ora_7405642.trc  (incident=540855
):
ORA-04031: unable to allocate 2944 bytes of shared memory ("shared pool","unknown object","sga heap(2
,1)","KGLHD")
Incident details in: /u01/app/oracle/diag/rdbms/dspdb/DSPDB1/incident/incdir_540855/DSPDB1_ora_7405642_i540855.trc
Sun Aug 20 06:00:24 2017
Trace dumping is performing id=[cdmp_20170820060024]
Errors in file /u01/app/oracle/diag/rdbms/dspdb/DSPDB1/trace/DSPDB1_dbrm_12583066.trc:
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","unknown object","KGSKI sche
dule","KGKP classinst")
Sun Aug 20 06:00:25 2017
Errors in file /u01/app/oracle/diag/rdbms/dspdb/DSPDB1/trace/DSPDB1_gen0_12256234.trc  (incident=5404
31):
ORA-04031: unable to allocate  bytes of shared memory ("","","","")
Incident details in: /u01/app/oracle/diag/rdbms/dspdb/DSPDB1/incident/incdir_540431/DSPDB1_gen0_12256
234_i540431.trc
Sun Aug 20 06:00:26 2017
Sweep [inc][540855]: completed
Sweep [inc][540447]: completed
Sweep [inc][540431]: completed
Sweep [inc2][540855]: completed
Sweep [inc2][540447]: completed​

 

问题分析：

这是Oracle 对这个报错的解释：

oerr ORA 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool or Streams 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
//          initialization parameters SHARED_POOL_RESERVED_SIZE and
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE. 
//          If the error is issued from an Oracle Streams or XStream process,
//          increase the initialization parameter STREAMS_POOL_SIZE or increase
//          the capture or apply parameter MAX_SGA_SIZE.

引起这个报错的原因一般情况下有如下原因：

1. 内存中有大量碎片，导致在分配内存的时候，没有连续的内存可存放，这个问题需要在开发上着手，增加绑定变量，减少硬解析。
2. 要么就是内存不足，需要扩大内存。

我查过发生这个问题前到发生这个报错的AWR报告：

数据库内存占用率不到60%，所以不太可能由于内存不足造成，加上发生故障是早上6:00左右，是业务非高峰时间

 

以下是显示软解析81.03%,表明硬解析比较多，所以增加绑定量减少硬解析的

共享池锁争用等待事件比较严重

 

使用以下命令查出没有绑定变量的SQL语句有309个。

SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
     (SELECT  FORCE_MATCHING_SIGNATURE,
              COUNT(*) cnt
     FROM     v$sqlarea
     WHERE    FORCE_MATCHING_SIGNATURE!=0
     GROUP BY FORCE_MATCHING_SIGNATURE
     HAVING   COUNT(*) > 20
     )
     ,
     sq AS
     (SELECT  sql_text                ,
              FORCE_MATCHING_SIGNATURE,
              row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
     FROM     v$sqlarea s
     WHERE    FORCE_MATCHING_SIGNATURE IN
              (SELECT FORCE_MATCHING_SIGNATURE
              FROM    c
              )
     )
SELECT   sq.sql_text                ,
         sq.FORCE_MATCHING_SIGNATURE,
         c.cnt "unshared count"
FROM     c,
         sq
WHERE    sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND      sq.p                       =1
ORDER BY c.cnt DESC;​

 

解决方案：

修改SQL增加绑定变量，减少硬解析，避免共享池锁争用，在内存自动管理的模式下，硬解析太多会导致内存抖动。

 

紧急处理：重启实例

 

参考博客：http://www.laoxiong.net/an-ora-04031-case.html