Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Incorrect cardinality estimate

RE: Incorrect cardinality estimate

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 25 Jan 2007 13:59:38 -0700
Message-Id: <20070125205854.5DECF5B5F7F@turing.freelists.org>


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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 25 2007 - 14:59:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US