interval partition自动添加分区引起的shared pool 4031错误

最新推荐文章于 2023-10-06 17:20:54 发布

cuiruipan6325

最新推荐文章于 2023-10-06 17:20:54 发布

阅读量675

点赞数

文章标签：数据库内存管理

某exdata下8节点RAC大型数据仓库，11.2.0.3.23版本，其中一节点监控警报状态异常，查看数据库后台alert log最开始报
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","PLMCD^e481b9fe","kglHeapInitialize:temp")
往后报：ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KKSSP^50","kglseshtSegs")
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","PLMCD^e481b9fe","kglHeapInitialize:temp")
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^78faa4dd","kglHeapInitialize:temp")
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","EV_EVENT","PRTMV^18f08f0b","Allocate kctph[]/ckyph[]/ckyprt[] array")
.....
等等一系列的4031，shared pool无法分配。

进入sqlplus，发现sqlplus里执行sql 都返回 4031错误。

遇到shared pool 4031错误，导致大部分sql无法解析的情况，一般都是由于shared pool 碎片化严重引起。

应急方法是重启数据库或执行刷新shared pool 。

我们采取重启暂时来解决故障，做了几次 checkpoint 和切换日志之后，使用shutdown immediate 方式关闭数据库，但但发现oracle 一直停不下来，查看后台日志发现一直卡在MMON进程上，
Stopping background process MMON
Fri Jan 08 19:44:50 2016
Background process MMON not dead after 30 seconds
Killing background process MMON
License high water mark = 248

最后还是重开窗口直接 shutdown abort，重启之后，数据库状态恢复正常。

于是便开始分析 trace文件，查找导致该故障的原因。实际当时几个人分析了好久一直无法定位，最后还是通过SR来解决问题，现在来阐述最后的分析结果。

从alert 日志上看，
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","PLMCD^e481b9fe","kglHeapInitialize:temp")
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^e481b9fe","kglHeapInitialize:temp")
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","EV_EVENT","PRTMV^18f08f0b","Allocate kctph[]/ckyph[]/ckyprt[] array")
出现的频率较多。

再查看报错之前的数据库是否存在什么操作，实际上这一点很容易让人忽视，但往往是触发某些错误的源头，数据库一直有出现   ADDED INTERVAL PARTITION的操作出现，而SR最后也将这个作为导致问题的原因。

Fri Jan 08 07:49:18 2016
TABLE IDL.DM_COR_DPS_AC_BAL_DTL_FALL_D_P: ADDED INTERVAL PARTITION SYS_P298027 (42375) VALUES LESS THAN (TO_DATE(' 2016-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Fri Jan 08 07:49:32 2016
TABLE IDL.DM_COR_DPS_CU_BAL_DTL_FALL_D_P: ADDED INTERVAL PARTITION SYS_P298028 (42375) VALUES LESS THAN (TO_DATE(' 2016-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Fri Jan 08 07:58:51 2016
TABLE IDL_RDM.BACA_INDIV_APPL: ADDED INTERVAL PARTITION SYS_P298029 (42375) VALUES LESS THAN (TO_DATE(' 2016-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Fri Jan 08 09:03:19 2016

从trace 文件中呈现当时存在大量的  SGA: allocation forcing component growth等待事件。

分析个subpool的使用情况：
Memory Utilization of Subpool 1
================================
Allocation Name Size
___________________________ ____________
"free memory " 83432448

Memory Utilization of Subpool 2
================================
Allocation Name Size
___________________________ ____________
"free memory " 132365824

Memory Utilization of Subpool 3
================================
Allocation Name Size
___________________________ ____________
"free memory " 78452768

==============================
Memory Utilization of Subpool 4
================================
Allocation Name Size
___________________________ ____________
"free memory " 63742208

Memory Utilization of Subpool 5
================================
Allocation Name Size
___________________________ ____________
"free memory " 144281784

Memory Utilization of Subpool 6
================================
Allocation Name Size
___________________________ ____________
"free memory " 105139176

Memory Utilization of Subpool 7
================================
Allocation Name Size
___________________________ ____________
"free memory " 230405048

在第三subpool 发现了一个异常大小的组件，这个竟然差不多到了12G。

"PRTMV " 12293877592


而该数据库采用手工内存管理，shared pool的大小为7G，sga max 为20G。

直接在MOS上搜PRTMV会发现因PRTMV分配过高而导致4031的问题非常多，结合该库上时常有  ADDED INTERVAL PARTITION的DDL操作，

最终oracle给出的说法是就是由于在间隔分区表上同时执行DML和 DDL导致给shared pool  的PRTMV  过多的内存导致的问题。
Bug 19461270 - high PRTMV allocations in shared pool executing concurrent DML and DDLs on interval partitioned tables (文档 ID 19461270.8)

给出的建议为：

1.升级到11.2.0.3.28，再打上  patch 19461270。
or
2. 升级到11.2.0.4.0版本，再打上  patch 19461270。
or
3. Use the following workaround: Flush the shared pool

You could periodically monitor the utilization of heap 'PRTMV'.
In particular after partitioned table maintenance were done.

In case 'PRTMV' would increase to an large extend (for example > 200 MB)
you could flush the shared pool to avoid ORA-4031.

SQL> select name,bytes from v$sgastat where name='PRTMV';

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

转载于:http://blog.itpub.net/29863023/viewspace-1974361/