Re: perplexing problem....

"Mark Townsend" <markbtownsend_at_home.com> wrote in message news:B7CEA22C.1456B%markbtownsend_at_home.com...
> in article 3BA8D870.FF6F32EA_at_dced.state.ak.us, Calvin Crumrine at
> Calvin_Crumrine_at_dced.state.ak.us wrote on 9/19/01 10:40 AM:
>
> > Personally, I prefer efficiency in coding/maintenance over efficiency in
RAM
> > and
> > CPU cycles and bind variables always seemed complicated to me. I find it
> > cheaper to
> > add RAM and CPU cycles than to hire another programmer. Of course it all
> > depends on
> > how you measure efficiency-I think of it in terms of budget impact. How
can I
> > get
> > the most work done with the smallest budget?
> >
> > I think bind variables are fine in stable apps, but not necessarily in
all
> > apps.
>
>
> Well - you are entitled to your own opinion, but this is sorta like buying
a
> car with manual transmission and refusing to shift out of second. The only
> time it doesn't make sense to use a bind variable is if you have
significant
> skew in an indexed column and are using histograms to alert the optimizer
to
> the fact (and even this is now largely mitigated in Oracle9i). And in
fact,
> bind variable are such a Good Thing (TM) that in later versions (8.1.6+,
> 9i), the engine will actually turn all your constants into bind variables
> for you - so NO development impact !!

Er, not quite. In 9i, Oracle finally recognises that in certain circumstances, bind variables are a disastrously bad thing. Which is why the CURSOR_SHARING parameter can now be set to 'similar'... if the use of bind variables won't negatively impact the execution plan, it will substitute in a bind variable. But if it will, it doesn't.

Which means that the Oracle engine is finally waking up to something we've all known (or should have known) long enough: where a slightly non-optimal execution plan results in an additional hour or two of execution, use of bind variables is extremely problematic (translation: bind variables in a DSS setting are a bit of a no-no).

Regards
HJR
>
Received on Wed Sep 19 2001 - 22:46:35 CDT