Re: Guidelines for avoid Bind Variable Peeking behavior

    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 in 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 in a while, plan change that wreaks havoc) it's that the optimizer computes 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 of 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 that 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 days rewriting those queries in a way that makes sense to the optimizer (even if you have, God forbid, to add a hint) than by randomly trying changes that risk adversely affect the whole program.

HTH 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 worse.
>
> I have proposed him to focus on the most critical process and put some
> 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
> changed?
>
> 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
> <http://br.rd.yahoo.com/mail/taglines/mail/*http://br.maisbuscados.yahoo.com/>
> - Celebridades
> <http://br.rd.yahoo.com/mail/taglines/mail/*http://br.maisbuscados.yahoo.com/celebridades/>
> - Música
> <http://br.rd.yahoo.com/mail/taglines/mail/*http://br.maisbuscados.yahoo.com/m%C3%BAsica/>
> - Esportes
> <http://br.rd.yahoo.com/mail/taglines/mail/*http://br.maisbuscados.yahoo.com/esportes/>

ghSea Ltd <http://www.roughsea.com>

--
http://www.freelists.org/webpage/oracle-l