RE: Join cardinality and query tuning.
- From: "Charu Joshi" <joshic@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 8 Nov 2004 15:40:29 +0530
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
