Re: Guidelines for avoid Bind Variable Peeking behavior
Date: Sun, 31 May 2009 11:29:46 -0500
I agree with everything that Stephane said. Find the statements that are the critical ones (most elapsed time) and fix those. If they are flipping plans due to bind variable peeking, use literals. Here are links to a couple of scripts to help identify if you have plans that are flipping and which ones have the most variance due to different plans. (you may want to modify these to look at a single instance at a time if you are using RAC)
unstable_plans.sql awr_plan_stats.sql awr_plan_change.sql
If you need a quick fix on a few statements, SQL Profiles can be helpful as a temporary fix while you work out a longer term fix. There are posts on my blog and Randolf Geist's blog with scripts.
On May 31, 2009, at 9:46 AM, Stephane Faroult wrote:
> The best way to avoid bind variable peeking issues is to bind
> variables that change a lot, and hard-code values that don't change
> often between calls, especially when the distribution of these
> values is
> far from uniform ...
> I don't think that hints are the best solution; for one thing, I'm
> not very far from thinking that hints are intrinsically evil ... But
> your particular case, if the plans change (and given the sense of
> urgency carried out by your email it doesn't look like the odd, once
> a while, plan change that wreaks havoc) it's that the optimizer
> that it has to change. With hints you are guaranteed to be
> suboptimal in
> some cases; perhaps less blatantly suboptimal than now, but suboptimal
> nevertheless. And you are mortgaging the future. Besides, your "lots
> procedures with lots of statements" makes me fear that you have other
> problems pending than the more obvious ones you have now ...
> There are things I don't really understand here. I assume, possibly
> wrongly, that by 'procedure' you mean 'PL/SQL procedure'. Unless you
> build your queries on the fly and EXECUTE IMMEDIATE them, PL/SQL
> shouldn't be a problem.
> If removing the histograms made the situation worse, it's probably
> some stuff is properly coded in your program and required histograms.
> If I were you, I wouldn't look for an universal solution - in my
> experience, even in a complex application, problems are usually fairly
> localized to a handful of queries. Why don't you start by identifying
> queries that take the most elapsed time, those that take the most CPU
> and perform the most logical I/Os (very often they are the same ones)?
> Statspack/ADDM/V$SQLSTATS can tell you that fairly easily.
> Remember the 80/20 (Pareto) rule? I have always found it to be
> closer to
> 90/10 in the case of Oracle.
> You are much more likely to get results fast by taking a couple of
> rewriting those queries in a way that makes sense to the optimizer
> if you have, God forbid, to add a hint) than by randomly trying
> that risk adversely affect the whole program.
> S Faroult
> Roberto Veiga wrote:
>> I am facing a lot of problems with bind variables because the access
>> plan is changing and a critical performance problem occurs.(I have
>> already posted a question "Histogram worthwhile" here at Oracle-l). I
>> am suffering the "bind variable peeking" behavior.
>> My customer is very disapointed about this "feature" of Oracle and he
>> is asking me what to do to avoid this behavior.
>> We have already tried to remove histograms and the situation become
>> I have proposed him to focus on the most critical process and put
>> hints to freeze the plan.
>> But the processes work with a lot of procedures with a lot of
>> statements. So I need to define a method to focus only in the queries
>> with most change to have this problem.
>> I can use Stored Outlines (Plan Stability) but I have a *lot of*
>> queries and I need a fast way to configure those queries.
>> I am thinking use Sql Profiles but I need a fast way to use this with
>> a lot of queries and I really dont know if this approach is going to
>> resolve the changing plan problem.
>> I am thinking using this points to focus only in a few potential
>> problem queries:
>> -Statements with Bind Variables
>> -Columns with Skewed distribution
>> -Statements with more than 5 tables join
>> -Tables with many rows (500k or more)
>> -Access plan using Hash Join
>> Is this a good approach to identify queries that can have the plan
>> We are having a great pressure to minimize this situation.
>> Thanks in advance,
>> Roberto Veiga
>> Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10
>> - Celebridades
>> - Música
>> - Esportes
> ghSea Ltd <http://www.roughsea.com>