| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Incorrect cardinality estimate
That seems to be the case. I did not specify estimate_percent for latest run, meaning it did a COMPUTE, which is consistent with a 22-hour runtime. The previous table and index stats used estimate_percent of dbms_stats.auto_sample_size. I haven't compared the 10053 traces yet but I expect that they will bear this out.
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
Sent: Thursday, January 25, 2007 4:00 PM
To: paul.baumgartel_at_credit-suisse.com
Cc: 'oracle-l_at_freelists.org'
Subject: RE: Incorrect cardinality estimate
Paul,
Just for fun, I re-created the column histogram, this time adding cascade=>true (hadn't done that before). The operation took over 22 hours--this is a ~20M row table with 107 partitions. (Question: what is the effect of cascade=>true when creating a histogram for a single column?)
>For the column, density is .006292113.
>
>Now, wonder of wonders, I am getting the (different) optimizer plans
>I want for non-popular (492 rows) and popular (2143642 rows) values
>of ODS_PROCESS_DATE. 10053 trace shows that, for non-popular value,
>optimizer estimates cardinality to be 1223485, but calculates cost
>of single-index lookup (desired path) to be just slightly less than
>the cost of the index join. For the popular value, cardinality is
>computed to be 2296622, and the index hash join in chosen.
I believe this has only indirectly to do with the cascade on the histogram gathering. As I said in my previous post, that causes all indexes to be re-analyzed and must have changed the index statistics enough to cause the plan change. A comparison of the index statistics and the index cost calculations in the 10053 traces should bear that out. You possibly also used a different (higher?) est_percent when you gathered the histogram (as you should) compared to the prior table and index statistics gathering.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
-- http://www.freelists.org/webpage/oracle-l ============================================================================== 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 Thu Jan 25 2007 - 15:30:56 CST
 |
 |