Re: execution plan and poor statistics
From: onedbguru <onedbguru_at_yahoo.com>
Date: Mon, 11 Apr 2011 17:42:38 -0700 (PDT)
Message-ID: <cc278a1b-de27-4353-9c46-57340c25cf90_at_r14g2000vbm.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...
Date: Mon, 11 Apr 2011 17:42:38 -0700 (PDT)
Message-ID: <cc278a1b-de27-4353-9c46-57340c25cf90_at_r14g2000vbm.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...
This is an easy fix. Stop collecting statistics on the table. - with a caveat.
- while the table is full - gather statistics - do this one time
- save these statistics somewhere
- when your table is full again, restore those statistics.
- 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. Received on Mon Apr 11 2011 - 19:42:38 CDT