RE: How to cope with nasty side effects of bind variable peeking

What if Oracle could evaluate the incoming bind variable values for a query and compare them to the bind values used at parse time for all previously cached versions of the same query and if they don't match, then the CBO peeks at the new bind variables and comes up with the best plan. If the new plan is different than the existing cached plan (quick check on plan_hash_value), then it loads it as a new child cursor, but if it's the same as the plan that was already loaded for the previous bind variables, then the new query just uses that plan. Oracle could store multiple bind_hash_values associated with a given plan and always do a comparison on new queries to determine which queries can use the existing cached plan(s) and which ones need to be optimized for a new set of variables.  

Anyone else think this would be a good idea?  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Allen, Brandon Sent: Thursday, August 24, 2006 11:52 AM

Ever since moving to 9i and 10g databases in the last couple years, the most common performance problem I see is queries that are optimized by the CBO based on one set of bind variables, but subsequently executed with a drastically different set of bind values, resulting in a terrible mismatch between the query and the execution plan . . .

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l