ORA-04031浅谈

1. 引言

昨天在一套生产库上碰到了ORA-04031错误,碰到了ORA-04031错误,那肯定是共享池的某个子池出现了内存不够分配导致。
我不想解释太多共享池的原理和ORA-04031发生的技术成因,比如发生之前内存搜索、合并等等,这块请查相关资料。主要这块内容是Oracle的算法,我们没办法控制什么。
今天就想谈谈,发生了这个问题一般如何确定原因和处理。

2. 故障情况

故障情况说明:

Sun Jan 22 16:26:11 EAT 2017
Errors in file /oracle/app/oracle/admin/xxxxx2/bdump/xxxxx22_j006_8166.trc:
ORA-12012: 自动执行作业 2776 出错
ORA-04031: 无法分配 4120 字节的共享内存 ("shared pool","select o.owner#,o.name,o.nam...","Typecheck","kgghteInit")
ORA-06512: 在 "RESLYG.SP_TS_DIS_ID_EVENT", line 47
ORA-06512: 在 line 1
Sun Jan 22 16:26:19 EAT 2017
Errors in file /oracle/app/oracle/admin/xxxxx2/bdump/xxxxx22_j001_7899.trc:
ORA-12012: 自动执行作业 2796 出错
ORA-04031: 无法分配 200 字节的共享内存 ("shared pool","DECLARE job BINARY_INTEGER :...","PL/SQL DIANA","IDL Heap Parts Holder")
Sun Jan 22 16:26:27 EAT 2017
Errors in file /oracle/app/oracle/admin/xxxxx2/bdump/xxxxx22_j009_8799.trc:
ORA-12012: 自动执行作业 2774 出错
ORA-04031: 无法分配 48 字节的共享内存 ("shared pool","DECLARE job BINARY_INTEGER :...","parameters","kglpda")
Sun Jan 22 16:26:52 EAT 2017
Errors in file /oracle/app/oracle/admin/xxxxx2/bdump/xxxxx22_j005_18998.trc:
ORA-12012: 自动执行作业 2765 出错
ORA-04031: 无法分配 4120 字节的共享内存 ("shared pool","select o.owner#,o.name,o.nam...","Typecheck","kgghteInit")
ORA-06512: 在 "RESWX.SP_TS_DIS_WF_F_EVENT", line 47
ORA-06512: 在 line 1
Sun Jan 22 16:28:51 EAT 2017
Errors in file /oracle/app/oracle/admin/xxxxx2/bdump/xxxxx22_j008_21011.trc:
ORA-12012: 自动执行作业 2758 出错
ORA-04031: 无法分配 4120 字节的共享内存 ("shared pool","DECLARE job BINARY_INTEGER :...","Typecheck","kgghteInit")
Sun Jan 22 16:32:07 EAT 2017
Thread 2 advanced to log sequence 196770 (LGWR switch)
  Current log# 8 seq# 196770 mem# 0: /redolog/xxxxx2/group2/redo881.log
  Current log# 8 seq# 196770 mem# 1: /redolog/xxxxx2/group2/redo882.log
Sun Jan 22 16:32:10 EAT 2017
Errors in file /oracle/app/oracle/admin/xxxxx2/bdump/xxxxx22_j006_29326.trc:
ORA-12012: 自动执行作业 2738 出错
ORA-04031: 无法分配 1056 字节的共享内存 ("shared pool","DECLARE job BINARY_INTEGER :...","PL/SQL DIANA","PAR.C:parchk:page")

这时侯去检查共享池碎片啥的,其实没有太多意义,Oracle是不会骗人的。

3. 诊断步骤

3.1 共享池大小

碰到ORA-04031,首先就应该检查共享池的大小,如果是静态管理,要看共享池的大小是否够大。在RAC情况下,Oracle对共享池的要求是比较高的。
对于比较大的SGA,Oracle建议设置共享池大小为15%的SGA,所以在现在内存白菜价的情况下,几百G的SGA比比皆是,所以几十G的共享池也是很普遍的情况了。
可以参考一下:Best Practices and Recommendations for RAC databases with SGA size over 100GB (文档 ID 1619155.1)
对于动态管理,建议设置一下共享池大小的下限,避免共享池的过份收缩。
如果共享池的大小没有问题了,下面就需要针对共享池内部进行诊断了。

3.2 关于子池

关于子池,默认情况下,Oracle是自动分配。对于10R2以上,子池又分子池。子池个数是参数_kghdsidx_cout控制,减少子池数量可以使子池更大,碰到ORA-04031的机会更小。
以前在9iRAC上,会碰到共享池perm内存过大的问题,解决办法也就是减少子池数量,让子池更大,极限情况下就把_kghdsidx_cout设为1,只用1个子池。
但是减少子池数量,会导致共享池的可用latch变少,SQL执行压力过大时,共享池latch争用会是个问题,而且调大子池只是缓解问题,根本原因还是存在。

