烦人的"Recost for ORDER BY"

mihawk · 发表于 2008-3-20 11:42

起因是一个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 编辑 ]