Re: analyze worked better than gather stats
- From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- To: John.Fedock@xxxxxxxxxxxx
- Date: Fri, 31 Oct 2008 16:46:36 -0700
If event_dt is a date datatype, then you should be casting it as a
date with either TO_DATE or the ANSI literal DATE.
Is your table partitioned and are you getting pruning if so?
If the row estimate was 800K of 70M , the question that needs
answering is how many rows are there really? You mention that a
estimate of 7K gives you the desired plan (index), but is 7K closer to
the actual number of rows or it just gives the plan you want?
I might suggest Metalink Note 31412.1 - Select to show Optimizer
Statistics for CBO, as a starting point.
A 70M row "busy" table with 11 indexes would be a red flag for me, but
I'm sure you have your reasons.
This may be of use as well:
http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/
On Fri, Oct 31, 2008 at 1:08 PM, Fedock, John (KAM.RIC)
<John.Fedock@xxxxxxxxxxxx> wrote:
> Oracle 10.2.0.3 on HP-UX 11i.
> I have a large, busy table. It is 70,000,000+ rows and 1.3GB in size.
> The gather stats job runs automatically as needed.
> There are 11 indexes on this table. One the indexes has as its first
> column, a date column (named event_dt).
> When doing a simple query such as:
> Select * from TABLE where event_dt > '27-OCT-2008' performs a full table
> scan. The explain_plan estimates that 800,000+ rows is to be returned.
> I then manually ran a gather stats as follows:
> exec dbms_stats.gather_table_stats ('XXX','XXXXXX', method_opt => 'FOR ALL
> COLUMNS SIZE 1', degree => dbms_stats.auto_degree, CASCADE => TRUE);
> It did not help. Out of desperation, I ran an old fashioned "analyze table
> XXX estimate statistics;"
> This ran very quickly and corrected the issue, as the explain_plan is now
> correct (estimated rows is 7,000) and it runs with the index.
> So, I cannot figure out why the old analyze worked, where the new
> gather_stats did not.
>
> Can anyone offer any advice on this?
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
