Re: Query execution question
Date: Wed, 16 Sep 2009 14:02:30 -0500
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.
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 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.