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

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 27 Jan 2010 12:02:28 -0800 (PST)
Message-ID: <70f2cfd8-e0f9-4454-a435-2d14cff0998e_at_33g2000yqo.googlegroups.com>

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 Received on Wed Jan 27 2010 - 14:02:28 CST