RE: Join cardinality and query tuning.

Hi all,

Apologies for the delay in getting back. Had a tough time getting the 10053
trace (it being production environment). Ultimately I replicated the
behaviour in one of the test environments. Given below is the relevant
portion of the trace, after a quick recap of the scenario:

The explain plan for the statement:

select *
from
W_ACTIVITY_F T29238,
W_LOV_D T230600
where
T29238.X_BT_OUTCOME_AREA_WID = T230600.ROW_WID
and T230600.VAL in ('Save-No', 'Save-Yes')

Shows up as:

-----------------------------------------------------------------------
| Id  | Operation                     |  Name         | Rows  | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |  1952K| 38636 |
|   1 |  HASH JOIN                    |               |  1952K| 38636 |
|   2 |   INLIST ITERATOR             |               |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| W_LOV_D       |     5 |     6 |
|   4 |     INDEX RANGE SCAN          | W_LOV_D_M3    |     5 |     2 |
|   5 |   TABLE ACCESS FULL           | W_ACTIVITY_F  |  4926K| 38627 |
-----------------------------------------------------------------------

The relevant statistics being:


TABLE_NAME      COLUMN_NAME           DIST_CNT  NUM_NULLS Buckets DENSITY
--------------- -------------------- --------- ---------- ---- ----------
W_ACTIVITY_F    ROW_WID               18963140          0    1 5.2734E-08
W_ACTIVITY_F    X_BT_OUTCOME_AREA_WID       13   13414260    1 .076923077
W_LOV_D         ROW_WID                  24410          0    1 .000040967
W_LOV_D         VAL                       9478          0    1 .000105507

(NOTE: No histograms.)

and

TABLE_NAME       NUM_ROWS
--------------- ---------
W_ACTIVITY_F     18340960
W_LOV_D             24410


So the join selectivity = (1/MAX(24410,13))* ( (18340960 -
13414260)/18340960)*((24410 - 0)/24410)
                                = 1.10043976277393E-5

and the join cardinality =  1.10043976277393E-5 * 5 * 4926K  -- Please refer
the plan.
                                 =  272

Yet the CBO calculates it as 1952K.

I hope I have picked up all the important statistics. If I have missed out
on something then please let me know. To highlight the most significant
statistic from the trace:

Join cardinality:  1952063 = outer (5) * inner (4926700) * sel (7.6923e-02)
[flag=0]

Please let me know where I am going wrong.

BTW,
I tried tweaking the statistics such that the CBO predicted the correct join
cardinality, but that didn't result in it taking the right execution plan -
which is Nested loops from W_LOV_D with index range scan of W_ACTVITY_F_N5
index. When I reduced the Data_blocks_per_key statistic of the
W_ACTIVITY_F_N5 index, then CBO picked up the best execution plan. So I was
wrong to think that the wrong access plan was due to incorrect JC estimate,
and Jaromir was right.

Eager to know your comments.

Thanks & Regards,
Charu.


*** 2004-11-08 08:00:26.772
*** SESSION ID:(27.18316) 2004-11-08 08:00:26.771
QUERY
explain plan set statement_id = 'cj' for
select *
from
W_ACTIVITY_F T29238,
W_LOV_D T230600
where
T29238.X_BT_OUTCOME_AREA_WID = T230600.ROW_WID
and T230600.VAL in ('Save-No', 'Save-Yes')
***************************************
PARAMETERS USED BY THE OPTIMIZER


***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: W_LOV_D   Alias: T230600
  TOTAL ::  CDN: 24410  NBLKS:  118  AVG_ROW_LEN:  100
Column:    ROW_WID  Col#: 5      Table: W_LOV_D   Alias: T230600
    NDV: 24410     NULLS: 0         DENS: 4.0967e-05 LO:  10  HI: 24067
    NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
  INDEX NAME: W_LOV_D_M3  COL#: 7
    TOTAL ::  LVLS: 1   #LB: 44  #DK: 13504  LB/K: 1  DB/K: 1  CLUF: 16613
  INDEX NAME: W_LOV_D_P1  COL#: 5
    TOTAL ::  LVLS: 1   #LB: 19  #DK: 21100  LB/K: 1  DB/K: 1  CLUF: 98

