11.2.0.3 RAC ORA-04031一例
1、该库采用手动内存管理方式 但由于11.2中引入了 _MEMORY_IMM_MODE_WITHOUT_AUTOSGA 新特性，所以即便采用了手动内存管理方式，当shared pool不够时，其还是会向buffer cache借用内存
2、通过AWR快照可以看到在 时间点的半个小时时间内， buffer cache从140,800M 收缩到 1,536M ， 而Shared Pool从137,728M 扩展到了276,992M， Shared Pool扩大了一倍
Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 56494 02:30:27 2,043 7.4 End Snap: 56495 03:00:08 2,269 7.2 Elapsed: 29.68 (mins) DB Time: 14,423.95 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 140,800M 1,536M Std Block Size: 8K Shared Pool Size: 137,728M 276,992M Log Buffer: 690,608K
3、从hard parse解析率看，平均为每秒14.8次，并不算很高
4、 通过SGA Breakdown 可以看到 shared pool中的KGLH0的大小 从 106,845MB 扩大到 245,261.6MB ，占shared pool内存使用量的88%
即KGLH0 的内存用量达到了 239GB，如此大量的内存占用，KGLH0可能存在内存泄露
5、shared pool共有7个subpool
Memory Utilization of Subpool 1 "free memory " 486322936 "KGLH0 " 336870024 Memory Utilization of Subpool 2 "free memory " 536832848 "KGLH0 " 289153640 Memory Utilization of Subpool 3 "free memory " 1060566792 "KGLH0 " 244375800 Memory Utilization of Subpool 4 "free memory " 558650672 "KGLH0 " 297159592 Memory Utilization of Subpool 5 "free memory " 561649768 "KGLH0 " 298433192 Memory Utilization of Subpool 6 "free memory " 2020790976 "KGLH0 " 256431434592 Memory Utilization of Subpool 7 "free memory " 427661912 "KGLH0 " 250737288
6、 11.2.0.3 上存在多个 KGLH0可能内存泄露的BUG，包括但不限于：
Bug Description Fixed 13250244 KGL handles not freed across subpools 12.1 13845795 KGLH0 memory leak (kkscsAddChildNode) 13989252 Memory leaks when parse errors are repeatedly seen 14762603 KGLH0 memory grows over time (comment is "kgldahds") 14770516 KGLH0 memory leak (qksmm.c)
–
建议配合stddb系统的实际补丁 情况 进一步分析该问题
7、与本案例较为相似的 KGLH0 memory leak (kkscsAddChildNode) Bug 13845795: OVER 200M ALLOCATION IN KGLH0 UNDER SHARED POOL 在版本11.2.0.4中被认为fix了
“DIAGNOSTIC ANALYSIS: -------------------- Following is the subpool allocation. Memory Utilization of Subpool 1 ================================ Allocation Name Size ___________________________ ____________ "free memory " 107651752 "KGLH0 " 1735013624 Memory Utilization of Subpool 2 ================================ Allocation Name Size ___________________________ ____________ "free memory " 132077536 "KGLH0 " 1570535600 Memory Utilization of Subpool 3 ================================ Allocation Name Size ___________________________ ____________ "free memory " 110924488 "KGLH0 " 1744998048 Memory Utilization of Subpool 4 ================================ Allocation Name Size ___________________________ ____________ "free memory " 122399328 "KGLH0 " 1987217160 Customer is on 11.2.0.3 and Bug 13845795: OVER 200M ALLOCATION IN KGLH0 UNDER SHARED POOL addresses the similar issue.”
8、此外AWR中显示 SQL SQL1 的Sharable Mem达到了177MB ， 个别SQL 如SQL2的version count达到了4,096 则说明系统中可能由于应用SQL的特点，存在游标共享类的问题，建议进一步调优
Comment