11.2.0.3 RAC ORA-04031一例

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

*

沪ICP备14014813号-2

沪公网安备 31010802001379号