RE: analyze worked better than gather stats
Date: Fri, 31 Oct 2008 16:19:03 -0400
Message-ID: <21469B88E0EA11498818517F210335310455B809@EPRI17P32001A.csfb.cs-group.com>
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_at_credit-suisse.com
www.credit-suisse.com
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fedock, John
(KAM.RIC)
Sent: Friday, October 31, 2008 4:09 PM
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 31 2008 - 15:19:03 CDT