客户打电话说系统慢,检查alert log 日志发现出现4031错误。
Fri Dec 6 13:02:13 2013
Errors in file /oracle10g/admin/ora10g/bdump/ora10g_ora_23581.trc:
ORA-12012: error on auto execute of job 9860
ORA-04031: unable to allocate ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SELECT INST_ID FROM GV$INSTANCE","sga heap(1,0)","kg
lsim object batch")
ORA-06512: at "ORACLE_OCM.MGMT_CONFIG", line 135
ORA-06512: at "ORACLE_OCM.MGMT_CONFIG", line 137
bytes of shared memory ("","","","")
Fri Dec 6 13:02:15 2013
Errors in file /oracle10g/admin/ora10g/bdump/ora10g_ora_19438.trc:
ORA-12012: error on auto execute of job 8950
ORA-04031: unable to allocate ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SELECT ONLINE$ FROM TS$ WHER...","sga heap(1,0)","kg
lsim object batch")
ORA-06512: at "SYS.DBMS_STATS", line 19244
bytes of shared memory ("","","","")
Fri Dec 6 13:02:24 2013
Errors in file /oracle10g/admin/ora10g/bdump/ora10g_ora_18577.trc:
ORA-12012: error on auto execute of job 8951
ORA-04031: unable to allocate ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SELECT O.ID, O.TYPE, OT.OBJE...","sga heap(1,0)","kg
lsim object batch")
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1500
ORA-06512: at "SYS.DBMS_SPACE", line 1566
bytes of shared memory ("","","","")
为了尽快解决客户问题,使用alter system flush shared_pool;命令刷新了shared pool池子。
过会又出现了,这时检查了内存相关参数
sga_max_size=6G
sga_target=6G
shared_pool_size=1200M
db_cache_size=4800M;
这时,感觉是buffer cache设置的太大了。修改了buffer cache 大小后,4031问题消失。
alter system set db_cache_size=0 scope=both;
和客户沟通才了解到,他们自己的DBA认为buffer cache 一定要设一个值,来保证减少物理读。这次无意的调整导致了这次4031错误。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/11590946/viewspace-1062563/
,如需转载,请注明出处,否则将追究法律责任。