Re: Guidelines for avoid Bind Variable Peeking behavior

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.
Actually I think that the whole approach is basically flawed. in that the optimizer has to run a query badly at least once (and often many times) before it recognizes that it needs to create multiple cursors with different plans. This is fine for systems where the performance is not really critical, or the swings between plans is not that dramatic. But if you have a batch job with a query that runs in 10 minutes with the "good" plan and 10 hours with the "bad" plan, you probably can't afford this type of approach.
Also, it doesn't appear that the information is persisted. That is, if a statement gets flushed from the shared pool, Oracle must "re-learn" which values should go with which plans.
So, it looks to me like the best approach remains to use literals where you have skewed data and need more than one plan.
These comments refer to a system that's not using SQL Plan Management (i.e. no baselines). By the way, ACS is turned on by default and SPM is off by default (sort of - no baselines will be created unless you do something). 


Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com






On Jun 1, 2009, at 11:09 AM, Allen, Brandon wrote:


Roberto,
I agree with some of the other ideas about focusing on the specific business-critical statements with high response times (method-r), but it is evident from your email that you’re looking for a quick fix, or at least temporary workaround to get the system running better ASAP, so I would also suggest testing with _optim_peek_user_binds=false. I have seen in the past that SAP suggested this as a standard configuration in their published documentation, and I know some others that also use it with other large ERP applications where the number of SQL statements is enormous and you don’t have the flexibility to change them to use literals. I personally have not used it since in my case, I’ve always found only a handful of statements that needed to be stabilized after upgrading to 9i+ and I did that via stored outlines, but I could see some situations where disabling the bind variable peeking instance-wide might be the best solution, at least for the short term.
Someone also suggested using SQL Profiles, which is a good idea, but keep in mind that’s only available if you have licenses for the Diagnostics & Tuning Packs.
Another option is to upgrade to 11g and take advantage of the new SQL Plan Management features: 

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/sqlplsql.htm#CNCPT1917
I haven’t had a chance to upgrade any of my systems to 11g yet, so I have no hands-on experience with it, but from the documentation it sounds like it could be the answer we’ve all been looking for with these bind-variable peeking problems.
Anyone else out there have much real life experience with SQL Plan Management? Is it as great as it sounds? Any tips, warnings, etc? 

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.

Other related posts: