Re: execution plan and poor statistics
From: joel garry <joel-garry_at_home.com>
Date: Mon, 11 Apr 2011 16:00:02 -0700 (PDT)
Message-ID: <f4c89b8a-d8b3-4dce-83a4-ab69cb68eb04_at_27g2000yqv.googlegroups.com>
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
Date: Mon, 11 Apr 2011 16:00:02 -0700 (PDT)
Message-ID: <f4c89b8a-d8b3-4dce-83a4-ab69cb68eb04_at_27g2000yqv.googlegroups.com>
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-Oracle-user-groups-conference-special-reportReceived on Mon Apr 11 2011 - 18:00:02 CDT