***********************
Table stats    Table: W_ACTIVITY_F   Alias: T29238
  TOTAL ::  CDN: 18340960  NBLKS:  401617  AVG_ROW_LEN:  302
Column: X_BT_OUTCO  Col#: 94     Table: W_ACTIVITY_F   Alias: T29238
    NDV: 13        NULLS: 13414260  DENS: 7.6923e-02 LO:  0  HI: 21070
    NO HISTOGRAM: #BKT: 1 #VAL: 2

-- Index stats

  INDEX NAME: W_ACTIVITY_F_N5  COL#: 94
    TOTAL ::  LVLS: 2   #LB: 9408  #DK: 5  LB/K: 1881  DB/K: 64077  CLUF:
320388

  INDEX NAME: W_ACTIVITY_F_P1  COL#: 31
    TOTAL ::  LVLS: 2   #LB: 19070  #DK: 19010876  LB/K: 1  DB/K: 1  CLUF:
388276

***************************************
SINGLE TABLE ACCESS PATH
  TABLE: W_ACTIVITY_F     ORIG CDN: 18340960  ROUNDED CDN: 4926700  CMPTD
CDN: 4926700
  Access path: tsc  Resc:  38627  Resp:  38627

  Access path: index (no sta/stp keys)
      Index: W_ACTIVITY_F_N5
  TABLE: W_ACTIVITY_F
      RSC_CPU: 0   RSC_IO: 9410
  IX_SEL:  1.0000e+00  TB_SEL:  1.0000e+00

  Access path: index (no sta/stp keys)
      Index: W_ACTIVITY_F_P1
  TABLE: W_ACTIVITY_F
      RSC_CPU: 0   RSC_IO: 19072
  IX_SEL:  1.0000e+00  TB_SEL:  1.0000e+00

  BEST_CST: 38627.00  PATH: 2  Degree:  1
***************************************
SINGLE TABLE ACCESS PATH
Column:        VAL  Col#: 7      Table: W_LOV_D   Alias: T230600
    NDV: 9478      NULLS: 0         DENS: 1.0551e-04
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: W_LOV_D     ORIG CDN: 24410  ROUNDED CDN: 5  CMPTD CDN: 5
  Access path: tsc  Resc:  13  Resp:  13
  Access path: index (scan)
      Index: W_LOV_D_M3
  TABLE: W_LOV_D
      RSC_CPU: 0   RSC_IO: 6
  IX_SEL:  2.1101e-04  TB_SEL:  2.1101e-04

  Access path: index (equal)
      Index: W_LOV_D_M3
  TABLE: W_LOV_D
      RSC_CPU: 0   RSC_IO: 1
  IX_SEL:  1.0551e-04  TB_SEL:  1.0551e-04

  BEST_CST: 6.00  PATH: 4  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: W_LOV_D [T230600] W_ACTIVITY_F [T29238]
Now joining: W_ACTIVITY_F [T29238] *******
NL Join
  Outer table: cost: 6  cdn: 5  rcz: 79  resp:  6
  Inner table: W_ACTIVITY_F
    Access path: tsc  Resc: 38627
    Join:  Resc:  193141  Resp:  193141
  Access path: index (join index)
      Index: W_ACTIVITY_F_N5
  TABLE: W_ACTIVITY_F
      RSC_CPU: 0   RSC_IO: 65959
  IX_SEL:  0.0000e+00  TB_SEL:  7.6923e-02
    Join:  resc: 329801  resp: 329801
Join cardinality:  1952063 = outer (5) * inner (4926700) * sel (7.6923e-02)
[flag=0]
  Best NL cost: 193141  resp: 193141


*
Thanks very much for your patience, because you reached here!! :-)
*


-----Original Message-----
From: Natural Join B.V. [mailto:lex.de.haan@xxxxxxxxxxxxxx]
Sent: Wednesday, November 03, 2004 3:22 PM
To: joshic@xxxxxxxxxxxxxx
Subject: RE: Join cardinality and query tuning.


>> I would need the full 10053 trace file to judge that ...

Lex.


*********************************************************
Disclaimer:          

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*********************************************************
Visit us at http://www.mahindrabt.com


--
http://www.freelists.org/webpage/oracle-l

Other related posts: