如何清除Oracle中存在的不良执行计划？

问题背景 #

此前遇到的问题：对函数代码做不影响逻辑的形式化修改（甚至只是增删空格），都会严重影响函数性能。Jon Heller对此给出了解释：

如果修改代码空格会改变性能，这很可能是计划管理问题。许多Oracle调优工具基于SQL_ID运行，它类似于SQL文本的MD5哈希值。因此，只要修改SQL文本中的一个字符，优化器就会将其视为全新语句。任何计划管理修复措施（如SQL profile或plan outline）都不会应用于新语句。可能DBA曾用/*+ INDEX... */提示调优旧语句，但该提示不会延续到新语句。请对比DBMS_XPLAN输出中的Note部分。

SQL语句中的空格会改变SQL_ID，这可能导致优化器无法将该语句与计划管理功能（如profiles、outlines、baselines，理论上它们应能在某些情况下避免此问题）、补丁、高级重写等匹配。

当前核心问题：如何清除Oracle中存在的不良执行计划？

常用清理方法 #

• 精准清除共享池中的单个游标
  避免使用ALTER SYSTEM FLUSH SHARED_POOL（会清空整个共享池，影响业务），针对特定SQL_ID的游标执行清除：

  BEGIN
    DBMS_SHARED_POOL.PURGE('<SQL_ID>,<CHILD_NUMBER>', 'C');
  END;
  /
  

  其中SQL_ID和CHILD_NUMBER可通过查询V$SQL视图获取。

• 删除绑定的SQL Profile
  若不良计划关联了SQL Profile，执行以下语句删除：

  BEGIN
    DBMS_SQLTUNE.DROP_SQL_PROFILE(name => '<PROFILE_NAME>');
  END;
  /
  

  可通过DBA_SQL_PROFILES视图查询所有SQL Profile的匹配信息。

• 移除SQL Plan Baseline

  1. 先查询目标语句对应的基线信息：

     SELECT SQL_HANDLE, PLAN_NAME FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%<目标语句特征>%';
     

  2. 删除指定基线：

     BEGIN
       DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '<SQL_HANDLE>', plan_name => '<PLAN_NAME>');
     END;
     /
     

• 清理SQL Plan Outline
  若使用了Outline，执行以下语句删除：

  BEGIN
    DBMS_OUTLN.DROP_OUTLINE('<OUTLINE_NAME>');
  END;
  /
  

  相关Outline信息可通过DBA_OUTLINES视图查询。

• 绑定正确执行计划（可选）
  清理不良计划后，建议为目标语句固定良好的执行计划，防止再次生成低效计划：

  BEGIN
    DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '<SQL_ID>', plan_hash_value => '<良好计划HASH值>');
  END;
  /
  

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