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
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:
