RE: Guidelines for avoid Bind Variable Peeking behavior

  • From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • To: "kerry.osborne@xxxxxxxxxxx" <kerry.osborne@xxxxxxxxxxx>, "sfaroult@xxxxxxxxxxxx" <sfaroult@xxxxxxxxxxxx>, "roberto_veiga@xxxxxxxxx" <roberto_veiga@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 1 Jun 2009 09:09:46 -0700


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 

Another option is to upgrade to 11g and take advantage of the new SQL Plan 
Management features:

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 

Anyone else out there have much real life experience with SQL Plan Management?  
Is it as great as it sounds?  Any tips, warnings, etc?


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: