本文共 5224 字,大约阅读时间需要 17 分钟。
昨天下午开发人员报告一条非常奇怪的sql,使用该sql的执行计划使用全部扫描,但是查询列是建有索引的,oracle版本 11.2.0.2该sql格式如下 select * from doc_JUSTIN where do_no ='0069325106';当时第一反应是该列数据分布不均匀,但是查询后发现该列选择性极佳SQL> select count(*),count(distinct do_no) from doc_JUSTIN;
COUNT(*) COUNT(DISTINCTDO_NO)---------- -------------------- 14037996 14037996检查统计信息,表和索引都是最近收集的,且该索引并没有失效,这就比较奇怪了;使用autotrace查看执行计划SQL> set autotrace traceonlySQL> select * from doc_JUSTIN where do_no ='0069325106';
Execution Plan----------------------------------------------------------Plan hash value: 1702056455-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7047K| 2177M| 175K (1)| 00:35:07 ||* 1 | TABLE ACCESS FULL| DOC_JUSTIN | 7047K| 2177M| 175K (1)| 00:35:07 |-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
1 - filter("DO_NO"='0069325106')
Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 643568 consistent gets 0 physical reads 0 redo size 7434 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)1 rows processed添加hint,强制走索引,虽然consistent gets值下降为5,但是rows却为7047K,和全表扫描的一样,看来应是CBO的问题了SQL> select /*+ index(doc_JUSTIN,IDX_JUSTIN_2) */ * from doc_JUSTIN where do_no ='0069325106'; Execution Plan----------------------------------------------------------Plan hash value: 1340941743-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7047K| 2177M| 961K (1)| 03:12:19 || 1 | TABLE ACCESS BY INDEX ROWID| DOC_JUSTIN | 7047K| 2177M| 961K (1)| 03:12:19 ||* 2 | INDEX RANGE SCAN | IDX_JUSTIN_2 | 7047K| | 19027 (1)| 00:03:49 |-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("DO_NO"='0069325106')
Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 7441 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
使用10053跟踪一把另:11g后的生成的跟踪文件跟10g变化很大,不再是生成以pid为标志的文件,可以通过查询v$diag_info获取生成的跟踪文件。以下是10053的摘录,CBO通过计算比较,发现使用全表扫描的cost要比索引扫描小,所以选择全表扫描;但是density居然为0.5,这是一个非常错误的值,而oracle是如何得来的?***********************Table Stats:: Table: DOC_JUSTIN Alias: DOC_JUSTIN #Rows: 14094996 #Blks: 644512 AvgRowLen: 324.00 ChainCnt: 0.00Index Stats:: Index: IDX_JUSTIN_1 Col#: 78 LVLS: 2 #LB: 33266 #DK: 56 LB/K: 594.00 DB/K: 11395.00 CLUF: 638142.00 Index: IDX_JUSTIN_2 Col#: 2 LVLS: 2 #LB: 38100 #DK: 14114520 LB/K: 1.00 DB/K: 1.00 CLUF: 1923680.00 Index: IDX_JUSTIN_3 Col#: 45 LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00 Index: IDX_JUSTIN_4 Col#: 34 5 3 LVLS: 2 #LB: 46950 #DK: 78 LB/K: 601.00 DB/K: 8181.00 CLUF: 638147.00 Index: PK_DOC_JUSTIN Col#: 1 LVLS: 2 #LB: 28243 #DK: 14134692 LB/K: 1.00 DB/K: 1.00 CLUF: 668126.00Access path analysis for DOC_JUSTIN***************************************SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for DOC_JUSTIN[DOC_JUSTIN] Column (#2): NewDensity:0.500000, OldDensity:0.000000 BktCnt:5548, PopBktCnt:5548, PopValCnt:1, NDV:205 Column (#2): DO_NO( AvgLen: 11 NDV: 205 Nulls: 0 Density: 0.500000 Histogram: Freq #Bkts: 1 UncompBkts: 5548 EndPtVals: 1 Using density: 0.500000 of col #2 as selectivity of unpopular value pred Table: DOC_JUSTIN Alias: DOC_JUSTIN Card: Original: 14094996.000000 Rounded: 7047498 Computed: 7047498.00 Non Adjusted: 7047498.00 Access Path: TableScan Cost: 175559.75 Resp: 175559.75 Degree: 0 Cost_io: 174557.00 Cost_cpu: 20094349137 Resp_io: 174557.00 Resp_cpu: 20094349137 Using density: 0.500000 of col #2 as selectivity of unpopular value pred Access Path: index (AllEqRange) Index: IDX_JUSTIN_2 resc_io: 980892.00 resc_cpu: 22017327324 ix_sel: 0.500000 ix_sel_with_filters: 0.500000 Cost: 981990.71 Resp: 981990.71 Degree: 1 Best:: AccessPath: TableScan Cost: 175559.75 Degree: 1 Resp: 175559.75 Card: 7047498.00 Bytes: 0 Google一把,找到一篇非常好的参考文档原来从11.1.0.6开始,当有直方图时,CBO开始使用新算法计算density,不再使用数据字典dba_tab_columns中储存的density值;该功能由隐含参数_optimizer_enable_density_improvements决定,当为true时开启当为等频直方图时,newdensity = 0.5/numrows,但是该表有千万条数据,按说newdensity值应该非常小才对,不知为何会计算出值为0.5;这应该是导致sql选择全表扫描的原因。解决方案应该很简单,要么去除该列上的直方图信息,要么设置_optimizer_enable_density_improvements为false;本来是说要今早执行的,但是早上来到后开发说问题已经解决了,他们把该表drop重建,此时表中只有几十万条记录,可以自动选择索引;登录数据库检查一下,该列上的等频直方图已经消失, 失去了一个验证的好机会来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-708513/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-708513/