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

From: HansP <Hans-Peter.Sloot_at_atosorigin.com>
Date: Fri, 29 Jan 2010 08:13:28 -0800 (PST)
Message-ID: <f1675572-6391-4dc1-a34e-6625c9a74d23_at_l19g2000yqb.googlegroups.com>



On 29 jan, 17:02, HansP <Hans-Peter.Sl..._at_atosorigin.com> wrote:
> On 28 jan, 19:06, HansP <Hans-Peter.Sl..._at_atosorigin.com> wrote:
>
>
>
>
>
> > On 27 jan, 23:26, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> > wrote:
>
> > > "HansP" <Hans-Peter.Sl..._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 Lewishttp://jonathanlewis.wordpress.com-Tekstuit oorspronkelijk bericht niet weergeven -
>
> > > - Tekst uit oorspronkelijk bericht weergeven -
>
> > I managed to reproduce with C# using
>
> >                 OdbcConnection DbConnection = new OdbcConnection
> > ("Driver={Microsoft ODBC for
> > Oracle};Server=TMI_SBTA;UID=xx;PWD=yyyy;");
> >                DbConnection.Open();
> >            DbCommand.CommandText = "SELECT /* 345678  */ * FROM CAT
> > where table_name = ?";
>
> >             DbCommand.Parameters.Add("_at_TableName", OdbcType.VarChar,
> > 4);
> >             DbCommand.Parameters["_at_TableName"].Value = "NICO";
>
> >             DbCommand.Prepare();
>
> >             OdbcDataReader DbReader = DbCommand.ExecuteReader();
>
> > I see 2 times the parsing in 10053 trace.
> > But now both times without bind variables.
>
> > regards Hans-Peter- Tekst uit oorspronkelijk bericht niet weergeven -
>
> > - Tekst uit oorspronkelijk bericht weergeven -
>
> Hi all,
>
> Contrary to what I said earlier I have to say that it dit NOT
> reproduce.
> I used 3 odbc driver , Microsoft's, Oracle's and Datadirect's.
> Apparently bind variable peeking can not be done with Microsofts
> (2.575.1132.00 version)
>
> Regards HansP- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

For those who want to know how to use the DataDirect ODBC driver: OdbcConnection DbConnection = new OdbcConnection("Driver={DataDirect 6.0 Oracle};DSN=DFASDF;logonid=xxx;password=yyy;Servername=DFASDF"); Received on Fri Jan 29 2010 - 10:13:28 CST

Original text of this message