Re: 1.7GB SHARABLE_MEM used by single SQL

I also noticed that change. And I noticed that there was a number of bind mismatch in reason column.

Like Bind mismatch(20), Bind mismatch(22). At first I thought it could be data type, but it wasn't.

Do you know the number's meanings? Is there any doc mention this?

Thanks.

On Fri, Jun 17, 2011 at 1:38 AM, Grzegorz Goryszewski <grzegorzof_at_interia.pl
> wrote:

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

-- 
Eagle Fan (www.dbafan.com)

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