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 - 15:47:44 CST
