On Mar 1, 11:21 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 28, 3:56 am, "John.Plan..._at_googlemail.com"
>
>
>
>
>
> <John.Plan..._at_googlemail.com> wrote:
> > On 27 Feb, 14:40, "John.Plan..._at_googlemail.com"
> > > I am using Oracle 10g (10.2.0.1) on Windows (XP SP2). Within my java
> > > code I make extensive use of PreparedStatements (as recommended by
> > > just about everyone). I am experiencing odd performance whereby a
> > > query will sometimes run in around 100 ms and sometimes it takes in
> > > excess of 3 minutes. In both cases the query is exactly the same, the
> > > data is exactly the same and there is no additional load on the
> > > database (this is all running on my desktop machine).
>
> > > If anyone can shed any light onto this anomoly then I would be most
> > > grateful.
>
> > I have further information on this.
>
> > I decided to run some database tracing using event 10053 (CBO
> > tracing), in the two cases. This is a bit of a black art to me, but
> > was quite revealing. The main difference between the two cases was
> > that the fast query, when building the execution plan, used bind
> > variable peeking. My schema has statistics including histograms,
> > which appear to enable the CBO to better judge how many hits it will
> > get when performing a query. Here is an extract froim the 10053 trace
>
> > *******************************************
> > Peeked values of the binds in SQL statement
> > *******************************************
> > kkscoacd
> > Bind#0
> > oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
> > oacflg=03 fl2=1000000 frm=01 csi=873 siz=48 off=0
> > kxsbbbfp=0af369c4 bln=22 avl=02 flg=05
> > value=85
> > Bind#1
> > oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
> > oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=24
> > kxsbbbfp=0af369dc bln=22 avl=04 flg=01
> > value=28529
>
> > In the second slow case, the optimiser is building the execution plan,
> > but is not using bind value peeking. The extract from this trace is
> > as follows
>
> > *******************************************
> > Peeked values of the binds in SQL statement
> > *******************************************
> > kkscoacd
> > Bind#0
> > oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
> > oacflg=03 fl2=1000000 frm=01 csi=873 siz=48 off=0
> > No bind buffers allocated
> > Bind#1
> > oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
> > oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=24
> > No bind buffers allocated
>
> > Subsequently when calculating weightings to various excution paths,
> > the values are quite different (because with no bind variable peeking,
> > the optimiser assumes an even spread of values based on the number of
> > distinct values (NDV) for a column). In my data the spread is not
> > even.
>
> > Having looked around for details about this mysterious bind value
> > peeking, I am led to believe that the only way that peeking will not
> > occur is if you explicitly switch it off (setting
> > _optim_peek_user_binds=false). I have not done this.
>
> > Can anyone shed any light as to why I see this behaviour?
>
> > Many Thanks
>
> This website seems to suggest that you will see "No bind buffers
> allocated" when you have disabled bind variable peeking:http://blog.oracloid.com/2006/07/bind-variable-peeking-with-no-histog...
>
> While that may be true, I have seen that statement in 10053 trace
> files generated for the Centura SQLWindows developed ERP system that
> we use, as well as ad-hoc SQL statements submitted from time to time,
> even through bind variable peeking is not disabled (this is in Oracle
> 10.2.0.2).
>
> >From one of my 10053 trace files:
>
> *******************************************
> Peeked values of the binds in SQL statement
> *******************************************
> kkscoacd
> Bind#0
> oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
> oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0
> No bind buffers allocated
> Bind#1
> oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
> oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0
> No bind buffers allocated
> ***************************************
> PARAMETERS USED BY THE OPTIMIZER
> ********************************
> *************************************
> PARAMETERS WITH ALTERED VALUES
> ******************************
> optimizer_features_enable = 10.1.0.4
> cursor_sharing = force
> ...
> QUERY BLOCK TEXT
> ****************
> SELECT
> *
> FROM
> SHIPPING_LINE
> WHERE
> PACKLIST_ID=:"SYS_B_0"
> AND CUST_ORDER_ID IN (:"SYS_B_1")
>
> The SQL statement submitted to Oracle looked something like this:
> SELECT
> *
> FROM
> SHIPPING_LINE
> WHERE
> PACKLIST_ID='10101'
> AND CUST_ORDER_ID IN ('ABCDE');
>
> You will note that in the peeked bind section that I included above:
> oacdty=01 mxl=32(05)
>
> oacdty=01 indicates that a varchar2 or nvarchar2 should be expected.
> mxl=32(05) indicates that the maximum length of the bind variable is
> 32, and that the actual data length is 5.
>
> >From another SQL statement that was parsed with CURSOR_SHARING set to
>
> FORCE on the same Oracle database:
> Bind#19
> oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
> oacflg=10 fl2=0500 frm=00 csi=00 siz=24 off=0
> kxsbbbfp=5a5cb020 bln=22 avl=02 flg=09
> value=4
>
> Note that this time that oacdty=02 is included, like in your example,
> which indicates that a number should be expected, and mxl=22(02)
> indicates that the bind variable can handle a NUMBER(22) data type,
> but a NUMBER(2) was actually provided.
>
> In your case, when the SQL statement executes quickly, you will see:
> oacdty=02 mxl=22(22)
>
> And when the SQL statement executes slowly, you will see:
> oacdty=02 mxl=22(00)
>
> That seems to indicate that when you are making the initial parse
> call, you may not be supplying initial bind values for the parse.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -
Another things that I have just noticed is that you have
cursor_sharing set to force while using prepared statements. Maybe you
need the setting at the database level, but it looks like you don't
need it at the session level (for this particular type of session).
You can alter the value at the session level with: ALTER SESSION SET
cursor_sharing = exact (or similar should you need it).
Received on Thu Mar 01 2007 - 13:28:43 CST
