| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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)
and the join cardinality = 1.10043976277393E-5 * 5 * 4926K -- Please refer the plan.
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.
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
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 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
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
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
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_at_naturaljoin.nl]
Sent: Wednesday, November 03, 2004 3:22 PM
To: joshic_at_mahindrabt.com
Subject: RE: Join cardinality and query tuning.
>> I would need the full 10053 trace file to judge that ...
Lex.
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 08 2004 - 04:07:05 CST
 |
 |