Re: 1.7GB SHARABLE_MEM used by single SQL

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 17 Jun 2011 02:38:33 +0300
Message-ID: <BANLkTik7djwT8m=Vdp8oeHgeV0=v-YnaZA_at_mail.gmail.com>

Looks like a bug indeed ... I've once troubleshooted something similar...

Check this:

Bug 10082277 - Excessive allocation in PCUR heap of "kkscsAddChildNo" (ORA-4031) (Doc ID 10082277.8)
https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=10082277.8&h=Y

Note that this bug description mentions the detailed permanent memory allocation breakdown (event 10235 level 65536), but think twice before enabling it (even if support recommends to) as there are a few hang/spin bugs related to setting it...

--
Tanel Poder
http://blog.tanelpoder.com


On Thu, Jun 16, 2011 at 6:42 PM, Eagle Fan <eagle.f_at_gmail.com> wrote:



> Hi:

>

> Thanks for that information.

>

> I run that script and found that the biggest one was the parent cursor:

>

> SQL> _at_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

>

>

>


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 16 2011 - 18:38:33 CDT