当然有时侯子池用得不均衡的状态,说实在的,我们也没有太多办法,只能从其它地方绕过解决。

3.3 判断共享池哪个组件大

对于发生ORA-04031,如果共享池内存够大的情况,一般情况是某个共享池组件用得特别大,通过以下脚本检查

SQL> select * from v$sgastat where pool = 'shared pool' order by bytes;
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  KTU SGA                             8
shared pool  parallel kcbibr dbwr bitv           8
shared pool  kga sga                             8
shared pool  kscdnfyglobalflags                  8
shared pool  kscdnfyinitflags                    8
shared pool  kolbsgi: KOLB's SGA initi           8
......
shared pool  library cache               311055672
shared pool  object level                338672544
shared pool  sql area                    498672024
shared pool  db_block_hash_buckets       754974720
shared pool  gcs shadows                1082217216
shared pool  obj stat memo              1838493504
shared pool  gcs resources              3079187616
shared pool  free memory                1221738992

gcs resources是用于处理RAC的缓存,这一块大小取决于RAC数据缓存大小。所以当SGA较大的情况下,共享池内存不应该太小。这里db_cache_size差不多80g的情况下,
gcs resources差不多3g左右,而当前共享池只有10g,属于偏小。
_gcs_resources 和 _gcs_shadow_locks 这两个隐含参数可以控制gcs resources和gcs
shadows组件大小。设置这两个参数,必须要检查v$resource_limit,从最大值和当前值和限制值进行综合考虑。

第二个大的是obj stat memo,这个组件比较大的原因如下:
a.数据库版本小于10.2.0.5
b.创建、删除段对象比较严重
c.在v$sgastat中显示obj stat memo存在内存泄露

 SQL> select * from v$sgastat where pool = 'shared pool' and name = 'obj stat memo';

 POOL         NAME                            BYTES
 ------------ -------------------------- ----------
 shared pool  obj stat memo              1838493504

可以进行多次查询,检查这个内存组件是否存在泄露。
d.x$ksolsfts存在很多不存在对象的数据,1条这样的数据,可能占用几百字节的内存,如果很多,占用内存也会很大。
可以通过如下SQL检查

 select distinct fts_objd, fts_objn from x$ksolsfts x
    where not exists (select 1 from obj$ o
      where o.dataobj#=x.fts_objd and o.obj#=x.fts_objn);

这次故障obj stat memo确实存在泄露的情况。所以解决办法就是:
10.2.0.5版本之前,设置 _object_statistics = false
10.2.0.5版本之后,设置 _disable_objstat_del_broadcast=false

3.4 如何诊断硬件析造成的ORA-04031

硬解析也是造成ORA-04031故障的主要原因之一,如果v$sgastat里 sql area、Ccursor等占据主要大小,那就可能与硬解析有关了。
解决硬解析最主要就是要找到哪些SQL语句在硬解析,这一块应用是不会告诉DBA的,那我们一般怎么查找呢。

select to_char(a.FORCE_MATCHING_SIGNATURE), count(1) from v$sqlarea a
group by to_char(FORCE_MATCHING_SIGNATURE)
order by 2 desc;

 TO_CHAR(A.FORCE_MATCHING_SIGNA             COUNT(1)
 ---------------------------------------- ----------
 0                                              6928
 5977430773653964215                             270
 4688721559632396657                             225
 1934495728863251069                             221
 18410670462558967655                            217
 14254918211988816520                            168
 11974274383252265321                            168
 12072296883966295896                            127
 5693652407801698617                             124
 4611803000994691556                             124
 13395851461178385123                             55
 3268990192152595750                              54
 6507843343907002268                              42
 14592185616129171199                             41
 8799612550786082754                              39
 1382477362147117091                              32
 5732851374619659170                              31
 2457081022807346300                              29
 4631434937234745000                              28
 ......

这样就可以查看哪个SQL语句硬解析最多了。

select * from v$sqlarea a where a.FORCE_MATCHING_SIGNATURE = 5977430773653964215;

4.其它

当然关于ORA-04031的问题还有很多,如保留池、游标共享、JAVA池、大池等,不过那些问题碰到的机会就比较少了,但诊断的办法也基本类似,确认哪个组件大,再针对解决。

关于紫砂壶

感悟技术人生
此条目发表在ORA-分类目录,贴了标签。将固定链接加入收藏夹。