烦人的"Recost for ORDER BY"

mihawk

起因是一个SQL走错了索引
SELECT ROWID AS rid
FROM tab_1 s
WHERE s.c_id = :1
ORDER BY s.gmt_r DESC

执行计划如下
------------------------------------------------------------------------------------------
| Id  | Operation                   |  Name                      | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |   858 | 25740 |   138K|
|*  1 |  TABLE ACCESS BY INDEX ROWID| tab_1               |   858 | 25740 |   138K|
|*  2 |   INDEX FULL SCAN DESCENDING| tab_1_RPST_ST_IND  |   605K|       |  3408 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("S"."c_id"=TO_NUMBER(:Z))
CBO走了排序字段的索引, 而不是过滤度很高的c_id字段上的索引.

10053 Trace发现的东西
***************************************
SINGLE TABLE ACCESS PATH
Column: C_ID  Col#: 4      Table: tab_1   Alias:  S
    NDV: 1854      NULLS: 0         DENS: 1.4164e-03
    HEIGHT BALANCED HISTOGRAM: #BKT: 72 #VAL: 73
Column:     STATUS  Col#: 33     Table: tab_1   Alias:  S
    NDV: 4         NULLS: 0         DENS: 5.0201e-04
    FREQUENCY HISTOGRAM: #BKT: 996 #VAL: 4
  TABLE: tab_1     ORIG CDN: 657584  ROUNDED CDN: 858  CMPTD CDN: 858
  Access path: tsc  Resc:  1868  Resp:  1868
  Access path: index (scan)
      Index: tab_1_CID_ST_GE_IND
  TABLE: tab_1
      RSC_CPU: 0   RSC_IO: 122
  IX_SEL:  1.3048e-03  TB_SEL:  1.3048e-03
  Access path: index (no sta/stp keys)
      Index: tab_1_GID_CID_ST_IND
  TABLE: tab_1
      RSC_CPU: 0   RSC_IO: 3785
  IX_SEL:  1.0000e+00  TB_SEL:  1.3048e-03
  Access path: index (no sta/stp keys)
      Index: tab_1_RPST_ST_IND
  TABLE: tab_1
      RSC_CPU: 0   RSC_IO: 138323
  IX_SEL:  1.0000e+00  TB_SEL:  9.2118e-01
  BEST_CST: 122.00  PATH: 4  Degree:  1-------------------------CBO在计算了全表扫描和各个索引的扫描成本以后找到到了正确的索引, cost是122.
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: tab_1 [ S]
ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          119 Area size:      838656 Max Area size:    41943040   Degree: 1
      Blocks to Sort:        5 Row size:           43 Rows:        858
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         17
      Total IO sort cost: 11------------------------------------------------------排序, cost为11
      Total CPU sort cost: 0
      Total Temp space used: 0
Best so far: TABLE#: 0  CST:        133  CDN:        858  BYTES:      25740
                                                       ↑---------------------------------------------------总cost为122+11=133
****** Recost for ORDER BY (using index) ************ --------------------成本重算, 噩梦的开始.
***************************************
SINGLE TABLE ACCESS PATH
  TABLE: tab_1     ORIG CDN: 657584  ROUNDED CDN: 858  CMPTD CDN: 858
  Access path: tsc  Resc:  1868  Resp:  1868
  Skip scan: ss-sel 1  andv 176430  
    ss cost 176430
    index io scan cost 3406
  Access path: index (no sta/stp keys)
      Index: tab_1_RPST_ST_IND
  TABLE: tab_1
      RSC_CPU: 0   RSC_IO: 138323
  IX_SEL:  1.0000e+00  TB_SEL:  9.2118e-01
  BEST_CST: 138323.00  PATH: 4  Degree:  1-----------------------CBO重新选择了排序字段所在的index, 尽管它的cost是13万.
***********************
Join order[1]: tab_1 [ S]
Best so far: TABLE#: 0  CST:     138323  CDN:        858  BYTES:      25740-------------best so far, 看到这几个字, 表示CBO停止了更多执行计划的尝试.
    SORT resource      Sort statistics
      Sort width:          119 Area size:      838656 Max Area size:    41943040   Degree: 1
      Blocks to Sort:        5 Row size:           43 Rows:        858
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         17
      Total IO sort cost: 11
      Total CPU sort cost: 0
      Total Temp space used: 0
Final:-------------------------------------------------------------------------final, 最终选择了cost为13万的执行计划.  
CST: 138323  CDN: 858  RSC: 138323  RSP: 138323  BYTES: 25740
  IO-RSC: 138323  IO-RSP: 138323  CPU-RSC: 0  CPU-RSP: 0
  First Rows Plan
PLAN
Cost of plan:  138323
Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT                                        0
TABLE ACCESS        tab_1    BY INDEX ROWID      1
INDEX               tab_1_RPFULL SCAN DESCEN    2    1

数据库版本是9.2.0.4, 我的问题是CBO为什么会在13万的cost和133的cost之间选择了前者.

[ 本帖最后由 mihawk 于 2008-10-20 14:52 编辑 ]

vogts

是不是直方图的统计信息不对啊？？？

mihawk

原帖由 vogts 于 2008-3-20 11:55 发表
是不是直方图的统计信息不对啊？？？

直方图的统计信息是最新的, 而且基于直方图计算出来的cost是正确的, 符合实际情况的.

justin033

attention!

mihawk

metalink查了, 是oracle的bug, id是1306032.

justin033

have fixed this bug? which the patch?

mihawk

刚刚在高人的指点下, 把_SORT_ELIMINATION_COST_RATIO 设为 1 就可以解决问题了.

paulyibinyi

学习了

battleman

学习

foxmile

原帖由 mihawk 于 2008-3-20 14:14 发表
metalink查了, 是oracle的bug, id是1306032.

怎么触发这个bug的。