为什么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一下这个表吗？
有什么办法能在程序层面解决这个问题，我的目的是希望能做到尽量不用系统管理员干预就能取得稳定的性能。
