RE: Guidelines for avoid Bind Variable Peeking behavior
Date: Wed, 10 Jun 2009 16:04:56 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E1126E73444_at_ONEWS06.oneneck.corp>
Kerry,
That's a very interesting blog entry. I searched the internet for any other mention of the 14 bind variable limit and couldn't find any so it appears you're the first to discover it - good work!
Did you catch the mention at the bottom of the optimizermagic blog about adding a hint in future releases to inform the CBO that a query should be bind aware so that it will know up front without having to go through the pain of figuring it out?
This reminded me that we had some discussion along the same lines on this list back in 2006:
http://www.freelists.org/post/oracle-l/How-to-cope-with-nasty-side-effects-of-bind-variable-peeking,4 http://www.freelists.org/post/oracle-l/How-to-cope-with-nasty-side-effects-of-bind-variable-peeking,5
I'm looking forward to digging into Adaptive Cursor Sharing and SQL Plan Management a lot more once I wrap up this Oracle Application Server project I've been working on and I get a chance to do an 11g upgrade.
Thanks,
Brandon
From: Kerry Osborne [mailto:kerry.osborne_at_enkitec.com]
We've been supporting a production 11.1.0.7 cluster for some time and just recently migrated a 10g system (which had bind variable peeking issues) to 11.1.0.7. I just did a post on my blog this morning based on my observations of this migrated system. The bottom line for this system was that ACS didn't really give us the results we were hoping for.
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 Jun 10 2009 - 18:04:56 CDT