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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 27 Jan 2010 22:26:50 -0000
Message-ID: <BZGdnUPN-dswIP3WnZ2dnUVZ8gOdnZ2d_at_bt.com>

"HansP" <Hans-Peter.Sloot_at_atosorigin.com> wrote in message news:c9828056-4e00-4af5-80b9-2d6c2a8789f0_at_e25g2000yqh.googlegroups.com...
> 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

There used to be a problem like this a few years ago relating to a layer of software between the front-end and the network connection. It might have been a version of the JDBC driver.

There was a configuration detail that specifically required the layer to send an explicit "parse-only" call to the database - which means sending only the text string, without any bind information at all - and then making the combined parse/execute call with the bind information.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Received on Wed Jan 27 2010 - 16:26:50 CST