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