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