RE: analyze worked better than gather stats

From: Baumgartel, Paul <>
Date: Fri, 31 Oct 2008 16:19:03 -0400
Message-ID: <>

This doesn't answer the question, but did you inspect the values of the various statistics columns in dba_tables and dba_indexes after both the dbms_stats and analyze runs? That information might provide some clues.  

Paul Baumgartel
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
Phone 212.538.1143  

[] On Behalf Of Fedock, John (KAM.RIC)
Sent: Friday, October 31, 2008 4:09 PM
Subject: analyze worked better than gather stats

Oracle 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  

Please access the attached hyperlink for an important electronic communications disclaimer:

Received on Fri Oct 31 2008 - 15:19:03 CDT

Original text of this message