烦人的 “Recost for ORDER BY”
作者：奶妈来了 | 分类： 大话技术 | 标签： 大话技术 | 日期：2008-03-21
起因是一个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之间选择了前者.
metalink查了, 包含 “recost for order by”的bug一大堆,8i/9i/10g都有, 回复很乱, 有的说不是bug, 有的说是重复bug, 但是给的base bug又是一个根本不搭嘎的东西, 还有的说已经修复了, id为1306032的bug情况跟上述例子最相似.
后来, 在测试库试了一下, 把_SORT_ELIMINATION_COST_RATIO 设为 1 就可以解决问题了.
SQL修改成
SELECT ROWID AS rid
FROM tab_1 s
WHERE s.c_id = :1
and s.gmt_r >sysdate-2000
ORDER BY s.gmt_r DESC
是否可以走到正确的执行计划呢?
foolishceo @ April 1, 2008 |
hey,find wow gold click here
ss @ April 10, 2008 |
http://www.alidba.net/index.php/archives/62
ss @ April 10, 2008 |