RE: Incorrect cardinality estimate
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 25 Jan 2007 21:30:56 -0000
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@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Thursday, January 25, 2007 4:00 PM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx
Cc: 'oracle-l@xxxxxxxxxxxxx'
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
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
//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
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
