Re: analyze worked better than gather stats

The only easy way to analyze your problem might be just to compare the 2 statistics(one by gather, one by analyze)

Can you post the table statistics of both cases?

Dion Cho

2008/11/1 Fedock, John (KAM.RIC) <John.Fedock_at_us.kline.com>

> 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?
>
>
>
> Thanks all,
>
>
>
>
>
>
>
> *John Fedock*
>
> *"K"* Line America, ISD Department
>
>
>

--
http://www.freelists.org/webpage/oracle-l