【优化】XXXX管理系统ORA-04031问题诊断优化

最新推荐文章于 2024-04-25 17:43:35 发布

weixin_34146986

最新推荐文章于 2024-04-25 17:43:35 发布

阅读量153

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3862440/blog/2051940

版权

2019独角兽企业重金招聘Python工程师标准>>>

XXXX管理系统
1.问题概述
XXXX管理系统在2018年5月11日上午10:30左右出现数据库无法访问，应用系统无法工作，DBA登陆数据库主机发现数据库后台报ORA-04031的错误，导致数据库无法正常提供服务。
2.问题分析
DBA对数据库告警日志进行分析，发现后台出现如下错误:
Errors in file /crbank/kms/app/product/database/diag/rdbms/kms/kms/trace/kms_cjq0_11482.trc (incident=30101):
ORA-04031: unable to allocate 3896 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”kglsim object batch”)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /crbank/kms/app/product/database/diag/rdbms/kms/kms/trace/kms_cjq0_11482.trc (incident=30102):
ORA-04031: unable to allocate bytes of shared memory (“”,”“,”“,”“)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu May 10 11:08:24 2018
Errors in file /crbank/kms/app/product/database/diag/rdbms/kms/kms/trace/kms_ora_12165.trc (incident=30064):
ORA-04031: unable to allocate 3896 bytes of shared memory (“shared pool”,”update clusterReg set regTim…”,”sga heap(1,0)”,”kglsim object batch”)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu May 10 11:08:24 2018
Errors in file /crbank/kms/app/product/database/diag/rdbms/kms/kms/trace/kms_ora_11645.trc (incident=30052):
ORA-04031: unable to allocate 3896 bytes of shared memory (“shared pool”,”update clusterReg set regTim…”,”sga heap(1,0)”,”kglsim object batch”)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /crbank/kms/app/product/database/diag/rdbms/kms/kms/trace/kms_cjq0_11482.trc (incident=30103):
ORA-04031: unable to allocate 3896 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”kglsim object batch”)
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

发现数据库shared_pool 第一个子池的内存不足导致ORA-04031，对这个子池进行分析，发现shared_pool组件SQLA占用了大量的内存，此池主要是存放SQL语句：

Memory Utilization of Subpool 1
  Allocation Name               Size    

“free memory ”                  1566210720 
“miscellaneous ”                   0 
(省略) 
“kglsim alloc latch area ”      2720 
“SQLA ”                         1854384792 
“SQLP ”                         363440 
“SQLK ”                         24288 
“work area tab ”                118320

从上面的子池发现SQLA组件使用1.8G内存，结合业务情况，导致SQLA组件使用这么大的内存，是因为SQL语句没有绑定变量，导致SQL不能共享，查看v$sql视图，发现下面两类sql：
语句1.
insert into XX (seqNo,lmkProtectMode,pkValue,vkValue) VALUES (‘20180511112831011312’,’01’,’CCC589867C1AF691E5D232B3CC34183C6D4F7928214B9A5C3251290C28EC11ED5C89A718910ADD4F3326DEBD0C5AD72B8125C6D94D629FB5F85056CC35EDC23370A8E15629F8EDAB68E0EDF1F3B70428A4BEEB20B222C9174681C0C1814839DB82D47FE2E200F64574E18072A78153410A19A1E42D12F0E2588E43D174D8143EFB6546C7394DD3B2E355C9AF58A8B54ECEC7E80A286B7509C71A51084D40209AC3548264446B8DF2A5469E5B07E145C1’,’000203482D2D584EEE325527290189E832FCAD14B0CE18ECB5B3152F1B241464FF55A1541B241464FF55A15431E943E0ABD899C6D2EA9BBEDF20FF959407B413D9935BA5B2E41F74896DA0707E82399C6654A69BEE87AA83164DC6193B8C4448A9716E3260B44C7A459409DCC54FC0718E56A5374916CC0494A1B652E9B4FBD329155E13E37B9712E8E7B816841F4C7AC704F99D102EB3576FFA750064B21AF50EAC1151F8B9AACA57EFFB41A61B77B2A7F89AE09C50294FC3C2DA1F247B9B23608519749319595A3C60B08E17EF56048DA35AFAB951DA829BE41F3BB478B17AADB3A8A43E5127FEEB47C1BC10EC59EC35AB73B1CEBFB0B1C4EAF9EEA59A4528B70A1E359BDA604D4EC60B9DBEE6
语句2.
update XX set regTime = ‘20180511160528’ where hostname = ‘pkmsapp02’
这2个语句因为没有绑定变量导致SQLA组件占用了大量的内存:

从上面查询的结果，sqla组件里面存在上面2条语句大量没有绑定变量，从而导致SQLA组件占用了大量的内存.
3 问题总结和建议
1. 对上面2类语句进行绑定变量，传入参数替代常量值
2. 加大shared pool 大小。
3. 关闭sga自动管理，启用手动管理。对于9i 10g设置sga_target=10g

转载于:https://my.oschina.net/u/3862440/blog/2051940