FAQ
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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 26, '04 at 3:21a
activeOct 26, '04 at 3:21a
posts1
users1
websiteoracle.com

1 user in discussion

Huy-luan.le_at_amecspie.com: 1 post

People

Translate

site design / logo © 2022 Grokbase