John.Planque_at_googlemail.com schreef:
>
> 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
>
I'd say you have a pretty strong case for Support Services...
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Wed Feb 28 2007 - 12:52:08 CST
