Re: question for the wise?

Presumably, you're talking about cases where different execution plans would be better depending on the value being bound, right? If so, then this seems like a case for hints. If you're claiming there's a .5 second hit for accessing memory and binding the value then I'd have to see some proof of that.

An interesting 9i feature is bind peeking ...

"The CBO peeks at the values of user-defined bind variables on the first invocation of a cursor. This lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals had been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values."

As good intentioned as this is, it seems screwy to me. If the first invocation of a cursor happens to have poorly representative values on the first execution then a poor plan may be imposed on all subsequent executions? Worse still, there's no way to know what's going to happen in advance, right?

Richard

FC wrote:
>
> Hi folks,
> here is a very simple question concerning good programming practice versus
> performance.
> I hope it will open up one of those neverending debates I like so much.
>
> 1.
> ===========================
> Select a,b,c from my_table
> where d = my_own_constants.XYZ
>
> say it takes 2 seconds.
>
> 2.
> ===========================
> Select a,b,c from my_table
> where d = 'XYZ'
>
> say it takes 1.5 seconds.
>
> my_own_constants is a bodyless package specification where I list all the
> constant values I need that might change in the future.
>
> Question:
> how can I increase the performance of solution 1 which is clearly easier to
> maintain but 25% slower in performance than solution 2 ?
>
> Thank you
> Flavio
Received on Thu May 09 2002 - 15:30:08 CDT