一个简单的sql为什么没走索引。

在程序中使用带参数的函数访问oracle 数据库，查看执行计划发现做了全表扫描。但是如果我使用拼接字符串的方式在程序中访问，或者直接在sqlplus中执行，查看其执行计划确实走了索引。
这是怎么回事？.net程序访问oracle数据库中的表T2，其中RT字段为timestamp类型，建立normal索引。
 
代码中使用了带参数的sql语句：
 
select C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 ,  C7 ,  C8 ,  C9 ,  C10 , 
 C11 ,  C12 ,  C13 ,  C14 ,  C15 ,  C16 ,  C17 ,  C18 ,  C19 , 
 C20 ,  RT from    ( select  /*+index(T2 ,RT )*/C1 ,  C2 , 
 C3 ,  C4 ,  C5 ,  C6 ,  C7 ,  C8 ,  C9 ,  C10 ,  C11 ,  C12 , 
 C13 ,  C14 ,  C15 ,  C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  RT 
 from T6 where RT>:lastTime   order by RT desc )  where rownum<=:maxRow  
发现执行时间非常长，大约4分钟。
在v$sql_plan中
是用了全表扫描，然后排序。
 
但我在sqlplus中直接执行拼接的sql语句，就会走索引，很快返回结果。
 
select   C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 , 
 C7 ,  C8 ,  C9 ,  C10 ,  C11 ,  C12 ,  C13 ,  C14 ,  C15 ,  
C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  
 RT from    ( select    C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 ,  C7 ,
  C8 ,  C9 ,  C10 ,  C11 ,  C12 ,  C13 ,  C14 , 
  C15 ,  C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  RT  from test.T2 
where RT>to_date('2012-10-16','yyyy-mm-dd')   order by RT desc )  where rownum<= 100  
这时什么原因造成的，带参数的sql为什么用了hint也没办法强制oracle走索引。
 
analyze表之后，带参数的sql也走索引了。但我发现一天以后这个毛病就又返了，难道需要管理员每天analyze一下这个表吗？
 
有什么办法能在程序层面解决这个问题，我的目的是希望能做到尽量不用系统管理员干预就能取得稳定的性能。

