RE: Guidelines for avoid Bind Variable Peeking behavior
Date: Mon, 1 Jun 2009 09:09:46 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E1126D11E85_at_ONEWS06.oneneck.corp>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 01 2009 - 11:09:46 CDT