oracle 执行计划禁止索引,执行计划变了，不走索引走全表扫描了。

今天一测试库上的一条sql说是突然变慢。查看执行计划，在pl/sql 工具中按 F5 键要转半天才能出来，感觉不正常。把最后排序的那行注释掉执行计划就会很快出来。

sql内容如下，其中from后的a和b都是视图。

SELECT   a.trs_serverid || 'A' || a.trs_recordaddr AS zhuanlih,

b.image_name AS image_name, a.thumbprefix || a.pathid AS image_path,

b.image_desc AS NAME, a.simplename, a.datatype, a.docnumber,

a.applyid_o AS shenqingh

FROM t_bib_info_cr a, t_bib_image_cr b

WHERE a.did = b.docid

AND a.trs_serverid || 'A' || a.trs_recordaddr = '0A696591'

ORDER BY a.simplename ASC, b.image_name ASC

正常的执行计划如下，不正常的执行计划最后2行都是全表扫描。

-------------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |                           |    10 | 50270 |    19   (6)| 00:00:01 |

|   1 |  SORT ORDER BY                  |                           |    10 | 50270 |    19   (6)| 00:00:01 |

|   2 |   NESTED LOOPS                  |                           |    10 | 50270 |    18   (0)| 00:00:01 |

|   3 |    VIEW                         | T_BIB_INFO_CR             |     2 |  6012 |     6   (0)| 00:00:01 |

|   4 |     UNION-ALL                   |                           |       |       |            |          |

|   5 |      TABLE ACCESS BY INDEX ROWID| T_BIB_INFO                |     1 |    90 |     4   (0)| 00:00:01 |

|*  6 |       INDEX RANGE SCAN          | IN_ZHUANLIH_T_BIB_INFO_CR |     1 |       |     3   (0)| 00:00:01 |

|   7 |      TABLE ACCESS BY INDEX ROWID| QK_MO_ZLX                 |     1 |    65 |     2   (0)| 00:00:01 |

|*  8 |       INDEX RANGE SCAN          | IN_ZHUANLIH_QK_MO_ZLX     |     1 |       |     1   (0)| 00:00:01 |

|   9 |    VIEW                         | T_BIB_IMAGE_CR            |     1 |  2021 |     6   (0)| 00:00:01 |

|  10 |     UNION ALL PUSHED PREDICATE  |                           |       |       |            |          |

|  11 |      TABLE ACCESS BY INDEX ROWID| T_BIB_IMAGE               |     5 |   135 |     4   (0)| 00:00:01 |

|* 12 |       INDEX RANGE SCAN          | PK_IMAGE                  |     5 |       |     3   (0)| 00:00:01 |

|  13 |      TABLE ACCESS BY INDEX ROWID| QK_MO_TXXX                |     8 |   136 |     2   (0)| 00:00:01 |

|* 14 |       INDEX RANGE SCAN          | INDEX_DOCID_QK_MO_TXXX    |     8 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------

原因：今天这个sql涉及的表做了重新导入，估计是用imp

解决：重新收集这4个表的统计信息：

SQL> exec dbms_stats.gather_table_stats('SCOTT','table_name',NO_INVALIDATE=>FALSE);