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

On Oct 30, 6:17 am, stephen O'D <stephen.odonn..._at_gmail.com> wrote:
> > Can you post the execution plan?
>
> OK, here is the full query and execution plan - I re-gathered stats on
> the tables with method_opt => 'FOR ALL COLUMNS SIZE 1'
>
> SQL> explain plan for
> SELECT
> --count(*)
> cpd.customer_ref
> FROM custproductdetails cpd, ACCOUNT a
> WHERE cpd.account_num = a.account_num
> AND cpd.customer_ref = a.customer_ref
> AND a.currency_code = 'GBP'
> AND a.invoicing_co_id = 1 2 3 4 5 6 7 8
> 9 ;
>
> Explained.
>
> SQL> /
>
> Explained.
>
> SQL> select * from table(dbms_xplan.display());
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------------------------
>
> -------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes
> | Cost | TQ |IN-OUT| PQ Distrib |
> -------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 86620 |
> 4652K| 1326 | | | |
> | 1 | NESTED LOOPS | | 86620 |
> 4652K| 1326 | 89,01 | P->S | QC (RAND) |
> |* 2 | TABLE ACCESS FULL | ACCOUNT | 855 | 27360
> | 43 | 89,00 | S->P | RND-ROBIN |
> |* 3 | INDEX RANGE SCAN | CUSTPRODUCTDETAILS_PRF | 101 | 2323
> | 5 | 89,01 | PCWP | |
> -------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("A"."CURRENCY_CODE"='GBP' AND "A"."INVOICING_CO_ID"=1)
> 3 - access("CPD"."CUSTOMER_REF"="A"."CUSTOMER_REF" AND
> "CPD"."ACCOUNT_NUM"="A"."ACCOUNT_NUM")
>
> Note: cpu costing is off
>
> In this case Oracle is guessing 86K rows (really there will be ~
> 60Million for this query), which means its choosing a bad plan here -
> probably should full scan both tables and not index range scan the big
> custProductDetails table.

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 Received on Tue Oct 30 2007 - 09:54:37 CDT