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

Home -> Community -> Usenet -> c.d.o.server -> Re: CBO rows estimate way out in 2 table join - what to do?

Re: CBO rows estimate way out in 2 table join - what to do?

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Wed, 31 Oct 2007 03:12:35 -0700
Message-ID: <1193825555.495130.123790@y42g2000hsy.googlegroups.com>

>
> Hi Stephen,
>
> Do you have 101 distinct values in whatever column(s) is/are indexed
> by the CUSTPRODUCTDETAILS_PRF index? What is your setting for
> cursor_sharing?
>
> It looks like the stats suggest that there are only 101 matches in the
> customerproductdetails table for whatever rows in account have a
> currency_code of GBP and an invoicing_co_id of 1. Does that sound
> like the right number?
>
> Out of the 10K rows, are there 855 rows in account with the query
> parameters you have?
>
> Have you run a 10053 trace (also detailed in Jonathans book)?
> Although cryptic, I usually find the cardinality estimates so
> important in joins can be extracted from that trace file without a
> *ton* of effort.
>
> HTH,
>
> Steve

Cursor sharing is off on this database.

The account table has 10K rows - those with currency code GBP and Invoicing_co_id = 1 makes up about 9000 or more of those rows (very skewed data, histograms on these two column on account did help things somewhat).

In the CustProductDetails table, each of the ~70M rows must have a matching account row, meaning if you join account and CustProductDetails the result will be about 70M rows. In this case we can expect it to be nine tenths of that give or take.

I was hunting through the 10053 trace file, and was able to extract the numbers (and formulae) that Oracle was using to get these estimates, so I understand why it is doing what it is doing - its figuring out how to make it do something else without putting an 'evil' hint in there that has me stumped! Received on Wed Oct 31 2007 - 05:12:35 CDT

Original text of this message

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