SQL缓存失效问题排查

🧠 一、什么是 OPTIMIZER_MISMATCH？

Oracle 在决定是否共享执行计划时，会检查许多属性是否一致，其中之一就是 优化器环境（Optimizer Environment）。

当：

  • 某些初始化参数（如 _optimizer_*、optimizer_mode 等）、
  • SESSION 级 Hint、
  • ALTER SESSION 命令

在执行同一条 SQL 时发生变化，就会导致 Optimizer Mismatch，进而创建新的 child cursor，导致缓存失效。

🧪 二、常见触发原因

原因 示例
optimizer_mode 不一致 如某些会话使用 ALL_ROWS，某些用 FIRST_ROWS
并行相关设置（parallel_degree）不同 一些 SQL 有并行 Hint，一些没有
session 级优化器 hint 使用不一致 如 /*+ FULL(t) */ vs 无 hint
隐藏参数（如 _optimizer_use_feedback）导致 12c+ 中反馈优化器相关
SQL 使用 outline，不同版本 Oracle 可能尝试多版本计划（plan evolution）

✅ 三、如何处理 OPTIMIZER_MISMATCH

方式 1：强制统一优化器设置

确保执行同一 SQL 的所有会话，优化器环境一致。

例如，所有 session 连接数据库后执行统一设置：

1
2
3
sql
复制编辑
ALTER SESSION SET optimizer_mode = ALL_ROWS;

如果你使用连接池（如 WebLogic、Druid、Tomcat），可以在连接创建时设置：

1
2
3
sql
复制编辑
ALTER SESSION SET optimizer_features_enable = '19.1.0';

方式 2：消除 Hint 差异

确保开发不要对相同 SQL 写入不同的优化器 Hint：

1
2
3
4
5
6
7
sql
复制编辑
-- 错误用法
SELECT /*+ FULL(t) */ * FROM t;
SELECT * FROM t;

-- 优化方法：统一 Hint 或改为绑定变量方案

方式 3：固定执行计划（使用 SQL Plan Baseline）

如果你发现某个 SQL 反复因优化器设置不同而生成多计划，可以 固定一个最优执行计划，让 Oracle 只使用它：

步骤如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sql
复制编辑
-- 1. 捕获当前执行计划作为候选
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '你的SQL_ID');

-- 2. 查看并启用计划
SELECT * FROM dba_sql_plan_baselines WHERE sql_text LIKE '%你的SQL%';

-- 3. 接着用以下命令固定计划
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
     sql_handle => 'SQL_XXX',
     plan_name  => 'PLAN_XXX',
     attribute_name => 'ENABLED',
     attribute_value => 'YES');

方式 4：设置共享游标策略（风险控制）

在某些情况下你可以设置：

1
2
3
sql
复制编辑
ALTER SYSTEM SET cursor_sharing = FORCE;

但该方法可能会影响系统中其他依赖字面量精准匹配的 SQL，因此需谨慎。

🔒 四、如何确认问题已解决？

重复执行以下 SQL，确认 child cursor 不再持续增长：

1
2
3
4
5
6
sql
复制编辑
SELECT sql_id, COUNT(*) AS child_count
  FROM v$sql
 WHERE sql_id = '你的SQL_ID'
GROUP BY sql_id;

若 child_count 不再增加，说明共享成功。

🧾 五、建议你建立一套监控逻辑（可每日巡检）

可以定期运行如下 SQL，找出因 OPTIMIZER_MISMATCH 失效的 SQL：

1
2
3
4
5
6
7
sql
复制编辑
SELECT sql_id, COUNT(*) child_count
  FROM v$sql_shared_cursor
 WHERE optimizer_mismatch = 'Y'
GROUP BY sql_id
HAVING COUNT(*) > 1;