Re: Query execution question
Date: Wed, 16 Sep 2009 14:02:30 -0500
Message-Id: <D81BBE62-C74D-4CDC-8D06-28FE5B7E0B47_at_enkitec.com>
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 plan
>
> Regards,
> 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-lReceived on Wed Sep 16 2009 - 14:02:30 CDT