| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PreparedStatement Performance
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-histograms/
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:
optimizer_features_enable = 10.1.0.4 cursor_sharing = force...
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.
Received on Thu Mar 01 2007 - 10:21:16 CST
 |
 |