Oracle 11g 11.2.0.2 Bug 10082277 – Excessive allocation in PCUR or KGLH0 heap of “kkscsAddChildNo”

 

轉自eagle 的blog， 原文連結地址如下： http://www.dbafan.com/blog/?p=495

 

11gR2 還沒怎麼研究，轉貼過來，以防以後出現這個問題。

 ---------------- -Begin  ------------------

在11.2.0.2的資料庫上，我們遇到了oracle 11g的Bug 10082277 Excessive allocationin PCUR or KGLH0 heap of “kkscsAddChildNo” (ORA-4031)

Bug 10082277 Excessive allocation in PCUR or KGLH0 heap of “kkscsAddChildNo”(ORA-4031)
This note gives a brief overview of bug 10082277.
The content was last updated on: 26-AUG-2011
Click here for details of each of the sections below.
Affects:

Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected

11.2.0.2
11.2.0.1

Platformsaffected Generic (all / most platforms affected)

Fixed:

This issue isfixed in

12.1 (Future Release)
11.2.0.3 (Future Patch Set)
11.2.0.2.3 Patch Set Update
11.2.0.2 Bundle Patch 4 for Exadata Database
11.2.0.2 Patch 2 on Windows Platforms
11.2.0.1 Patch 11 on Windows Platforms

Description

Under certain circumstances the “perm” space in PCURsubheaps (11.2.0.1)
or KGLH0 subheaps (11.2.0.2) for cursors in the shared pool may continue
to grow over time with continual additions of memory of the type
“kkscsAddChildNo”.

Over time this can use excessive shared pool memory , evenleading
to ORA-4031 errors.

Rediscovery Notes:
A heapdump of the SGA will show one or more very large
subheaps with a name of the form “PCUR^xxxx” or “KGLH0^xxxx”.
Within this subheap there is lots of “perm” space allocated.
If CPRM tracing is enable (event 10235 level 65536) then
the subheap dump shows the perm space as due to “kkscsAddChildNo”
allocations of memory.

Workaround
This issue requires that there is a lack of sharing of the
parent cursor. Hence a workaround, where possible, is to
address the reason for not sharing the parent cursor.
(eg: Check V$SQL_SHARED_CURSOR for why the parent is not
being shared).

該問題在我們的系統上表現為

1. 單個SQL佔用了大量的shared pool memory, 這個例子中單個SQL就佔用了1.7GB的sharedpool memory

select VERSION_COUNT,SHARABLE_MEM from v$sqlarea where
hash_value=2038009379;
 
VERSION_COUNT SHARABLE_MEM
------------- ------------
           96   1888704961

而在10.2.0.4的資料庫上，同樣的SQL只佔用了4M左右大小的記憶體

select VERSION_COUNT,SHARABLE_MEM from v$sqlarea where
hash_value=2038009379;
 
VERSION_COUNT SHARABLE_MEM
------------- ------------
          214      4216097

2. 大部分的記憶體都被parent cursor (cursor id為65535)佔用了

通過Tanel的指令碼curheaps.sql 可以查看各個child cursor的大小

SQL> @curheaps 2038009379 65535
old  20:        KGLNAHSH in (&1)
new  20:        KGLNAHSH in (2038009379)
old  21: and    KGLOBT09 like ('&2')
new  21: and    KGLOBT09 like ('65535')
 
  KGLNAHSH KGLHDPAR             CHILD# KGLHDADR
KGLOBHD0                         SIZE0    SIZE1    SIZE2    SIZE3
---------- -------------------------- ---------------- ----------------
------------------------------ -------- --------
KGLOBHD4            SIZE4    SIZE5 KGLOBHD6            SIZE6    SIZE7
STATUS
---------------- ---------------- ---------------- -------- --------
----------
2038009379 0000000F3BC53E78      65535 0000000F3BC53E78
0000000F5BF1E648             *1883443712        *0        0        0
00                     0        0 00                     0
0          1
 
 
old  10:     KSMCHDS = hextoraw('&v_curheaps_kglobhd0')
new  10:     KSMCHDS = hextoraw('0000000F5BF1E648')
 
HEAP  CLASS    ALLOC_COMMENT         BYTES     CHUNKS
----- ------------------------ ---------- ----------
HEAP0 perm     permanent memor  *1898642464    *474659
HEAP0 free     free memory        26531224     473772
HEAP0 freeabl  kksfbc:hash1          4872         96
HEAP0 freeabl  kgltbtab               912          6
 
old  10:     KSMCHDS = hextoraw('&v_curheaps_kglobhd4')
new  10:     KSMCHDS = hextoraw('00')
 
no rows selected
 
