admin管理员组

文章数量:1575990

在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

view plain copy to clipboard print ?
  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   
  9. --                 
  10. -- Usage:       @curheaps    
  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.   
  19. col curheaps_size0 heading SIZE0 for 9999999  
  20. col curheaps_size1 heading SIZE1 for 9999999  
  21. col curheaps_size2 heading SIZE2 for 9999999  
  22. col curheaps_size3 heading SIZE3 for 9999999  
  23. col curheaps_size4 heading SIZE4 for 9999999  
  24. col curheaps_size5 heading SIZE5 for 9999999  
  25. col curheaps_size6 heading SIZE6 for 9999999  
  26. col curheaps_size7 heading SIZE7 for 9999999  
  27.   
  28. col KGLOBHD0 new_value v_curheaps_kglobhd0 print  
  29. col KGLOBHD1 new_value v_curheaps_kglobhd1 noprint  
  30. col KGLOBHD2 new_value v_curheaps_kglobhd2 noprint  
  31. col KGLOBHD3 new_value v_curheaps_kglobhd3 noprint  
  32. col KGLOBHD4 new_value v_curheaps_kglobhd4 print  
  33. col KGLOBHD5 new_value v_curheaps_kglobhd5 noprint  
  34. col KGLOBHD6 new_value v_curheaps_kglobhd6 print  
  35. col KGLOBHD7 new_value v_curheaps_kglobhd7 noprint  
  36.   
  37.   
  38. select   
  39.     KGLNAHSH,  
  40.     KGLHDPAR,  
  41.     kglobt09 CHILD#,  
  42.     KGLHDADR,  
  43.     KGLOBHD0, KGLOBHS0 curheaps_size0,  
  44.     KGLOBHD1, KGLOBHS1 curheaps_size1,  
  45.     KGLOBHD2, KGLOBHS2 curheaps_size2,  
  46.     KGLOBHD3, KGLOBHS3 curheaps_size3,  
  47.     KGLOBHD4, KGLOBHS4 curheaps_size4,  
  48.     KGLOBHD5, KGLOBHS5 curheaps_size5,  
  49.     KGLOBHD6, KGLOBHS6 curheaps_size6,  
  50.     KGLOBHD7, KGLOBHS7 curheaps_size7,  
  51. --  KGLOBT00 CTXSTAT,   
  52.     KGLOBSTA STATUS  
  53. from   
  54.     X$KGLOB  
  55. --  X$KGLCURSOR_CHILD   
  56. where  
  57.     KGLNAHSH in (&1)  
  58. and KGLOBT09 like ('&2')  
  59. order by  
  60.         KGLOBT09 ASC  
  61. /  
  62.   
  63. -- Cursor data block summary   
  64. select   
  65.    'HEAP0'        heap  
  66.   , ksmchcls      class  
  67.   , ksmchcom      alloc_comment  
  68.   , sum(ksmchsiz) bytes  
  69.   , count(*)      chunks  
  70. from   
  71.     x$ksmhp  
  72. where   
  73.     KSMCHDS = hextoraw('&v_curheaps_kglobhd0')  
  74. group by  
  75.    'HEAP0'  
  76.   , ksmchcls  
  77.   , ksmchcom  
  78. order by  
  79.     sum(ksmchsiz) desc  
  80. /  
  81.   
  82. select   
  83.    'HEAP4'        heap  
  84.   , ksmchcls      class  
  85.   , ksmchcom      alloc_comment  
  86.   , sum(ksmchsiz) bytes  
  87.   , count(*)      chunks  
  88. from   
  89.     x$ksmhp  
  90. where   
  91.     KSMCHDS = hextoraw('&v_curheaps_kglobhd4')  
  92. group by  
  93.    'HEAP6'  
  94.   , ksmchcls  
  95.   , ksmchcom  
  96. order by  
  97.     sum(ksmchsiz) desc  
  98. /  
  99.   
  100.   
  101.   
  102. select   
  103.    'HEAP6'        heap  
  104.   , ksmchcls      class  
  105.   , ksmchcom      alloc_comment  
  106.   , sum(ksmchsiz) bytes  
  107.   , count(*)      chunks  
  108. from   
  109.     x$ksmhp  
  110. where   
  111.     KSMCHDS = hextoraw('&v_curheaps_kglobhd6')  
  112. group by  
  113.    'HEAP0'  
  114.   , ksmchcls  
  115.   , ksmchcom  
  116. order by  
  117.     sum(ksmchsiz) desc  
  118. /  
  119.   
  120.   
  121. -- Cursor data block details   
  122.   
  123. -- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd0');   -- select * from x$ksmhp where KSMCHDS = hextoraw('&v_curheaps_kglobhd6');  

原文鏈接:http://www.freelists/post/oracle-l/ORA04031-KGLH0-heap

来自 “ ITPUB博客 ” ,链接:http://blog.itpub/24849178/viewspace-714374/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub/24849178/viewspace-714374/

本文标签: excessiveallocationBugheapkkscsAddChildNo