Re: CBO rows estimate way out in 2 table join

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Mon, 05 Nov 2007 02:35:50 -0800
Message-ID: <1194258950.824682.13800@o3g2000hsb.googlegroups.com>

On Oct 31, 10: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.
>
> If you do this, what does the plan look like ?

Sorry for the slow response - some other stuff come up that distracted me from this for a while.

I gathered stats again, this time with the histograms only on account (currency_code and invoicing_co_id). Things are better, but still nowhere close to the correct answer:

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

| Id  | Operation            |  Name                   | Rows  | Bytes

|   0 | SELECT STATEMENT     |                         | 32848 |
1764K|  3117 |        |      |            |
|   1 |  NESTED LOOPS        |                         | 32848 |
1764K|  3117 | 02,01  | P->S | QC (RAND)  |
|*  2 |   TABLE ACCESS FULL  | ACCOUNT                 |  2561 | 81952
|    43 | 02,00  | S->P | RND-ROBIN  |
|*  3 |   INDEX RANGE SCAN   | CUSTPRODUCTDETAILS_PRF  |    13 |   299
|     4 | 02,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

17 rows selected.

SQL> select count(*) from account
2 where currency_code = 'GBP'
3 and invoicing_co_id = 1;

COUNT(*)

9601 Received on Mon Nov 05 2007 - 04:35:50 CST

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