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: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 31 Oct 2007 22:10:17 -0000
Message-ID: <vN2dnaVwQJbTYLXaRVnyggA@bt.com>

"stephen O'D" <stephen.odonnell_at_gmail.com> wrote in message news:1193739465.370652.161440_at_d55g2000hsg.googlegroups.com...
>
>> 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.
>
>

begin

    dbms_stats.gather_table_stats({owner},'custproductdetails', 
cascade=>true, method_opt=>'for all columns size 1');
    dbms_stats.gather_table_stats({owner},'account', cascade=>true, 
method_opt=>'for all columns size 1');

    dbms_stats.gather_table_stats({owner},'account', method_opt=>'for columns currency_code, invoicing_code_id size 254');

end;

i.e. simple stats everywhere, with histograms - hopefully frequency histograms -
on the two filter predicates in your where clause.

You might want to set a block sample of a few percent for the big table.

If you do this, what does the plan look like ?

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Wed Oct 31 2007 - 17:10:17 CDT

Original text of this message

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