A table is filled with a lot of data (about 100.000 entries). Data is
processed and rows are deleted until table is empty. Problem: table
statistics are gathered periodically, often when this table is nearly
empty. The effect: the optimizer chooses full table scan, even if the
table is filled.

The database admin periodically updates all table statistics. The update
starts at a fixed time.

questions:
1. The above example preferes indexed access. How can oracle 11g be
advised to always use the index? The used sql is fix, the existing
application should not be changed.

2. Assuming the above example uses different times for operations. We do
not know, when table is full or empty. How can oracle be advised to use
the index.

I think the origin of this problem is the cost based optimizer. The rule
based optimizer would always use the indexed access path, wouldn't it?

markus

On Apr 11, 1:53*pm, Ceeper <n...@m.e> wrote:
The rule based optimizer is going away, Oracle has been saying "don't
use it" for decades . There are a number of ways to deal with this,
depending on your version. One way is to get the statistics at a time
when they happen to elicit correct results from the cbo, and lock
them. This is easily done mindless GUI with EM on 10g.

And be sure you understand all the ways of your data being accessed,
and that sometime it really is better to do a full table scan.

Here are some hints about posting here: http://dbaoracle.net/readme-cdos.htm#subj12

jg
--
@home.com is bogus.
http://searchoracle.techtarget.com/n...special-report

On Apr 11, 7:00*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

This is an easy fix. Stop collecting statistics on the table. - with
a caveat.

1) while the table is full - gather statistics - do this one time
2) save these statistics somewhere
3) when your table is full again, restore those statistics.
4) If you never run stats against it again, then it is unlikely that
you will collect stats while the table is empty thereby averting your
problem.

While rules based is still there, there is a reason is stated as
deprecated. That being said, it may never go away as there is a lot of
system-level processing that relies very heavily on rules-based - or
at least it did in the past.

On Apr 11, 7:00*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
Markus, you failed to list your full database version but JG is right,
if you can, collect the statistics when the table is near its larger
size and lock them in place. If you are on an earlier release and
cannot lock the statistics then remove the table from the statistics
gathering process. If you cannot do this then consider using hints in
the SQL to lock in the 'good' plan so the change in the statistics
does not result in a change in the plan.

HTH -- Mark D Powell --

"Ceeper" <nosp@m.e> wrote

Since you've mentioned 11g and fixed SQL - use SQL baselines.

Use dbms_spm.load_plans_from_cursor_cache() to capture an SQL Baseline for
each of the critical SQL statements when good plans appear, enable them,
and that's the job done. There's an example of a method here (
http://jonathanlewis.wordpress.com/2...ake-baselines/ ) that
doesn't quite match your position - but I think a couple of the comments
should be relevant.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

"onedbguru" <onedbguru (AT) yahoo (DOT) com> wrote

This is an easy fix. Stop collecting statistics on the table. - with
a caveat.

1) while the table is full - gather statistics - do this one time
2) save these statistics somewhere
3) when your table is full again, restore those statistics.
4) If you never run stats against it again, then it is unlikely that
you will collect stats while the table is empty thereby averting your
problem.


Doesn't necessarily work - sometimes you have to keep updating the states
if you want the plans to stay the same.
This is a side effect of the "linear decay" algorithm that the optimizer
uses to pro-rate statistics when the query predicates move outside the
low/high column values. If the user has time-based (or sequence-based)
data then there may be cases where (in the absence of hinted paths) he will
see execution plans change as time passes.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com