RE: analyze worked better than gather stats

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 
CREDIT SUISSE 
Information Technology 
Prime Services Databases Americas 
One Madison Avenue 
New York, NY 10010 
USA 
Phone 212.538.1143 
paul.baumgartel@xxxxxxxxxxxxxxxxx 
www.credit-suisse.com 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Fedock, John
(KAM.RIC)
Sent: Friday, October 31, 2008 4:09 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: analyze worked better than gather stats



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

 


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

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

Other related posts: