I totally agree with Brandon's comments (except maybe the 11g one - I just haven't found ACS that helpful, so far anyway). If it's just a single statement that's giving you grief, a SQL Profile may be a quick fix. Nothing's perfect though, so you'll need to test it to make sure it does what you expect. If there are a lot of statements that are suffering from this issue, SQL Profiles can become cumbersome. I have to mention that after spending a couple of months working with an SAP system (which as recommended by SAP, has _optim_peek_user_binds set to false) that I am starting to think this is actually a pretty good approach for systems that would otherwise have a lot of issues with bind variable peeking. The systems I've been working with works remarkably well without that added "feature". I know it's a "hidden" parameter, so please don't slam me too hard, but as I said, after a couple of months, I'm starting to think it's not such a bad idea. Certainly if I was the vendor of an app such as SAP I would prefer this approach. Feel free to flame away.
Kerry Osborne Enkitec blog: kerryosborne.oracle-guy.com On Sep 16, 2009, at 12:43 PM, Allen, Brandon wrote:
Metalink 387394.1 also has a good description of the problem. If the query is just coming from a perl script, your best bet might be to change it to use literals instead of bind variables. Other possible solutions are:· Remove histograms on the involved tables (gather stats with method_opt=>’for all columns size 1’)· Stored outlines · SQL Profiles (10g+ if you have Tuning Pack license)· Upgrade to 11g and see if Adaptive Cursor Sharing fixes it or try using SQL Plan Management to preserve the good planRegards, BrandonPrivileged/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.
