Re: 1.7GB SHARABLE_MEM used by single SQL
Date: Thu, 16 Jun 2011 23:42:16 +0800
Message-ID: <BANLkTik6QEmnMWHpH-7ca7vqGNf6fkWzcg_at_mail.gmail.com>
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
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
How to dig into it?
The high version count is caused by bind mismatch. The SQL has several varchar4000 bind variables. On 10g database, it has 200+ versions but only uses 4MB memory.
+ Tanel to this thread.
Thanks.
On Thu, Jun 16, 2011 at 2:48 AM, Grzegorz Goryszewski <grzegorzof_at_interia.pl
> wrote:
> On 2011-06-15 17:39, Eagle Fan wrote:
> > hi:
> >
> > Is there any way to dump a cursor's sharable memory?
> >
> > When the SQL's version was not increasing, the sharable memory was
> > increasing slowly. I'm curious what caused the increasing of sharable
> > memory.
> >
>
> Hi,
> I think You can try with Tanel's curheaps.sql
> script.
>
> File name: curheaps.sql
> -- Purpose: Show main cursor data block heap sizes and their contents
> -- (heap0 and heap6)
>
> You can obtain source from
> http://files.e2sn.com/scripts/tpt_public_win.zip
>
> take few snapshots and check which heap is growing .
>
> btw what is the reason behind high version count ?
> Regards
> GG
> >> Dzwonki MP3 na telefon. To sa prawdziwe hity!
> ----------------------------------------------------------------
> Pobierz >> http://linkint.pl/f29c2
>
-- Eagle Fan (www.dbafan.com) -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 16 2011 - 10:42:16 CDT