Re: 1.7GB SHARABLE_MEM used by single SQL

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> @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@xxxxxxxxxx
>> 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

Other related posts: