Re: 1.7GB SHARABLE_MEM used by single SQL

From: Grzegorz Goryszewski <grzegorzof_at_interia.pl>
Date: Thu, 16 Jun 2011 19:38:48 +0200
Message-ID: <4DFA3FA8.8030303_at_interia.pl>



Hi,
  is that the case :
In 10g the cursors show in V$SQL_SHARED_CUROSR.BIND_MISMATCH=Y,

in 11g+ as BIND_LENGTH_UPGRADEABLE=Y

there is
|alter system set events 'immediate trace name cursordump level 16' mentioned in metalink for 11gr2 but I would recommend that without Oracle support blessing .
Moreover I'm not sure if trace would be useful for mortals :). Regards
GregG

|

On 2011-06-16 17:42, Eagle Fan 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
>
>
> 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
>>
>
>



Dzwonki MP3 na telefon. To sa prawdziwe hity! Pobierz >> http://linkint.pl/f29c2
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 16 2011 - 12:38:48 CDT

Original text of this message