Re: 1.7GB SHARABLE_MEM used by single SQL
- From: Eagle Fan <eagle.f@xxxxxxxxx>
- To: Grzegorz Goryszewski <grzegorzof@xxxxxxxxxx>
- Date: Fri, 17 Jun 2011 12:26:28 +0800
hi:
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@xxxxxxxxxx
> 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> @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
>
--
Eagle Fan (www.dbafan.com)
Other related posts: