oracle 10g 4031

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

现察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频率；

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!