old  10:     KSMCHDS = hextoraw('&v_curheaps_kglobhd6')
new  10:     KSMCHDS = hextoraw('00')
 
no rows selected

另外該問題只發生在client的jdbc driver升級到11g以後，jdbcdriver為10g的時候沒有這個問題，估計和sharedcursor sharing有關係。

SQL的 parent cursor不斷增長一方面會使得shared pool的記憶體耗盡，另外如果發生hard parse耗時非常嚴重，可能會導致大量的和parse相關的等待時間，例如“cursor: mutex S”。

Oracle有相關的patch可以下載，打上patch後問題解決。

curheaps.sql

------------------------------------------------------------------------------------ File name:   curheaps.sql-- Purpose:     Show main cursor data block heap sizes and their contents--              (heap0 and heap6)---- Author:      Tanel Poder-- Copyright:   (c) http://www.tanelpoder.com--              -- Usage:       @curheaps <hash_value> <child#>----              @curheaps 942515969 %   -- shows a summary of cursor heaps--            @curheaps 942515969 0   -- shows detail for child cursor 0---- Other:       "Child" cursor# 65535 is actually the parent cursor----------------------------------------------------------------------------------col curheaps_size0 heading SIZE0 for 9999999col curheaps_size1 heading SIZE1 for 9999999col curheaps_size2 heading SIZE2 for 9999999col curheaps_size3 heading SIZE3 for 9999999col curheaps_size4 heading SIZE4 for 9999999col curheaps_size5 heading SIZE5 for 9999999col curheaps_size6 heading SIZE6 for 9999999col curheaps_size7 heading SIZE7 for 9999999col KGLOBHD0 new_value v_curheaps_kglobhd0 printcol KGLOBHD1 new_value v_curheaps_kglobhd1 noprintcol KGLOBHD2 new_value v_curheaps_kglobhd2 noprintcol KGLOBHD3 new_value v_curheaps_kglobhd3 noprintcol KGLOBHD4 new_value v_curheaps_kglobhd4 printcol KGLOBHD5 new_value v_curheaps_kglobhd5 noprintcol KGLOBHD6 new_value v_curheaps_kglobhd6 printcol KGLOBHD7 new_value v_curheaps_kglobhd7 noprintselect KGLNAHSH,KGLHDPAR,kglobt09 CHILD#,KGLHDADR,KGLOBHD0, KGLOBHS0 curheaps_size0,KGLOBHD1, KGLOBHS1 curheaps_size1,KGLOBHD2, KGLOBHS2 curheaps_size2,KGLOBHD3, KGLOBHS3 curheaps_size3,KGLOBHD4, KGLOBHS4 curheaps_size4,KGLOBHD5, KGLOBHS5 curheaps_size5,KGLOBHD6, KGLOBHS6 curheaps_size6,KGLOBHD7, KGLOBHS7 curheaps_size7,--KGLOBT00 CTXSTAT,KGLOBSTA STATUSfrom X$KGLOB--X$KGLCURSOR_CHILDwhereKGLNAHSH in (&1)andKGLOBT09 like ('&2')order by        KGLOBT09 ASC/-- Cursor data block summaryselect    'HEAP0'        heap  , ksmchcls      class  , ksmchcom      alloc_comment  , sum(ksmchsiz) bytes  , count(*)      chunksfrom     x$ksmhpwhere     KSMCHDS = hextoraw('&v_curheaps_kglobhd0')group by   'HEAP0'  , ksmchcls  , ksmchcomorder by    sum(ksmchsiz) desc/select    'HEAP4'        heap  , ksmchcls      class  , ksmchcom      alloc_comment  , sum(ksmchsiz) bytes  , count(*)      chunksfrom     x$ksmhpwhere     KSMCHDS = hextoraw('&v_curheaps_kglobhd4')group by   'HEAP6'  , ksmchcls  , ksmchcomorder by    sum(ksmchsiz) desc/select    'HEAP6'        heap  , ksmchcls      class  , ksmchcom      alloc_comment  , sum(ksmchsiz) bytes  , count(*)      chunksfrom     x$ksmhpwhere     KSMCHDS = hextoraw('&v_curheaps_kglobhd6')group by   'HEAP0'  , ksmchcls  , ksmchcomorder by    sum(ksmchsiz) desc/-- Cursor data block details-- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd0');-- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd6');

 

 

 

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

Blog： http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929(滿) DBA5群： 142216823(滿) 

DBA6 群：158654907(滿)   DBA7 群：69087192(滿)  DBA8 群：172855474

DBA 超級群2：151508914  DBA9群：102954821     聊天 群：40132017(滿)

--加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請