oracle10g报错27123,Oracle 10g 报错 ORA-04031 的解决方案

Oracle 10g慢慢退出了,但用的地方也还有。这里再记一下案例:

数据库16:58出现大量4031,导致数据库无法使用,取对应时段的AWR

发现shared_pool一直在收缩;

查看日志:

** 2018-07-26 16:58:09.868

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select count() from sys.job...","sql area","tmp")

查看对应的trc:

LIBRARY CACHE STATISTICS:

namespace          gets hit ratio      pins hit ratio    reloads  invalids

-------------- --------- --------- --------- --------- ---------- ----------

CRSR          889957320    0.245 2974322326    0.877    6674225    3570108

出现大量的cursor无法共享;

并伴随出现:

last wait for 'SGA: allocation forcing component growth' blocking sess=0x0000000000000000 seq=62360 wait_time=7064 seconds since wait started=0

=0, =0, =0

Dumping Session Wait History

for 'SGA: allocation forcing component growth' count=1 wait_time=7064

=0, =0, =0

现察subpool:

Memory Utilization of Subpool 2

Allocation Name          Size

_________________________  __________

"free memory              "  -2020262480

由于系统已重启,只能检查现在的resize情况:

set linesize 1000;

SELECT start_time,

component,

oper_type,

oper_mode,

initial_size/1048576 "INITIAL MB",

final_size/1048576  "FINAL MB",

end_time

FROM  v$sga_resize_ops

WHERE  component IN ( 'DEFAULT buffer cache', 'shared pool' )

AND status = 'COMPLETE'

ORDER  BY start_time,

component;

发现调整还是很频繁;

检查历史的sga分配情况:发现故障时段大量的内存分配了kgh:no access

select * from DBA_HIST_SGASTAT where name in ('buffer_cache','sql area','KGH: NO ACCESS') and snap_id>40630

解决办法:

1 打补丁

Patch 7189722: APPSST GSI 10G : VERY FREQUENT GROW/SHRINK SGA RESIZE OPERATION HAPPENING

2 禁用ASMM功能;

参见MOS:

How To Prevent The Growth Of The Component 'KGH: NO ACCESS' In The Shared Pool When ASMM Is Enabled (Doc ID 451960.1)

Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" Memory Allocation [Video] (Doc ID 801787.1)

3 依旧启用ASMM,但设置buffer cache/shared pool的最小值.

4 调整_memory_broker_stat_interval的值,减少sga 的auto resize频率;

0b1331709591d260c1c78e86d0c51c18.png

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值