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: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 25 Jan 2007 21:30:56 -0000
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663BC05@ENYC11P32005.corpny.csfb.com>


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



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



==============================================================================
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-l
Received on Thu Jan 25 2007 - 15:30:56 CST

Original text of this message

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