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

On Oct 31, 3:10 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "stephen O'D" <stephen.odonn..._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.

This seems a bit strange to me, given the cascade true, 10K distributed skewed across 70M rows? Wouldn't it be better to use a larger percent on the table, no cascade, and separately completely analyze the index (and any others)? Yes it would take hours, but wouldn't it likely be worth it to get a better execution time? Am I missing an elephant in my blind flailing?

jg

--
@home.com is bogus.
http://www.oracle.com/global/ap/openworld/ppt_download/index.html
(Thanks Eddie!)