Re: execution plan and poor statistics
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 12 Apr 2011 07:53:32 -0700 (PDT)
Message-ID: <1a2c50a6-b23e-499e-9372-4973165d9fc3_at_p13g2000yqh.googlegroups.com>
On Apr 11, 7:00 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 11, 1:53 pm, Ceeper <n..._at_m.e> wrote:
>
>
>
>
>
> > 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
>
> 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
> --
> _at_home.com is bogus.http://searchoracle.techtarget.com/news/2240033743/Collaborate-2011-O...- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 12 Apr 2011 07:53:32 -0700 (PDT)
Message-ID: <1a2c50a6-b23e-499e-9372-4973165d9fc3_at_p13g2000yqh.googlegroups.com>
On Apr 11, 7:00 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 11, 1:53 pm, Ceeper <n..._at_m.e> wrote:
>
>
>
>
>
> > 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
>
> 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
> --
> _at_home.com is bogus.http://searchoracle.techtarget.com/news/2240033743/Collaborate-2011-O...- Hide quoted text -
>
> - Show quoted text -
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 -- Received on Tue Apr 12 2011 - 09:53:32 CDT