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

From: joel garry <joel-garry_at_home.com>
Date: Wed, 27 Jan 2010 10:51:26 -0800 (PST)
Message-ID: <caac91e2-897e-4f47-86d0-d9522ede01cb_at_y12g2000yqh.googlegroups.com>

On Jan 27, 9:38 am, 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

Sorry for the vague answer as I don't have time to recall where I've seen this (probably on one of the guru-blogs, Kerry Osborne, Jonathan Lewis or Tanel Poder), but I think you might be seeing the results of profiling or some cardinality adjustment by the optimizer, as it tries to decide whether to use peeked cardinalities. Which of course is easily bogosified as it never has been a well-thought-out feature for 10g.

jg

--
_at_home.com is bogus.
Apple announces iPad, but it only works at certain times of the month.

Received on Wed Jan 27 2010 - 12:51:26 CST