昨天下午开发人员报告一条非常奇怪的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 traceonly
SQL> 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
----------------------------------------------------------
1recursive calls
0db block gets
643568consistent gets
0physical reads
0redo size
7434bytes sent via SQL*Net to client
519bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (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
----------------------------------------------------------
1recursive calls
0db block gets
5consistent gets
0physical reads
0redo size
7441bytes sent via SQL*Net to client
519bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
使用10053跟踪一把
另：11g后的生成的跟踪文件跟10g变化很大，不再是生成以pid为标志的文件，可以通过查询v$diag_info获取生成的跟踪文件。
以下是10053的摘录，CBO通过计算比较，发现使用全表扫描的cost要比索引扫描小，所以选择全表扫描；
但是density居然为0.5，这是一个非常错误的值，而oracle是如何得来的？
***********************
Table Stats::
Table: DOC_JUSTINAlias: DOC_JUSTIN
#Rows: 14094996#Blks:644512AvgRowLen:324.00ChainCnt:0.00
Index Stats::
Index: IDX_JUSTIN_1Col#: 78
LVLS: 2#LB: 33266#DK: 56LB/K: 594.00DB/K: 11395.00CLUF: 638142.00
Index: IDX_JUSTIN_2Col#: 2
LVLS: 2#LB: 38100#DK: 14114520LB/K: 1.00DB/K: 1.00CLUF: 1923680.00
Index: IDX_JUSTIN_3Col#: 45
LVLS: 0#LB: 0#DK: 0LB/K: 0.00DB/K: 0.00CLUF: 0.00
Index: IDX_JUSTIN_4Col#: 34 5 3
LVLS: 2#LB: 46950#DK: 78LB/K: 601.00DB/K: 8181.00CLUF: 638147.00
Index: PK_DOC_JUSTINCol#: 1
LVLS: 2#LB: 28243#DK: 14134692LB/K: 1.00DB/K: 1.00CLUF: 668126.00
Access 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: 1UncompBkts: 5548EndPtVals: 1
Using density: 0.500000 of col #2 as selectivity of unpopular value pred
Table: DOC_JUSTINAlias: DOC_JUSTIN
Card: Original: 14094996.000000Rounded: 7047498Computed: 7047498.00Non Adjusted: 7047498.00
Access Path: TableScan
Cost:175559.75Resp: 175559.75Degree: 0
Cost_io: 174557.00Cost_cpu: 20094349137
Resp_io: 174557.00Resp_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.00resc_cpu: 22017327324
ix_sel: 0.500000ix_sel_with_filters: 0.500000
Cost: 981990.71Resp: 981990.71Degree: 1
Best:: AccessPath: TableScan
Cost: 175559.75Degree: 1Resp: 175559.75Card: 7047498.00Bytes: 0
Google一把，找到一篇非常好的参考文档
http://www.adellera.it/investigations/11g_newdensity/11gNewDensity.pdf
原来从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重建，此时表中只有几十万条记录，可以自动选择索引；
登录数据库检查一下，该列上的等频直方图已经消失， 失去了一个验证的好机会
|
