数据库查询优化中的查询计划固化（Plan Freezing）与执行计划固定（Plan Fixation）技术

1. 问题背景

在数据库查询优化中，优化器会根据统计信息、代价模型等动态生成执行计划。但某些场景下，执行计划可能因统计信息变化、参数波动或环境因素而频繁变动，导致性能不稳定（如计划回归，Plan Regression）。例如：

• 某查询在业务低峰期使用索引扫描，高峰期却误选全表扫描；
• 版本升级后，优化器可能生成更差的新计划。

查询计划固化与执行计划固定是一类稳定性优化技术，旨在避免非预期的计划变更，确保关键查询的性能可预测性。

2. 核心概念解析

2.1 查询计划固化（Plan Freezing）

• 定义：通过手动或自动方式将某个查询的当前执行计划标记为“最优”，并禁止优化器在未来重新优化该查询。
• 特点：
  • 通常基于历史执行表现（如执行时间、资源消耗）选择需固化的计划。
  • 固化后的计划可能不再适应数据分布的变化，需定期评估。

2.2 执行计划固定（Plan Fixation）

• 定义：将特定执行计划（如通过Hint或绑定计划）强制应用于查询，优化器不再考虑其他候选计划。
• 与固化的区别：
  • 固化侧重“保留当前计划”，固定更主动“指定某个计划”。
  • 固定通常通过外部工具（如Oracle的SQL Plan Baseline、SQL Server的Plan Guide）实现。

3. 技术实现机制

3.1 计划存储与匹配

1. 计划缓存：数据库会缓存已生成的执行计划（Query Plan Cache）。
2. 计划指纹：对查询文本参数化后生成唯一标识（如SQL ID），用于匹配缓存中的计划。
3. 固定方式：
   • 手动绑定：使用数据库提供的命令（如EXEC sp_create_plan_guide in SQL Server）将现有计划绑定到查询。
   • 自动捕获：开启计划基线功能（如Oracle的OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES），自动记录历史计划，后续仅从基线中选择计划。

3.2 计划选择策略

• 优先级规则：
  1. 若存在固定计划，直接使用；
  2. 否则，从计划基线中选取代价最低的已验证计划；
  3. 若无基线，由优化器动态生成新计划。
• 验证机制：固定计划前需验证其有效性（如表结构变更后索引是否仍存在）。

4. 具体操作示例（以Oracle为例）

4.1 创建计划基线

-- 开启自动捕获  
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;  
-- 执行目标查询  
SELECT * FROM orders WHERE customer_id = 100;  
-- 关闭捕获  
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;  

此时，该查询的计划会被存入基线（DBA_SQL_PLAN_BASELINES）。

4.2 固定特定计划

-- 手动将某个计划标记为已接受（Fixed）  
DECLARE  
  v_plan_name PLS_INTEGER;  
BEGIN  
  v_plan_name := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(  
    sql_id          => 'abc123xyz',  
    plan_hash_value => 123456789,  
    fixed           => 'YES'  
  );  
END;  

此后，优化器会优先使用此固定计划。

5. 适用场景与注意事项

5.1 适用场景

• OLTP系统中参数化查询频繁执行，且要求稳定响应时间；
• 升级或迁移后需保持原有计划行为；
• 优化器因统计信息偏差常生成劣质计划。

5.2 潜在风险

• 计划僵化：数据量大幅变化后，固定计划可能失效（如索引扫描退化为全表扫描更优时）；
• 管理成本：需监控固定计划的性能，定期重新评估；
• 存储开销：大量存储计划基线可能占用系统空间。

6. 进阶优化策略

• 自适应计划固定：结合机器学习技术，根据数据变化自动调整固定策略（如周期性重新绑定计划）；
• 部分固定：仅固定查询中的关键部分（如连接顺序），其余部分仍允许优化器动态调整。

通过计划固化与固定，可在复杂环境中平衡“性能稳定性”与“灵活性”，是生产环境优化的重要补充手段。