RE: Bad exectution plans due to bind variable peeking

Does Baan have any advice? For example, in certain SAP versions they explicitly recommend not enabling bind variable peeking.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Allen, Brandon Sent: Friday, June 23, 2006 11:15 AM
To: oracle-l_at_freelists.org
Subject: Bad exectution plans due to bind variable peeking

Hello,  

I'm supporting an ERP app (SSA BaanIV) that uses bind variables extensively, which is a good thing for the most part, but occasionally I'm having queries that are suffering from plan instability due to bind variable peeking. A user runs a query with a certain set of bind variable values and Oracle chooses the optimal execution plan for those bind variables - then someone runs the exact same query with wildly different bind variables, but gets stuck with the same execution plan that was optimized for the first execution's bind variables. There are 3 possible solutions/workarounds that I'm aware of:  

1. Don't use bind variables for these queries - unfortunately I can't do this - it's a canned application. We do have access to modify queries, but can't stop it from using bind variables.
2. Set _optim_peek_user_binds=false - I don't really want to do this due to the fact that it would most likely do more damage than good since these problems are the rare exception - 99%+ queries are running very well.
3. Use stored outlines to force a more consistent plan that works decently, although probably not optimally, for both sets of bind variables - this is what I've done up to now.

I'm just curious if anyone else has any ideas other than the 3 above?  

Thanks,
Brandon
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