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 01:24:33 -0800 (PST)
Message-ID: <0973fbd3-1886-483d-b904-b004a60bd601_at_b10g2000yqa.googlegroups.com>
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- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -
Date: Thu, 28 Jan 2010 01:24:33 -0800 (PST)
Message-ID: <0973fbd3-1886-483d-b904-b004a60bd601_at_b10g2000yqa.googlegroups.com>
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- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -
But would, if that was the case, a 10046 trace show 2 parses or just
1?
I did a combined trace of 10046 and 10053.
I used Christian Antognini's tvdxtat.
It only shows 1 parse
Database Call Statistics with Recursive Statements
Call Count Misses CPU [s] Elapsed [s] PIO [b] LIO [b] Consistent [b] Current [b] Rows Parse 1 1 0.001 0.001 0 0 0 0 0 Execute 1 1 0.024 0.023 0 0 0 0 0 Fetch 1 0 0.000 0.000 0 16 16 0 1 Total 3 2 0.025 0.024 0 16 16 0 1
regards HansP Received on Thu Jan 28 2010 - 03:24:33 CST