| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Join selectivity is 0 causing bad cardinality estimates.
Have you gathered histograms?
begin
dbms_stats.gather_table_stats(
ownname=>'&Schema',
tabname=>'&Table',
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
cascade=>TRUE,
no_invalidate=>FALSE);
I'm working on a cardinality issue just like this. The way the histograms are gathered can make a big difference. Also, try rebuilding the indexes or table.
I hate reading 10053 traces....
andrew.markiewicz_at_gmail.com wrote:
> Hello.
> I have a tuning issue which has brought me a situation that is
> confusing. I have reduced the issue down to a simple join which should
> return all rows in the larger table (7792 rows). But the CBO join
> cardinality estimate is 1 for this join. I created a 10053 trace and
> found the join cardinality calculation uses a selectivity of zero
> causing the CBO to estimate 1 row. What would cause this?
>
> >From my understanding the selectivity for this case should be
> 1/max(192,172) *(1)*(1) = 0.00521.
>
> There are no implicit conversion issues I can find. There are no
> histogram lookups since there are not even any filtering predicates on
> the sql....
> I'm not sure what else to look at.
> Any ideas?
>
> Thanks in advance.
> Andrew
>
> 10053 trace follows:
>
> *** SESSION ID:(74.215) 2006-12-12 10:46:41.061
> QUERY
> select vm.*
> from equipment e
> , vendor_model vm
> where vm.vend_model_num = e.vend_model_num
> ***************************************
> PARAMETERS USED BY THE OPTIMIZER
> ********************************
> OPTIMIZER_FEATURES_ENABLE = 9.2.0
> OPTIMIZER_MODE/GOAL = Choose
> _OPTIMIZER_PERCENT_PARALLEL = 101
> HASH_AREA_SIZE = 2097152
> HASH_JOIN_ENABLED = TRUE
> HASH_MULTIBLOCK_IO_COUNT = 0
> SORT_AREA_SIZE = 1048576
> OPTIMIZER_SEARCH_LIMIT = 5
> PARTITION_VIEW_ENABLED = FALSE
> _ALWAYS_STAR_TRANSFORMATION = FALSE
> _B_TREE_BITMAP_PLANS = TRUE
> STAR_TRANSFORMATION_ENABLED = FALSE
> _COMPLEX_VIEW_MERGING = TRUE
> _PUSH_JOIN_PREDICATE = TRUE
> PARALLEL_BROADCAST_ENABLED = TRUE
> OPTIMIZER_MAX_PERMUTATIONS = 2000
> OPTIMIZER_INDEX_CACHING = 60
> _SYSTEM_INDEX_CACHING = 0
> OPTIMIZER_INDEX_COST_ADJ = 4
> OPTIMIZER_DYNAMIC_SAMPLING = 1
> _OPTIMIZER_DYN_SMP_BLKS = 32
> QUERY_REWRITE_ENABLED = TRUE
> QUERY_REWRITE_INTEGRITY = TRUSTED
> _INDEX_JOIN_ENABLED = TRUE
> _SORT_ELIMINATION_COST_RATIO = 0
> _OR_EXPAND_NVL_PREDICATE = TRUE
> _NEW_INITIAL_JOIN_ORDERS = TRUE
> ALWAYS_ANTI_JOIN = CHOOSE
> ALWAYS_SEMI_JOIN = CHOOSE
> _OPTIMIZER_MODE_FORCE = TRUE
> _OPTIMIZER_UNDO_CHANGES = FALSE
> _UNNEST_SUBQUERY = TRUE
> _PUSH_JOIN_UNION_VIEW = TRUE
> _FAST_FULL_SCAN_ENABLED = TRUE
> _OPTIM_ENHANCE_NNULL_DETECTION = TRUE
> _ORDERED_NESTED_LOOP = TRUE
> _NESTED_LOOP_FUDGE = 100
> _NO_OR_EXPANSION = FALSE
> _QUERY_COST_REWRITE = TRUE
> QUERY_REWRITE_EXPRESSION = TRUE
> _IMPROVED_ROW_LENGTH_ENABLED = TRUE
> _USE_NOSEGMENT_INDEXES = FALSE
> _ENABLE_TYPE_DEP_SELECTIVITY = TRUE
> _IMPROVED_OUTERJOIN_CARD = TRUE
> _OPTIMIZER_ADJUST_FOR_NULLS = TRUE
> _OPTIMIZER_CHOOSE_PERMUTATION = 0
> _USE_COLUMN_STATS_FOR_FUNCTION = TRUE
> _SUBQUERY_PRUNING_ENABLED = TRUE
> _SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
> _SUBQUERY_PRUNING_COST_FACTOR = 20
> _LIKE_WITH_BIND_AS_EQUALITY = FALSE
> _TABLE_SCAN_COST_PLUS_ONE = TRUE
> _SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
> _DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
> _ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
> _OPTIMIZER_COST_MODEL = CHOOSE
> _GSETS_ALWAYS_USE_TEMPTABLES = FALSE
> DB_FILE_MULTIBLOCK_READ_COUNT = 8
> _NEW_SORT_COST_ESTIMATE = TRUE
> _GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
> _CPU_TO_IO = 0
> _PRED_MOVE_AROUND = TRUE
> ***************************************
> BASE STATISTICAL INFORMATION
> ***********************
> Table stats Table: VENDOR_MODEL Alias: VM
> TOTAL :: CDN: 192 NBLKS: 2 AVG_ROW_LEN: 45
> Column: VEND_MODEL Col#: 1 Table: VENDOR_MODEL Alias: VM
> NDV: 192 NULLS: 0 DENS: 5.2083e-03 LO: 1 HI: 194
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> Column: VEND_MODEL Col#: 1 Table: VENDOR_MODEL Alias: VM
> NDV: 192 NULLS: 0 DENS: 5.2083e-03 LO: 1 HI: 194
> NO HISTOGRAM: #BKT: 1 #VAL: 2
> -- Index stats
> INDEX NAME: I_VENDOR_MODEL_PK COL#: 1
> TOTAL :: LVLS: 0 #LB: 1 #DK: 192 LB/K: 1 DB/K: 1 CLUF: 6
> INDEX NAME: I_VENDOR_MODEL_UK1 COL#: 2 3 4
> TOTAL :: LVLS: 0 #LB: 1 #DK: 192 LB/K: 1 DB/K: 1 CLUF: 63
> ***********************
> Table stats Table: EQUIPMENT Alias: E
> TOTAL :: CDN: 7792 NBLKS: 52 AVG_ROW_LEN: 42
> Column: VEND_MODEL Col#: 3 Table: EQUIPMENT Alias: E
> NDV: 172 NULLS: 0 DENS: 3.3694e-03
> HEIGHT BALANCED HISTOGRAM: #BKT: 200 #VAL: 65
> -- Index stats
> INDEX NAME: I_EQUIPMENT_2 COL#: 3
> TOTAL :: LVLS: 1 #LB: 28 #DK: 172 LB/K: 1 DB/K: 6 CLUF: 1123
> INDEX NAME: I_EQUIPMENT_3 COL#: 4
> TOTAL :: LVLS: 1 #LB: 15 #DK: 2208 LB/K: 1 DB/K: 1 CLUF:
> 2539
> INDEX NAME: I_EQUIPMENT_PK COL#: 1
> TOTAL :: LVLS: 1 #LB: 26 #DK: 7792 LB/K: 1 DB/K: 1 CLUF: 51
> INDEX NAME: I_EQUIPMENT_UK1 COL#: 2
> TOTAL :: LVLS: 1 #LB: 19 #DK: 7792 LB/K: 1 DB/K: 1 CLUF:
> 4404
> _OPTIMIZER_PERCENT_PARALLEL = 0
> ***************************************
> SINGLE TABLE ACCESS PATH
> TABLE: EQUIPMENT ORIG CDN: 7792 ROUNDED CDN: 7792 CMPTD CDN:
> 7792
> Access path: tsc Resc: 10 Resp: 10
> Access path: index (iff)
> Index: I_EQUIPMENT_2
> TABLE: EQUIPMENT
> RSC_CPU: 0 RSC_IO: 6
> IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
> Access path: iff Resc: 6 Resp: 6
> Access path: index (no sta/stp keys)
> Index: I_EQUIPMENT_2
> TABLE: EQUIPMENT
> RSC_CPU: 0 RSC_IO: 29
> IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
> BEST_CST: 2.00 PATH: 4 Degree: 1
> ***************************************
> SINGLE TABLE ACCESS PATH
> TABLE: VENDOR_MODEL ORIG CDN: 192 ROUNDED CDN: 192 CMPTD CDN:
> 192
> Access path: tsc Resc: 2 Resp: 2
> BEST_CST: 2.00 PATH: 2 Degree: 1
> ***************************************
> OPTIMIZER STATISTICS AND COMPUTATIONS
> ***************************************
> GENERAL PLANS
> ***********************
> Join order[1]: VENDOR_MODEL [VM] EQUIPMENT [ E]
> Now joining: EQUIPMENT [ E] *******
> NL Join
> Outer table: cost: 2 cdn: 192 rcz: 45 resp: 2
> Inner table: EQUIPMENT
> Access path: tsc Resc: 10
> Join: Resc: 1922 Resp: 1922
> Access path: index (iff)
> Index: I_EQUIPMENT_2
> TABLE: EQUIPMENT
> RSC_CPU: 0 RSC_IO: 6
> IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
> Inner table: EQUIPMENT
> Access path: iff Resc: 6
> Join: Resc: 1154 Resp: 1154
> OPTIMIZER PERCENT INDEX CACHING = 60
> Access path: index (join index)
> Index: I_EQUIPMENT_2
> TABLE: EQUIPMENT
> RSC_CPU: 0 RSC_IO: 0
> IX_SEL: 0.0000e+00 TB_SEL: 5.8140e-03
> Join: resc: 3 resp: 3
> Join cardinality: 0 = outer (192) * inner (7792) * sel (0.0000e+00)
> [flag=0]
> Best NL cost: 4 resp: 3
> SM Join
> Outer table:
> resc: 2 cdn: 192 rcz: 45 deg: 1 resp: 2
> Inner table: EQUIPMENT
> resc: 1 cdn: 7792 rcz: 4 deg: 1 resp: 1
> using join:1 distribution:2 #groups:1
> SORT resource Sort statistics
> Sort width: 29 Area size: 712704 Max Area size:
> 712704 Degree: 1
> Blocks to Sort: 2 Row size: 60 Rows: 192
> Initial runs: 1 Merge passes: 1 IO Cost / pass:
> 7
> Total IO sort cost: 4
> Total CPU sort cost: 0
> Total Temp space used: 0
> SORT resource Sort statistics
> Sort width: 29 Area size: 712704 Max Area size:
> 712704 Degree: 1
> Blocks to Sort: 15 Row size: 15 Rows: 7792
> Initial runs: 1 Merge passes: 1 IO Cost / pass:
> 20
> Total IO sort cost: 18
> Total CPU sort cost: 0
> Total Temp space used: 0
> Merge join Cost: 25 Resp: 25
> SM Join (with index on outer)
> Access path: index (no sta/stp keys)
> Index: I_VENDOR_MODEL_PK
> TABLE: VENDOR_MODEL
> RSC_CPU: 0 RSC_IO: 7
> IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
> Outer table:
> resc: 1 cdn: 192 rcz: 45 deg: 1 resp: 1
> Inner table: EQUIPMENT
> resc: 1 cdn: 7792 rcz: 4 deg: 1 resp: 1
> using join:1 distribution:2 #groups:1
> SORT resource Sort statistics
> Sort width: 29 Area size: 712704 Max Area size:
> 712704 Degree: 1
> Blocks to Sort: 15 Row size: 15 Rows: 7792
> Initial runs: 1 Merge passes: 1 IO Cost / pass:
> 20
> Total IO sort cost: 18
> Total CPU sort cost: 0
> Total Temp space used: 0
> Merge join Cost: 20 Resp: 20
> HA Join
> Outer table:
> resc: 2 cdn: 192 rcz: 45 deg: 1 resp: 2
> Inner table: EQUIPMENT
> resc: 1 cdn: 7792 rcz: 4 deg: 1 resp: 1
> using join:8 distribution:2 #groups:1
> Hash join one ptn Resc: 1 Deg: 1
> hash_area: 256 (max=256) buildfrag: 257
> probefrag: 16 ppasses: 2
> Hash join Resc: 4 Resp: 4
> Join result: cost: 4 cdn: 1 rcz: 49
> Best so far: TABLE#: 0 CST: 2 CDN: 192 BYTES:
> 8640
> Best so far: TABLE#: 1 CST: 4 CDN: 1 BYTES:
> 49
> ***********************
> Join order[2]: EQUIPMENT [ E] VENDOR_MODEL [VM]
> Now joining: VENDOR_MODEL [VM] *******
> NL Join
> Outer table: cost: 2 cdn: 7792 rcz: 4 resp: 1
> Inner table: VENDOR_MODEL
> Access path: tsc Resc: 2
> Join: Resc: 15585 Resp: 15585
> OPTIMIZER PERCENT INDEX CACHING = 60
> Access path: index (unique)
> Index: I_VENDOR_MODEL_PK
> TABLE: VENDOR_MODEL
> RSC_CPU: 0 RSC_IO: 0
> IX_SEL: 5.2083e-03 TB_SEL: 5.2083e-03
> Join (ordered NL): resc: 2 resp: 2
> OPTIMIZER PERCENT INDEX CACHING = 60
> Access path: index (eq-unique)
> Index: I_VENDOR_MODEL_PK
> TABLE: VENDOR_MODEL
> RSC_CPU: 0 RSC_IO: 0
> IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00
> Join (ordered NL): resc: 2 resp: 2
> Join cardinality: 0 = outer (7792) * inner (192) * sel (0.0000e+00)
> [flag=0]
> Best NL cost: 3 resp: 2
> SM Join
> Outer table:
> resc: 1 cdn: 7792 rcz: 4 deg: 1 resp: 1
> Inner table: VENDOR_MODEL
> resc: 2 cdn: 192 rcz: 45 deg: 1 resp: 2
> using join:1 distribution:2 #groups:1
> SORT resource Sort statistics
> Sort width: 29 Area size: 712704 Max Area size:
> 712704 Degree: 1
> Blocks to Sort: 2 Row size: 60 Rows: 192
> Initial runs: 1 Merge passes: 1 IO Cost / pass:
> 7
> Total IO sort cost: 4
> Total CPU sort cost: 0
> Total Temp space used: 0
> Merge join Cost: 8 Resp: 8
> HA Join
> Outer table:
> resc: 1 cdn: 7792 rcz: 4 deg: 1 resp: 1
> Inner table: VENDOR_MODEL
> resc: 2 cdn: 192 rcz: 45 deg: 1 resp: 2
> using join:8 distribution:2 #groups:1
> Hash join one ptn Resc: 2 Deg: 1 (sides swapped)
> hash_area: 256 (max=256) buildfrag: 257
> probefrag: 2 ppasses: 2
> Hash join Resc: 5 Resp: 5
> Outer table:
> resc: 2 cdn: 192 rcz: 45 deg: 1 resp: 2
> Inner table: EQUIPMENT
> resc: 1 cdn: 7792 rcz: 4 deg: 1 resp: 1
> using join:8 distribution:2 #groups:1
> Hash join one ptn Resc: 1 Deg: 1
> hash_area: 256 (max=256) buildfrag: 257
> probefrag: 16 ppasses: 2
> Hash join Resc: 4 Resp: 4
> Join result: cost: 3 cdn: 1 rcz: 49
> Best so far: TABLE#: 1 CST: 2 CDN: 7792 BYTES:
> 31168
> Best so far: TABLE#: 0 CST: 3 CDN: 1 BYTES:
> 49
> Final:
> CST: 3 CDN: 1 RSC: 2 RSP: 2 BYTES: 49
> IO-RSC: 2 IO-RSP: 2 CPU-RSC: 2 CPU-RSP: 2
> *** 2006-12-12 10:47:05.862
Received on Tue Dec 12 2006 - 16:06:27 CST
 |
 |