如何确保Oracle查询优化器始终为相同查询选择高效执行计划

核心优化手段 #

• 更新统计信息
  你的表每日新增30-40百万行，统计信息极易过时，这是优化器误判执行计划的常见原因。执行以下命令强制更新表和关联索引的统计信息，让优化器掌握准确的数据分布：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'MY_TABLE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE => TRUE);
  

  建议配置定时任务（比如每日凌晨）自动执行，保证统计信息实时性。

• 创建适配查询的复合索引
  当前单字段索引无法覆盖你的多条件过滤逻辑，尝试创建包含status、created、id的复合索引，并包含started及其他查询需要返回的字段（避免回表查询）：

  CREATE INDEX IDX_MY_TABLE_STATUS_CREATED_ID ON MY_TABLE (STATUS, CREATED, ID) INCLUDE (STARTED, 其他查询字段);
  

  这类复合索引能让优化器直接通过索引完成过滤和数据获取，自然会优先选择高效的执行路径。

• 用Hint强制指定执行计划
  如果前面的优化仍无法解决问题，可以直接在SQL中添加Hint，强制优化器使用基于status的索引：

  SELECT /*+ INDEX(MY_TABLE 你的status字段索引名) */ * 
  FROM MY_TABLE
  WHERE ID = 'some_id'
    AND CREATED > SYSDATE - 160/24 
    AND STARTED <= TO_TIMESTAMP('07-06-2023 06:12:45', 'dd-mm-yyyy hh24:mi:ss') 
    AND STATUS IN (3, 13, 14);
  

  若已创建复合索引，替换索引名为复合索引即可。

• 优化分区策略
  当前7天分区可调整为按created字段分区，因为你的查询有created > sysdate - 160/24（约6.7天）的过滤条件，按created分区后，优化器能直接定位到最近的分区，大幅减少扫描范围。同时搭配分区索引，进一步提升性能。

• 锁定高效执行计划
  当你确认某个执行计划稳定高效后，可通过SQL Profile或SQL Plan Baseline锁定该计划，防止优化器后续切换到低效路径：

  1. 先执行一次高效查询，获取对应的SQL_ID
  2. 创建SQL Profile：

     EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE(SQL_ID => '你的SQL_ID', NAME => 'PROFILE_MY_QUERY', FORCE_MATCH => TRUE);
     

  或者创建SQL Plan Baseline：

  DECLARE
    l_plans_loaded PLS_INTEGER;
  BEGIN
    l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => '你的SQL_ID');
  END;
  /
  

内容的提问来源于stack exchange，提问作者Rajat