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

selectVERSION_COUNT,SHARABLE_MEMfromv$sqlareawhere
hash_value=2038009379;

VERSION_COUNTSHARABLE_MEM
------------- ------------
96 1888704961

而在10.2.0.4的数据库上，同样的SQL只占用了4M左右大小的内存

selectVERSION_COUNT,SHARABLE_MEMfromv$sqlareawhere
hash_value=2038009379;

VERSION_COUNTSHARABLE_MEM
------------- ------------
214 4216097

2. 大部分的内存都被parent cursor (cursor id为65535)占用了

通过Tanel的脚本curheaps.sql可以查看各个child cursor的大小

SQL> @curheaps203800937965535
old20: KGLNAHSHin(&1)
new20: KGLNAHSHin(2038009379)
old21:and KGLOBT09like('&2')
new21:and KGLOBT09like('65535')

KGLNAHSHKGLHDPAR CHILD# KGLHDADR
KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3
---------- -------------------------- ---------------- ----------------
------------------------------ -------- --------
KGLOBHD4 SIZE4 SIZE5KGLOBHD6 SIZE6 SIZE7
STATUS
---------------- ---------------- ---------------- -------- --------
----------
2038009379 0000000F3BC53E78 65535 0000000F3BC53E78
0000000F5BF1E648 *1883443712 *0 0 0
00 0 000 0
0 1


old10: KSMCHDS=hextoraw('&v_curheaps_kglobhd0')
new10: KSMCHDS=hextoraw('0000000F5BF1E648')

HEAPCLASS ALLOC_COMMENT BYTES CHUNKS
----- ------------------------ ---------- ----------
HEAP0perm permanentmemor *1898642464 *474659
HEAP0free freememory 26531224 473772
HEAP0freeablkksfbc:hash1 4872 96
HEAP0freeablkgltbtab 912 6

old10: KSMCHDS=hextoraw('&v_curheaps_kglobhd4')
new10: KSMCHDS=hextoraw('00')

norowsselected

old10: KSMCHDS=hextoraw('&v_curheaps_kglobhd6')
new10: KSMCHDS=hextoraw('00')

norowsselected

另外该问题只发生在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 9999999
col curheaps_size1 heading SIZE1 for 9999999
col curheaps_size2 heading SIZE2 for 9999999
col curheaps_size3 heading SIZE3 for 9999999
col curheaps_size4 heading SIZE4 for 9999999
col curheaps_size5 heading SIZE5 for 9999999
col curheaps_size6 heading SIZE6 for 9999999
col curheaps_size7 heading SIZE7 for 9999999

col KGLOBHD0 new_value v_curheaps_kglobhd0 print
col KGLOBHD1 new_value v_curheaps_kglobhd1 noprint
col KGLOBHD2 new_value v_curheaps_kglobhd2 noprint
col KGLOBHD3 new_value v_curheaps_kglobhd3 noprint
col KGLOBHD4 new_value v_curheaps_kglobhd4 print
col KGLOBHD5 new_value v_curheaps_kglobhd5 noprint
col KGLOBHD6 new_value v_curheaps_kglobhd6 print
col KGLOBHD7 new_value v_curheaps_kglobhd7 noprint


select 
	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 STATUS
from 
	X$KGLOB
--	X$KGLCURSOR_CHILD
where
	KGLNAHSH in (&1)
and	KGLOBT09 like ('&2')
order by
        KGLOBT09 ASC
/

-- Cursor data block summary
select 
   'HEAP0'        heap
  , ksmchcls      class
  , ksmchcom      alloc_comment
  , sum(ksmchsiz) bytes
  , count(*)      chunks
from 
    x$ksmhp
where 
    KSMCHDS = hextoraw('&v_curheaps_kglobhd0')
group by
   'HEAP0'
  , ksmchcls
  , ksmchcom
order by
    sum(ksmchsiz) desc
/

select 
   'HEAP4'        heap
  , ksmchcls      class
  , ksmchcom      alloc_comment
  , sum(ksmchsiz) bytes
  , count(*)      chunks
from 
    x$ksmhp
where 
    KSMCHDS = hextoraw('&v_curheaps_kglobhd4')
group by
   'HEAP6'
  , ksmchcls
  , ksmchcom
order by
    sum(ksmchsiz) desc
/



select 
   'HEAP6'        heap
  , ksmchcls      class
  , ksmchcom      alloc_comment
  , sum(ksmchsiz) bytes
  , count(*)      chunks
from 
    x$ksmhp
where 
    KSMCHDS = hextoraw('&v_curheaps_kglobhd6')
group by
   'HEAP0'
  , ksmchcls
  , ksmchcom
order 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: [e-mail]

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表空间和数据文件的关系，否则拒绝申请