11g 等頻直方圖下sql不走索引掃描

昨天下午開發人員報告一條非常奇怪的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
----------------------------------------------------------
          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.00
Index 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.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: 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重建，此時表中只有幾十萬條記錄，可以自動選擇索引；
登入資料庫檢查一下，該列上的等頻直方圖已經消失， 失去了一個驗證的好機會