Roberto,
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>
--
//www.freelists.org/webpage/oracle-l
