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




from the blog of eagle, the original link address is as follows: http://www.dbafan.com/blog/?p=495

11gR2 hasn't been studied yet, repost it in case it happens later.


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

On the 11.2.0.2 database, we encountered an oracle 11g bug 10082277 Overs 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).


the problem manifests itself in our system

1. A single SQL takes up a lot of shared pool memory, in this case a single SQL takes up 1.7GB of sharedpool memory

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

VERSION_COUNT SHARABLE_MEM
------------- ------------
96 1888704961

ON A 10.2.0.4 DATABASE, THE SAME SQL USES ONLY ABOUT 4M OF MEMORY

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

VERSION_COUNT SHARABLE_MEM
------------- ------------
214 4216097


2. most of the memory is occupied by cursor (cursor id 65535).

With Tanel's script curheaps.sql you can see the size of each 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


in addition, the problem only occurs after the jdbc driver upgrade to 11g, jdbcdriver is 10g when there is no problem, estimated to have something to do with shardcursor sharing.

SQL's growing cur parentsor can drain the memory of the shared pool on the one hand, and can lead to a large number of parse-related wait times, such as "cursor: mutex S", if the hard parse is very time consuming.

Oracle has relevant patches that can be downloaded and resolved after hitting the patch.


curheaps.sql

  1. --------------------------------------------------------------------------------
  2. --
  3. -- File name: curheaps.sql
  4. -- Purpose: Show main cursor data block heap sizes and their contents
  5. -- (heap0 and heap6)
  6. --
  7. -- Author: Tanel Poder
  8. -- Copyright: (c) http://www.tanelpoder.com
  9. --
  10. -- Usage: @curheaps <hash_value> <child#>
  11. --
  12. -- @curheaps 942515969 % -- shows a summary of cursor heaps
  13. -- @curheaps 942515969 0 -- shows detail for child cursor 0
  14. --
  15. -- Other: "Child" cursor# 65535 is actually the parent cursor
  16. --
  17. --------------------------------------------------------------------------------
  18. col curheaps_size0 heading SIZE0 for 9999999
  19. col curheaps_size1 heading SIZE1 for 9999999
  20. col curheaps_size2 heading SIZE2 for 9999999
  21. col curheaps_size3 heading SIZE3 for 9999999
  22. col curheaps_size4 heading SIZE4 for 9999999
  23. col curheaps_size5 heading SIZE5 for 9999999
  24. col curheaps_size6 heading SIZE6 for 9999999
  25. col curheaps_size7 heading SIZE7 for 9999999
  26. col KGLOBHD0 new_value v_curheaps_kglobhd0 print
  27. col KGLOBHD1 new_value v_curheaps_kglobhd1 noprint
  28. col KGLOBHD2 new_value v_curheaps_kglobhd2 noprint
  29. col KGLOBHD3 new_value v_curheaps_kglobhd3 noprint
  30. col KGLOBHD4 new_value v_curheaps_kglobhd4 print
  31. col KGLOBHD5 new_value v_curheaps_kglobhd5 noprint
  32. col KGLOBHD6 new_value v_curheaps_kglobhd6 print
  33. col KGLOBHD7 new_value v_curheaps_kglobhd7 noprint
  34. select
  35. KGLNAHSH,
  36. KGLHDPAR,
  37. kglobt09 CHILD#,
  38. KGLHDADR,
  39. KGLOBHD0, KGLOBHS0 curheaps_size0,
  40. KGLOBHD1, KGLOBHS1 curheaps_size1,
  41. KGLOBHD2, KGLOBHS2 curheaps_size2,
  42. KGLOBHD3, KGLOBHS3 curheaps_size3,
  43. KGLOBHD4, KGLOBHS4 curheaps_size4,
  44. KGLOBHD5, KGLOBHS5 curheaps_size5,
  45. KGLOBHD6, KGLOBHS6 curheaps_size6,
  46. KGLOBHD7, KGLOBHS7 curheaps_size7,
  47. -- KGLOBT00 CTXSTAT,
  48. KGLOBSTA STATUS
  49. from
  50. X$KGLOB
  51. -- X$KGLCURSOR_CHILD
  52. where
  53. KGLNAHSH in (&1)
  54. and KGLOBT09 like ('&2')
  55. order by
  56. KGLOBT09 ASC
  57. /
  58. -- Cursor data block summary
  59. select
  60. 'HEAP0' heap
  61. , ksmchcls class
  62. , ksmchcom alloc_comment
  63. , sum(ksmchsiz) bytes
  64. , count(*) chunks
  65. from
  66. x$ksmhp
  67. where
  68. KSMCHDS = hextoraw('&v_curheaps_kglobhd0')
  69. group by
  70. 'HEAP0'
  71. , ksmchcls
  72. , ksmchcom
  73. order by
  74. sum(ksmchsiz) desc
  75. /
  76. select
  77. 'HEAP4' heap
  78. , ksmchcls class
  79. , ksmchcom alloc_comment
  80. , sum(ksmchsiz) bytes
  81. , count(*) chunks
  82. from
  83. x$ksmhp
  84. where
  85. KSMCHDS = hextoraw('&v_curheaps_kglobhd4')
  86. group by
  87. 'HEAP6'
  88. , ksmchcls
  89. , ksmchcom
  90. order by
  91. sum(ksmchsiz) desc
  92. /
  93. select
  94. 'HEAP6' heap
  95. , ksmchcls class
  96. , ksmchcom alloc_comment
  97. , sum(ksmchsiz) bytes
  98. , count(*) chunks
  99. from
  100. x$ksmhp
  101. where
  102. KSMCHDS = hextoraw('&v_curheaps_kglobhd6')
  103. group by
  104. 'HEAP0'
  105. , ksmchcls
  106. , ksmchcom
  107. order by
  108. sum(ksmchsiz) desc
  109. /
  110. -- Cursor data block details
  111. -- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd0');
  112. -- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd6');


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

QQ:492913789

Email:[email protected]

Blog: http://www.cndba.cn/dave

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

DBA1 GROUP: 62697716 (FULL); DBA2 GROUP: 62697977 (FULL) DBA3 GROUP: 62697850 (FULL)

DBA SUPERGROUP: 63306533 (FULL); DBA4 GROUP: 83829929 (FULL) DBA5 GROUP: 142216823 (FULL)

DBA6 GROUP: 158654907 (FULL) DBA7 GROUP: 69087192 (FULL) DBA8 GROUP: 172855474

DBA SUPERGROUP 2: 151508914 DBA9 GROUP: 102954821 CHAT GROUP: 40132017 (FULL)

--Adding groups is required to explain the relationship between Oracle table space and data files in the notes, otherwise the request will be rejected