首页 新闻 赞助 找找看

为什么sql执行计划没有走索引

0
悬赏园豆:30 [待解决问题]

.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中,发现其执行计划如下:

ADDRESS  HASH_VALUE SQL_ID        PLAN_HASH_VALUE CHILD_ADDRESS CHILD_NUMBER TIMESTAMP   OPERATION                                                    OPTIONS                                                      OBJECT_NODE                                                                         OBJECT# OBJECT_OWNER                   OBJECT_NAME                    OBJECT_ALIAS                                                      OBJECT_TYPE                              OPTIMIZER                                        ID  PARENT_ID      DEPTH   POSITION SEARCH_COLUMNS       COST CARDINALITY      BYTES OTHER_TAG                                                              PARTITION_START PARTITION_STOP PARTITION_ID OTHER                                                                            DISTRIBUTION                               CPU_COST    IO_COST TEMP_SPACE ACCESS_PREDICATES                                                                FILTER_PREDICATES                                                                PROJECTION                                                                             TIME QBLOCK_NAME                    REMARKS                                                                          OTHER_XML
-------- ---------- ------------- --------------- ------------- ------------ ----------- ------------------------------------------------------------ ------------------------------------------------------------ -------------------------------------------------------------------------------- ---------- ------------------------------ ------------------------------ ----------------------------------------------------------------- ---------------------------------------- ---------------------------------------- ---------- ---------- ---------- ---------- -------------- ---------- ----------- ---------- ---------------------------------------------------------------------- --------------- -------------- ------------ -------------------------------------------------------------------------------- ---------------------------------------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2CD61FBC 2385429608 g6qnd0272xj38        98068844 2A7277A0                 0 2012-10-16  SELECT STATEMENT                                                                                                                                                                                                                                                                                                                                                                               ALL_ROWS                                          0                     0       2253              0       2253                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
2CD61FBC 2385429608 g6qnd0272xj38        98068844 2A7277A0                 0 2012-10-16  COUNT                                                        STOPKEY                                                                                                                                                                                                                                                                                                                                                                             1          0          1          1              0                                                                                                                                                                                                                                                                                                                                                                                                  ROWNUM<=:MAXROW                                                                  "C1"[VARCHAR2,20], "C2"[VARCHAR2,20], "C3"[VARCHAR2,20], "C4"[VARCHAR2,20], "C5"            SEL$1                                                                                                           <other_xml><info type="db_version">10.2.0.1</info><info type="parse_schema"><![C
2CD61FBC 2385429608 g6qnd0272xj38        98068844 2A7277A0                 0 2012-10-16  VIEW                                                                                                                                                                                                                                                                                from$_subquery$_001@SEL$1                                                                                                                                    2          1          2          1              0       2253       23460    5935380                                                                                                                                                                                                                                               364108518       2190                                                                                                                                                                              "C1"[VARCHAR2,20], "C2"[VARCHAR2,20], "C3"[VARCHAR2,20], "C4"[VARCHAR2,20], "C5"         28 SEL$2                                                                                                           
2CD61FBC 2385429608 g6qnd0272xj38        98068844 2A7277A0                 0 2012-10-16  SORT                                                         ORDER BY STOPKEY                                                                                                                                                                                                                                                                                                                                                                    3          2          3          1              0       2253       23460    2604060                                                                                                                                                                                                                                               364108518       2190    7545000                                                                                  ROWNUM<=:MAXROW                                                                  (#keys=1) INTERNAL_FUNCTION("RT")[11], "C1"[VARCHAR2,20], "C2"[VARCHAR2,20], "C3         28 SEL$2                                                                                                           
2CD61FBC 2385429608 g6qnd0272xj38        98068844 2A7277A0                 0 2012-10-16  TABLE ACCESS                                                 FULL                                                                                                                                               51376 TEST                           T2                             T2@SEL$2                                                          TABLE                                                                                      4          3          4          1              0       1656       23460    2604060                                                                                                                                                                                                                                               333511912       1599                                                                                             "RT">:LASTTIME                                                                   "C1"[VARCHAR2,20], "C2"[VARCHAR2,20], "C3"[VARCHAR2,20], "C4"[VARCHAR2,20], "C5"         20 SEL$2                                                                                                           
 

是用了全表扫描,然后排序。

但我在sqlplus中直接执行拼接的sql语句,就会走索引,很快返回结果。

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    ( 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

其执行计划如下:

ADDRESS  HASH_VALUE SQL_ID        PLAN_HASH_VALUE CHILD_ADDRESS CHILD_NUMBER TIMESTAMP   OPERATION                                                    OPTIONS                                                      OBJECT_NODE                                                                         OBJECT# OBJECT_OWNER                   OBJECT_NAME                    OBJECT_ALIAS                                                      OBJECT_TYPE                              OPTIMIZER                                        ID  PARENT_ID      DEPTH   POSITION SEARCH_COLUMNS       COST CARDINALITY      BYTES OTHER_TAG                                                              PARTITION_START PARTITION_STOP PARTITION_ID OTHER                                                                            DISTRIBUTION                               CPU_COST    IO_COST TEMP_SPACE ACCESS_PREDICATES                                                                FILTER_PREDICATES                                                                PROJECTION                                                                             TIME QBLOCK_NAME                    REMARKS                                                                          OTHER_XML
-------- ---------- ------------- --------------- ------------- ------------ ----------- ------------------------------------------------------------ ------------------------------------------------------------ -------------------------------------------------------------------------------- ---------- ------------------------------ ------------------------------ ----------------------------------------------------------------- ---------------------------------------- ---------------------------------------- ---------- ---------- ---------- ---------- -------------- ---------- ----------- ---------- ---------------------------------------------------------------------- --------------- -------------- ------------ -------------------------------------------------------------------------------- ---------------------------------------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
33512994 2974804990 9vjg1sasnzuzy       418697960 2CD0B22C                 0 2012-10-16  SELECT STATEMENT                                                                                                                                                                                                                                                                                                                                                                               ALL_ROWS                                          0                     0          4              0          4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
33512994 2974804990 9vjg1sasnzuzy       418697960 2CD0B22C                 0 2012-10-16  COUNT                                                        STOPKEY                                                                                                                                                                                                                                                                                                                                                                             1          0          1          1              0                                                                                                                                                                                                                                                                                                                                                                                                  ROWNUM<=100                                                                      "C1"[VARCHAR2,20], "C2"[VARCHAR2,20], "C3"[VARCHAR2,20], "C4"[VARCHAR2,20], "C5"            SEL$1                                                                                                           <other_xml><info type="db_version">10.2.0.1</info><info type="parse_schema"><![C
33512994 2974804990 9vjg1sasnzuzy       418697960 2CD0B22C                 0 2012-10-16  VIEW                                                                                                                                                                                                                                                                                from$_subquery$_001@SEL$1                                                                                                                                    2          1          2          1              0          4           1        253                                                                                                                                                                                                                                                   29236          4                                                                                                                                                                              "C1"[VARCHAR2,20], "C2"[VARCHAR2,20], "C3"[VARCHAR2,20], "C4"[VARCHAR2,20], "C5"          1 SEL$2                                                                                                           
33512994 2974804990 9vjg1sasnzuzy       418697960 2CD0B22C                 0 2012-10-16  TABLE ACCESS                                                 BY INDEX ROWID                                                                                                                                     51376 TEST                           T2                             T2@SEL$2                                                          TABLE                                                                                      3          2          3          1              0          4           1        111                                                                                                                                                                                                                                                   29236          4                                                                                                                                                                              "C1"[VARCHAR2,20], "C2"[VARCHAR2,20], "C3"[VARCHAR2,20], "C4"[VARCHAR2,20], "C5"          1 SEL$2                                                                                                           
33512994 2974804990 9vjg1sasnzuzy       418697960 2CD0B22C                 0 2012-10-16  INDEX                                                        RANGE SCAN DESCENDING                                                                                                                              55506 TEST                           INDEX_T2_RT                    T2@SEL$2                                                          INDEX                                                                                      4          3          4          1              1          3           1                                                                                                                                                                                                                                                              21564          3            "RT">TIMESTAMP'2012-10-16 00:00:00'                                              "RT">TIMESTAMP'2012-10-16 00:00:00'                                              "T2".ROWID[ROWID,10], "RT"[TIMESTAMP,11]                                                  1 SEL$2                                                                                                           
 


这时什么原因造成的,为什么用了hint也没办法强制oracle走索引。

analyze表之后,带参数的sql也走索引了。但我发现一天以后这个毛病就又返了,难道需要管理员每天analyze一下这个表吗?

有什么办法能在程序层面解决这个问题,我的目的是希望能做到尽量不用系统管理员干预就能取得稳定的性能。

fupei101011的主页 fupei101011 | 初学一级 | 园豆:81
提问于:2012-10-17 08:20
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册