Re: using bind variables makes the optimizer choose a bad plan

Using bind variables - less parse time, less stress on shared pool, more scalable, may produce less than optimal execution plan Not using bind variable - may produce better execution plan, more parse time, more stress on shared pool, less scalable

This is a very simplistic comparison, but it illustrates that there is a trade off to using bind variables. For most applications/statements, using bind variables will produce acceptable performance. For a few applications/statements, bind variables might cause the optimizer to choose a bad plan. It is the responsibility of the designer/developer/dba to identify the exceptions and find the appropriate solution.

There is not an absolute "Bind Variables are always GOOD|BAD" rule. They are 'usually' a good thing, but not always.

Regards,
Dan Fink Received on Fri Sep 09 2005 - 15:15:52 CDT