Re: 10053 shows Siebel parsing each statement 2 times : without and with bind variable

From: HansP <Hans-Peter.Sloot_at_atosorigin.com>
Date: Thu, 28 Jan 2010 00:35:34 -0800 (PST)
Message-ID: <dd5c919d-921e-4564-853c-a2fd23aa4e87_at_r24g2000yqd.googlegroups.com>

On 27 jan, 21:02, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Jan 27, 8:38 pm, HansP <Hans-Peter.Sl..._at_atosorigin.com> wrote:
>
>
>
>
>
> > Hi,
> > Oracle version 10.2.0.4
> > OS Linux 64 bit
>
> > I am investigating performance issue's on Siebel.
> > I traced a session with the 10053 event to see the optimizer
> > decisions.
>
> > It struck me too see that each statement appears 2 times in the trace.
> > Both times with the same sql_id.
> > Furthermore the first parse does not show bind variables but the
> > second does.
>
> > Does someone now whether this may be cause by a bug?
> > Or is this a Siebel issue?
>
> > Further below the Peeking part of the trace.
>
> > {code}
> > *******************************************
> > Peeked values of the binds in SQL statement
> > *******************************************
> > kkscoacd
> > Bind#0
> > oacdty=01 mxl=2000(00) mxlc=00 mal=00 scl=00 pre=00
> > oacflg=00 fl2=0020 frm=01 csi=873 siz=2000 off=0
> > No bind buffers allocate
>
> > *******************************************
> > Peeked values of the binds in SQL statement
> > *******************************************
> > kxscoacd
> > Bind#0
> > oacdty=01 mxl=128(60) mxlc=15 mal=00 scl=00 pre=00
> > oacflg=00 fl2=1000010 frm=01 csi=873 siz=128 off=0
> > kxsbbbfp=2abb2fab5810 bln=128 avl=06 flg=05
> > value="0-R9NH
>
> > regards HP
>
> This SQL is invoked from [possibly anonymous] PL/SQL, right? I think
> the first bind variable dump comes from the compilation layer (KK) and
> the second is from the execution layer (KX). Obviously, at compile
> time no bind variables are available (no bind buffers allocated.) When
> PL/SQL is actually executed and the query is invoked from it,
> execution layer peeks at actual bound values and adjusts the compile
> time assumptions.
>
> Don't think it's a bug. Neither it is a Siebel issue. Just the way it
> works.
>
> Regards,
>    Vladimir M. Zakharychev
>    N-Networks, makers of Dynamic PSP(tm)
>    http://www.dynamicpsp.com- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Hi Vladimir,

No the SQL is not invoked from PL/SQL.
It is an online user session.

Thanks HansP Received on Thu Jan 28 2010 - 02:35:34 CST