Help understanding CBO plan
- From: huy-luan.le@xxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 26 Oct 2004 10:23:43 +0200
Hi,
I see that CBO did a wrong choice for his plan (HA join is the best, not
NL).
I did a 10053 trace and there 2 two things I don't understand :
How is comptutated this line :
BEST_CST: 2166.00 PATH: 4 Degree: 1
?
and how can the cost and TBSEL be negative
CST: -4659 IXSEL: 1.5767e-01 TBSEL: -5.5476e-02
?
This gives for the this NL join this cost : Join resc: 2167 , the best
for CBO.
How is computed this value 2167 from the CST -4659 ?
I use this for stats :
dbms_stats.gather_schema_stats('SYSADM',CASCADE=>TRUE,ESTIMATE_PERCENT
=>NULL,method_opt=>'FOR ALL COLUMNS SIZE 75
');
Thanks for your help.
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.2.0 - 64bit Production
...
SELECT 35150, 'AP',
A.BUSINESS_UNIT, A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, 0, 0, A.LEDGER_GROUP,
A.LEDGER, A.ACCOUNT, A.ALTACCT, B.VENDOR_SETID, B.VENDOR_ID, ' ', ' ', ' ',
'REFCO', A.DOC_TYPE, A.DOC_SEQ_NBR, A.DOC_SEQ_DATE, A.ACCOUNTING_DT,
A.BUSINESS_UNIT_GL, A.BUSINESS_UNIT, A.JOURNAL_ID, ' ', ' ', B.INVOICE_ID,
B.INVOICE_DT, A.APPL_JRNL_ID, ' ', 0, 0, 0, 0, 0, 0, A.PYMNT_CNT,
A.UNPOST_SEQ, A.VOUCHER_ID, A.CURRENCY_CD, A.FOREIGN_CURRENCY, A.RT_TYPE,
A.RATE_DIV, A.RATE_MULT, A.MOVEMENT_FLAG, A.MONETARY_AMOUNT,
A.FOREIGN_AMOUNT, 0, 0, 0, 0
FROM
PS_VCHR_ACCTG_LINE A, PS_VOUCHER B
WHERE
A.BUSINESS_UNIT = 'ACF03' AND A.FISCAL_YEAR = 2004 AND A.ACCOUNTING_PERIOD
=
9 AND A.GL_DISTRIB_STATUS = 'D' AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND
A.VOUCHER_ID = B.VOUCHER_ID
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 8.1.7
OPTIMIZER_MODE/GOAL = Choose
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 3145728
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = FALSE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = FALSE
_PUSH_JOIN_PREDICATE = FALSE
PARALLEL_BROADCAST_ENABLED = FALSE
OPTIMIZER_MAX_PERMUTATIONS = 80000
OPTIMIZER_INDEX_CACHING = 70
OPTIMIZER_INDEX_COST_ADJ = 50
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = FALSE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = FALSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = FALSE
_PUSH_JOIN_UNION_VIEW = FALSE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = FALSE
_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 = FALSE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
DB_FILE_MULTIBLOCK_READ_COUNT = 32
SORT_AREA_SIZE = 3145728
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: PS_VOUCHER Alias: B
TOTAL :: CDN: 87165 NBLKS: 7794 TABLE_SCAN_CST: 476 AVG_ROW_LEN:
631
-- Index stats
INDEX#: 57471 COL#: 4 1 2
TOTAL :: LVLS: 2 #LB: 632 #DK: 87165 LB/K: 1 DB/K: 1 CLUF: 55103
INDEX#: 57472 COL#: 8 1 2
TOTAL :: LVLS: 2 #LB: 604 #DK: 87165 LB/K: 1 DB/K: 1 CLUF: 54002
INDEX#: 57473 COL#: 1 11 26
TOTAL :: LVLS: 1 #LB: 387 #DK: 67386 LB/K: 1 DB/K: 1 CLUF: 84336
INDEX#: 57470 COL#: 1 2
TOTAL :: LVLS: 2 #LB: 524 #DK: 87165 LB/K: 1 DB/K: 1 CLUF: 11697
***********************
Table stats Table: PS_VCHR_ACCTG_LINE Alias: A
TOTAL :: CDN: 540958 NBLKS: 36416 TABLE_SCAN_CST: 2220 AVG_ROW_LEN:
489
-- Index stats
INDEX#: 54851 COL#: 4 1 45 37 18
TOTAL :: LVLS: 2 #LB: 4662 #DK: 2418 LB/K: 1 DB/K: 17 CLUF:
42687
INDEX#: 54852 COL#: 46
TOTAL :: LVLS: 2 #LB: 1834 #DK: 133 LB/K: 13 DB/K: 293 CLUF:
39029
INDEX#: 278659 COL#: 1 39 38 45 2
TOTAL :: LVLS: 2 #LB: 2844 #DK: 168345 LB/K: 1 DB/K: 1 CLUF:
113057
INDEX#: 54850 COL#: 1 2 3 4 5 6 7 8 9 10 11 12
TOTAL :: LVLS: 2 #LB: 6110 #DK: 540958 LB/K: 1 DB/K: 1 CLUF:
217832
***************************************
SINGLE TABLE ACCESS PATH
Column: BUSINESS_U Col#: 1 Table: PS_VCHR_ACCTG_LINE Alias: A
NDV: 27 NULLS: 0 DENS: 9.2429e-07
Column: FISCAL_YEA Col#: 39 Table: PS_VCHR_ACCTG_LINE Alias: A
NDV: 2 NULLS: 0 DENS: 9.2429e-07
Column: ACCOUNTING Col#: 38 Table: PS_VCHR_ACCTG_LINE Alias: A
NDV: 8 NULLS: 0 DENS: 9.2429e-07
Column: GL_DISTRIB Col#: 45 Table: PS_VCHR_ACCTG_LINE Alias: A
NDV: 2 NULLS: 0 DENS: 9.2429e-07
TABLE: PS_VCHR_ACCTG_LINE ORIG CDN: 540958 CMPTD CDN: 20203
Access path: tsc Resc: 2220 Resp: 2220
Access path: index (scan)
INDEX#: 278659 TABLE: PS_VCHR_ACCTG_LINE
CST: 4332 IXSEL: 3.7345e-02 TBSEL: 3.7345e-02
Access path: index (scan)
INDEX#: 54850 TABLE: PS_VCHR_ACCTG_LINE
CST: 36297 IXSEL: 1.6207e-01 TBSEL: 1.6207e-01
BEST_CST: 2166.00 PATH: 4 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
Column: BUSINESS_U Col#: 1 Table: PS_VOUCHER Alias: B
NDV: 27 NULLS: 0 DENS: 5.7362e-06
TABLE: PS_VOUCHER ORIG CDN: 87165 CMPTD CDN: 13743
Access path: tsc Resc: 476 Resp: 476
Access path: index (scan)
INDEX#: 57473 TABLE: PS_VOUCHER
CST: 13360 IXSEL: 1.5767e-01 TBSEL: 1.5767e-01
Access path: index (scan)
INDEX#: 57470 TABLE: PS_VOUCHER
CST: 1930 IXSEL: 1.5767e-01 TBSEL: 1.5767e-01
BEST_CST: 476.00 PATH: 2 Degree: 1
Table: PS_VCHR_ACCTG_LINE Join index: 57470
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: PS_VOUCHER [ B] PS_VCHR_ACCTG_LINE [ A]
Now joining: PS_VCHR_ACCTG_LINE [ A] *******
NL Join
Outer table: cost: 476 cdn: 13743 rcz: 46 resp: 476
Inner table: PS_VCHR_ACCTG_LINE
Access path: tsc Resc: 2220
Join resc: 30509936 Resp: 30509936
OPTIMIZER PERCENT INDEX CACHING = 70
Access path: index (join index)
INDEX#: 278659 TABLE: PS_VCHR_ACCTG_LINE
CST: 1 IXSEL: 0.0000e+00 TBSEL: 2.9236e-07
Join resc: 7348 resp:7348
OPTIMIZER PERCENT INDEX CACHING = 70
Access path: index (scan)
INDEX#: 54850 TABLE: PS_VCHR_ACCTG_LINE
CST: 2 IXSEL: 1.2687e-06 TBSEL: 2.0562e-07
Join resc: 14219 resp:14219
Join cardinality: 3185 = outer (13743) * inner (20203) * sel (1.1472e-05)
[flag=0]
Using index (ndv = 87165 sel = -2.3817e-05)
Best NL cost: 7348 resp: 7348
SM Join
Outer table:
resc: 476 cdn: 13743 rcz: 46 deg: 1 resp: 476
Inner table: PS_VCHR_ACCTG_LINE
resc: 2166 cdn: 20203 rcz: 121 deg: 1 resp: 2166
SORT resource Sort statistics
Sort width: 5 Area size: 2125824 Degree: 1
Blocks to Sort: 103 Row size: 61 Rows: 13743
Initial runs: 1 Merge passes: 1 Cost / pass:
124
Total sort cost: 114
SORT resource Sort statistics
Sort width: 5 Area size: 2125824 Degree: 1
Blocks to Sort: 357 Row size: 144 Rows: 20203
Initial runs: 2 Merge passes: 1 Cost / pass:
429
Total sort cost: 393
Merge join Cost: 3148 Resp: 3148
SM Join (with index on outer)
Access path: index (scan)
INDEX#: 57470 TABLE: PS_VOUCHER
CST: 1930 IXSEL: 1.5767e-01 TBSEL: 1.5767e-01
Outer table:
resc: 965 cdn: 13743 rcz: 46 deg: 1 resp: 965
Inner table: PS_VCHR_ACCTG_LINE
resc: 2166 cdn: 20203 rcz: 121 deg: 1 resp: 2166
SORT resource Sort statistics
Sort width: 5 Area size: 2125824 Degree: 1
Blocks to Sort: 357 Row size: 144 Rows: 20203
Initial runs: 2 Merge passes: 1 Cost / pass:
429
Total sort cost: 393
Merge join Cost: 3524 Resp: 3524
HA Join
Outer table:
resc: 476 cdn: 13743 rcz: 46 deg: 1 resp: 476
Inner table: PS_VCHR_ACCTG_LINE
resc: 2166 cdn: 20203 rcz: 121 deg: 1 resp: 2166
Hash join one ptn: 34 Deg: 1
hash_area: 384 buildfrag: 385 probefrag: 329
ppasses: 2
Hash join Resc: 2676 Resp: 2676
Join result: cost: 2676 cdn: 3185 rcz: 167
Best so far: TABLE#: 0 CST: 476 CDN: 13743 BYTES: 632178
Best so far: TABLE#: 1 CST: 2676 CDN: 3185 BYTES: 531895
***********************
Join order[2]: PS_VCHR_ACCTG_LINE [ A] PS_VOUCHER [ B]
Now joining: PS_VOUCHER [ B] *******
NL Join
Outer table: cost: 2166 cdn: 20203 rcz: 121 resp: 2166
Inner table: PS_VOUCHER
Access path: tsc Resc: 476
Join resc: 9618794 Resp: 9618794
OPTIMIZER PERCENT INDEX CACHING = 70
Access path: index (unique)
INDEX#: 57470 TABLE: PS_VOUCHER
CST: 1 IXSEL: 7.2764e-05 TBSEL: 7.2764e-05
Join resc: 12268 resp:12268
OPTIMIZER PERCENT INDEX CACHING = 70
Access path: index (scan)
INDEX#: 57473 TABLE: PS_VOUCHER
CST: -4659 IXSEL: 1.5767e-01 TBSEL: -5.5476e-02
Join resc: 2167 resp:2167
OPTIMIZER PERCENT INDEX CACHING = 70
Access path: index (eq-unique)
INDEX#: 57470 TABLE: PS_VOUCHER
CST: 1 IXSEL: 0.0000e+00 TBSEL: 0.0000e+00
Join resc: 12268 resp:12268
Join cardinality: 3185 = outer (20203) * inner (13743) * sel (1.1472e-05)
[flag=0]
Using index (ndv = 87165 sel = -2.3817e-05)
Best NL cost: 2167 resp: 2167
SM Join
Outer table:
resc: 2166 cdn: 20203 rcz: 121 deg: 1 resp: 2166
Inner table: PS_VOUCHER
resc: 476 cdn: 13743 rcz: 46 deg: 1 resp: 476
SORT resource Sort statistics
Sort width: 5 Area size: 2125824 Degree: 1
Blocks to Sort: 103 Row size: 61 Rows: 13743
Initial runs: 1 Merge passes: 1 Cost / pass:
124
Total sort cost: 114
Merge join Cost: 2756 Resp: 2756
HA Join
Outer table:
resc: 2166 cdn: 20203 rcz: 121 deg: 1 resp: 2166
Inner table: PS_VOUCHER
resc: 476 cdn: 13743 rcz: 46 deg: 1 resp: 476
Hash join one ptn: 27 Deg: 1 (sides swapped)
hash_area: 384 buildfrag: 385 probefrag: 329
ppasses: 2
Hash join Resc: 2669 Resp: 2669
Join result: cost: 2167 cdn: 3185 rcz: 167
Best so far: TABLE#: 1 CST: 2166 CDN: 20203 BYTES: 2444563
Best so far: TABLE#: 0 CST: 2167 CDN: 3185 BYTES: 531895
Final:
CST: 2167 CDN: 3185 RSC: 2167 RSP: 2167 BYTES: 531895
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Help understanding CBO plan