解决方案 »

  1.   

    走不走索引是由Oracle自己决定的。Oracle做决定是基于统计信息的。用绑定变量，传入变量之前，SQL执行计划就必须已经就绪了，所以可能会出现判断错误的结果。自己拼接SQL，Oracle解析的时候会判断这个变量，根据统计信息决定走什么样的执行计划。还有，如果索引维护出现问题，使用索引可能会得出和常规查询不一样的(错误的)结果，这时无论如何都不会走索引。如果“analyze表之后，带参数的sql也走索引了。但我发现一天以后这个毛病就又返了”说明你的这个表每天都会发生不规范的变化，破坏了索引。
      

  2.   

    /*+INDEX（TABLE INDEX_NAME）*/
    才是index hint的用法。
      

  3.   

    你说的是滴，hint的用法用错了。
    但是不用hint为什么就走索引呢。
      

  4.   

    做下10053 TRACE事件，看看什么让你的SQL语句再数据库端执行的时候执行计划变了
      

  5.   

    我做了一下10053事件，但是还是没看明白，为什么CBO认为走索引需要用更高的代价。大家给分析分析。带参数的sql，cost显然应该跟参数的取值是有关系的。而这个执行计划似乎应该是静态的，不会因为每次执行的sql的参数值不一样而重新改变。那CBO是估计一个参数取值来确定cost的吗。我看了一下，这个表的CLUF: 542441.00。这个值却是很大，比较接近记录数  #Rows: 671932 。  但是我不明白，那为什么拼出来的sql语句就无视这个cluf呢。下面把带参数的sql的trace贴出来。
    ******************************************
    Current SQL statement for this session:
     select  C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 ,  C7 ,  C8 ,  C9 ,  C10 ,  C11 ,  C12 ,  C13 ,  C14 ,  C15 ,  C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  RT from    ( select    C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 ,  C7 ,  C8 ,  C9 ,  C10 ,  C11 ,  C12 ,  C13 ,  C14 ,  C15 ,  C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  RT  from T6 where RT>:lastTime    order by RT desc )  where rownum<=:maxRow 
       ***************************************
    BASE STATISTICAL INFORMATION
    ***********************
    Table Stats::
      Table: T6  Alias: T6
        #Rows: 671932  #Blks:  10733  AvgRowLen:  111.00
    Index Stats::
      Index: INDEX_T6_RT  Col#: 21
        LVLS: 2  #LB: 1768  #DK: 670274  LB/K: 1.00  DB/K: 1.00  CLUF: 542441.00
    ***************************************
    SINGLE TABLE ACCESS PATH
      Column (#21): RT(TIMESTAMP)
        AvgLen: 11.00 NDV: 671932 Nulls: 0 Density: 1.4882e-006 Min: 2456165 Max: 2456220
        Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 255
      Table: T6  Alias: T6     
        Card: Original: 671932  Rounded: 33597  Computed: 33596.60  Non Adjusted: 33596.60
      Access Path: TableScan
        Cost:  2432.41  Resp: 2432.41  Degree: 0
          Cost_io: 2350.00  Cost_cpu: 479593616
          Resp_io: 2350.00  Resp_cpu: 479593616
      Access Path: index (RangeScan)
        Index: INDEX_T6_RT
        resc_io: 4913.00  resc_cpu: 39523635
        ix_sel: 0.0090223  ix_sel_with_filters: 0.0090223
        Cost: 4919.79  Resp: 4919.79  Degree: 1
      Best:: AccessPath: TableScan
             Cost: 2432.41  Degree: 1  Resp: 2432.41  Card: 33596.60  Bytes: 0
    ***************************************
    OPTIMIZER STATISTICS AND COMPUTATIONS
    ***************************************
    GENERAL PLANS
    ***************************************
    Considering cardinality-based initial join order.
    ***********************
    Join order[1]:  T6[T6]#0
    ORDER BY sort
        SORT resource      Sort statistics
          Sort width:         227 Area size:      199680 Max Area size:    40054784
          Degree:               1
          Blocks to Sort:     548 Row size:          133 Total Rows:          33597
          Initial runs:         2 Merge passes:        1 IO Cost / pass:        298
          Total IO sort cost: 846      Total CPU sort cost: 42074783
          Total Temp space used: 10806000
    ***********************
    Best so far: Table#: 0  cost: 3285.6384  card: 33596.6000  bytes: 3729267
    *********************************
    Number of join permutations tried: 1
    *********************************
        SORT resource      Sort statistics
          Sort width:         227 Area size:      199680 Max Area size:    40054784
          Degree:               1
          Blocks to Sort:     548 Row size:          133 Total Rows:          33597
          Initial runs:         2 Merge passes:        1 IO Cost / pass:        298
          Total IO sort cost: 846      Total CPU sort cost: 42074783
          Total Temp space used: 10806000
    Final - All Rows Plan:  Best join order: 1
      Cost: 3285.6384  Degree: 1  Card: 33597.0000  Bytes: 3729267
      Resc: 3285.6384  Resc_io: 3196.0000  Resc_cpu: 521668398
      Resp: 3285.6384  Resp_io: 3196.0000  Resc_cpu: 521668398
    kkoipt: Query block SEL$2 (#0)
    ******* UNPARSED QUERY IS *******
    SELECT /*+ NO_STAR_TRANSFORMATION NO_EXPAND */ "T6"."C1" "C1","T6"."C2" "C2","T6"."C3" "C3","T6"."C4" "C4","T6"."C5" "C5","T6"."C6" "C6","T6"."C7" "C7","T6"."C8" "C8","T6"."C9" "C9","T6"."C10" "C10","T6"."C11" "C11","T6"."C12" "C12","T6"."C13" "C13","T6"."C14" "C14","T6"."C15" "C15","T6"."C16" "C16","T6"."C17" "C17","T6"."C18" "C18","T6"."C19" "C19","T6"."C20" "C20","T6"."RT" "RT" FROM "TEST"."T6" "T6" WHERE "T6"."RT">:B1 ORDER BY "T6"."RT" DESC
    kkoqbc-end
              : call(in-use=57448, alloc=0), compile(in-use=54392, alloc=0)
    kkoqbc-start
                : call(in-use=56080, alloc=0), compile(in-use=56468, alloc=0)
    ****************
    QUERY BLOCK TEXT
    ****************
    select  C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 ,  C7 ,  C8 ,  C9 ,  C10 ,  C11 ,  C12 ,  C13 ,  C14 ,  C15 ,  C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  RT from    ( select    C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 ,  C7 ,  C8 ,  C9 ,  C10 ,  C11 ,  C12 ,  C13 ,  C14 ,  C15 ,  C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  RT  from T6 where RT>:lastTime    order by RT desc )  where rownum<=:maxRow 
     
    ============
    Plan Table
    ============
    -------------------------------------------+-----------------------------------+
    | Id  | Operation                | Name    | Rows  | Bytes | Cost  | Time      |
    -------------------------------------------+-----------------------------------+
    | 0   | SELECT STATEMENT         |         |       |       |  3286 |           |
    | 1   |  COUNT STOPKEY           |         |       |       |       |           |
    | 2   |   VIEW                   |         |   33K | 8301K |  3286 |  00:00:40 |
    | 3   |    SORT ORDER BY STOPKEY |         |   33K | 3642K |  3286 |  00:00:40 |
    | 4   |     TABLE ACCESS FULL    | T6      |   33K | 3642K |  2432 |  00:00:30 |
    -------------------------------------------+-----------------------------------+
      

  6.   


    select  C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 ,  C7 ,  C8 ,  C9 ,  C10 ,  C11 ,  C12 ,  C13 ,  C14 ,  C15 ,  C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  RT
    from    ( select    C1 ,  C2 ,  C3 ,  C4 ,  C5 ,  C6 ,  C7 ,  C8 ,  C9 ,  C10 ,  C11 ,  C12 ,  C13 ,  C14 ,  C15 ,  C16 ,  C17 ,  C18 ,  C19 ,  C20 ,  RT
    from test.T1 where RT>to_date('2012-10-19','yyyy-mm-dd')   order by RT desc )  where rownum<=100trace上面这条语句 发现他走索引cost居然只有4。这又是为什么啊。***************************************
    SINGLE TABLE ACCESS PATH
      Column (#21): RT(TIMESTAMP)
        AvgLen: 11.00 NDV: 671747 Nulls: 0 Density: 1.4887e-006 Min: 2456165 Max: 2456220
        Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 255
      Using prorated density: 1.4884e-006 of col #21 as selectivity of out-of-range value pred
      Table: T1  Alias: T1     
        Card: Original: 671747  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
      Access Path: TableScan
        Cost:  2404.23  Resp: 2404.23  Degree: 0
          Cost_io: 2322.00  Cost_cpu: 478571071
          Resp_io: 2322.00  Resp_cpu: 478571071
      Using prorated density: 1.4884e-006 of col #21 as selectivity of out-of-range value pred
      Access Path: index (RangeScan)
        Index: INDEX_T1_RT
        resc_io: 4.00  resc_cpu: 29236
        ix_sel: 1.4906e-006  ix_sel_with_filters: 1.4906e-006
        Cost: 4.01  Resp: 4.01  Degree: 1
      Best:: AccessPath: IndexRange  Index: INDEX_T1_RT
             Cost: 4.01  Degree: 1  Resp: 4.01  Card: 1.00  Bytes: 0
      

  7.   

    你测试的时候，有没有执行 flush shared_pool ，下次的测试会不会用到了上次的解析结果？
      

  8.   

    很奇怪啊，难道不用带参数的sql而要用拼sql语句的方式吗？