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

Hegyvari, you want to use bind variables everywhere you can because of the side effects on the performance of the library cache from failing to use them. This does mean you need to use hints sometimes to help the optimizer. Now since the code is being generated it would appear you do not have an option to change it away from using bind variables, which are not workint well in this case to using constants which are working well probably due the presence of histograms.

Have you looked at the outline feature. It should allow you to add a hint "dynamically" to the SQL statement every time it runs.

 Version 9+ offers bind variable peeking which may or may not help in the CBO recognizing the usefulness of the index. There are a few bugs with the feature which sometime prevent the desired performance benefit of the feature though the bugs do not result in any runtime errors stopping the SQL. You just do reparse the SQL or do not get the plan you expected.

HTH -- Mark D Powell -- Received on Thu Sep 08 2005 - 09:33:09 CDT