Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Help understanding CBO plan

Help understanding CBO plan

From: <huy-luan.le_at_amecspie.com>
Date: Tue, 26 Oct 2004 10:23:43 +0200
Message-ID: <OFF29C628E.8E4D695A-ONC1256F39.002CCAF1-C1256F39.002E1DF9@amecspie.com>

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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 26 2004 - 03:21:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US