Oracle SQL计划管理（SPM）的进化与SQL计划基线（SQL Plan Baselines）的捕获/演进

在这里插入图片描述
好的，这是一个非常核心且高级的Oracle数据库性能稳定性主题。SQL计划管理（SPM）是Oracle用于解决“执行计划突变（Plan Regression）”这一顽疾的终极武器。我们将深入解析其内部机制。

第一部分：官方严谨的详细阐述

一、核心概念：为什么需要SPM？

执行计划是SQL性能的关键。计划突变（例如，一个原本使用索引的计划突然变成了全表扫描）是DBA最常见的噩梦。传统工具如存储大纲（Stored Outlines）是“指令式”的，它强制固定计划，但无法适应数据变化。SPM是“声明式”的，它的哲学是：“首先保证性能不倒退，然后谨慎地引入可能更好的新计划。”

二、 SPM的核心组件：SQL计划基线（SQL Plan Baseline）

一个SQL计划基线是一个与该SQL语句关联的一个或多个可接受执行计划的集合。它包含三个核心部分：

SQL句柄（SQL Handle）：唯一标识一个SQL语句，类似于SQL的指纹。
计划成员（Plan Members）：一个基线中可以包含多个执行计划。每个计划都有一个唯一的标识符，即 SQL计划名称（SQL Plan Name）。
计划状态（Plan Attributes）：每个计划成员都有关键属性，决定了其在基线中的角色：
- Accepted (YES/NO)：这是最重要的状态。只有 ACCEPTED=YES 的计划才是优化器可以考虑使用的计划。NO 表示该计划已被捕获但尚未被验证接受。
- Enabled (YES/NO)：是否启用。如果为 NO，优化器将完全忽略此计划。
- Fixed (YES/NO)：是否为“固定”计划。如果基线中存在任何 FIXED=YES 的计划，则优化器仅会在这些固定计划中选择，而忽略其他非固定但已接受的计划。这是控制力最强的选项。

三、 SPM的工作流程：捕获、选择、演进

1. 计划捕获（Capture）

新的计划如何进入系统？有两种方式：

自动捕获（Automatic Capture）：
- 通过将初始化参数 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 设置为 TRUE 来开启。
- 内部原理：开启后，每当数据库第一次硬解析一个SQL语句时，所生成的执行计划不会立即被使用。相反，Oracle会：
  1. 为该SQL创建一个计划基线。
  2. 将这第一个计划作为初始成员添加到基线中，但其状态为 ACCEPTED=NO。
  3. 随后，数据库会再次硬解析该SQL（是的，第二次！），这次生成的计划会与基线中的计划进行比较。
  4. 如果新计划与第一个计划相同，它会被标记为 ACCEPTED=YES。如果不同，第一个计划仍为 NO，新计划也会被加入，状态也为 NO。
- 注意：自动捕获通常只应在初始化阶段使用，因为它会增加硬解析的开销。
手动加载（Manual Loading）：
这是更常见的方式，从以下来源加载计划：
- 游标缓存（Cursor Cache）：将当前正在共享池中的优秀计划加载为基线。
```
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'g0uubmuv6c4p0'
  );
END;
```
- SQL调优集（STS - SQL Tuning Set）：从STS加载，STS可以是从AWR、游标缓存或自定义筛选的SQL集合。
- AWR仓库（AWR Repository）：直接从AWR历史快照中加载过去的好计划。
```
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_AWR(
    begin_snap => 1520,
    end_snap   => 1530
  );
END;
```

2. 计划选择（Selection）

当优化器需要为一个SQL生成执行计划时，如果该SQL存在计划基线，流程如下：

优化器照常进行硬解析或软解析，基于当前成本计算出一个新计划。
优化器在计划基线中查找是否有 ACCEPTED=YES 的计划。
如果找到：它会将新计算出的计划与基线中的每个已接受计划进行比较。
- 如果新计划与某个已接受计划相同，则使用该计划。
- 如果新计划是全新的，它会被自动添加到基线中，但状态为 ACCEPTED=NO（这就是计划捕获），然后优化器会从已接受的计划中选择一个成本最低的计划来使用。
如果未找到（即没有已接受的计划）：优化器使用它新计算出的计划。

这个过程保证了：即使优化器计算出了一个新计划，只要它不在已接受的基线中，就不会被使用，从而避免了计划突变。

3. 计划演进（Evolution） - 最精妙的环节

计划演进是SPM的大脑。它负责回答一个关键问题：那个被自动捕获、状态为 ACCEPTED=NO 的新计划，是否真的比现有的已接受计划更好？

演进不是自动发生的，需要手动或通过自动任务触发。其内部步骤如下：

创建演进任务：告诉数据库你想验证哪个非接受的计划。

-- 查找需要演进的非接受计划
SELECT sql_handle, plan_name, origin 
FROM dba_sql_plan_baselines 
WHERE accepted = 'NO';

-- 创建演进任务
VAR task_name VARCHAR2(100);
BEGIN
  :task_name := DBMS_SPM.CREATE_EVOLVE_TASK(
    sql_handle => 'SYS_SQL_7b76323ad90440b9',
    plan_name  => 'SQL_PLAN_7xqj9bhjwawmcb7f2e7d6'
  );
END;

执行验证（核心）：数据库会实际执行两个计划（通常是新计划和一个已接受的基准计划），并比较它们的性能。这可以通过两种方式：
- 执行统计信息比较：直接运行两个计划，比较它们的逻辑读（BUFFER_GETS）、CPU时间、经过时间等。
- SQL性能分析器（SPA）：更高级和可靠的方式，使用SQL Tuning Set进行更全面的比较。

生成建议报告：执行后，数据库会生成报告，明确说明新计划是否更好，以及好多少。

-- 执行演进任务
BEGIN
  DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => :task_name);
END;
/
-- 查看演进报告
SET LONG 100000
SET PAGESIZE 5000
SET LINESIZE 200
SELECT DBMS_SPM.REPORT_EVOLVE_TASK(:task_name) FROM dual;

实施决策： DBA可以审查报告后手动接受，或者配置为自动接受。
- 手动接受：
```
BEGIN
  DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_7b76323ad90440b9',
    plan_name  => 'SQL_PLAN_7xqj9bhjwawmcb7f2e7d6'
  );
END;
```
- 自动接受：如果演进任务的验证结果显示新计划的性能提升超过一定阈值（例如，性能提升1.5倍），可以配置任务自动接受该计划。

四、场景、争用与排查

场景：一个关键报表SQL的执行计划突然变差，导致性能下降。

排查：

首先检查该SQL是否有计划基线。

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin 
FROM dba_sql_plan_baselines 
WHERE sql_text LIKE '%your key SQL text%';

查看当前执行计划，并与基线中的计划比较。

-- 查看当前执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null));

-- 查看基线中的某个计划
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_7b76323ad90440b9', 
    plan_name => 'SQL_PLAN_7xqj9bhjwawmcb7f2e7d6'
  )
);

检查是否有未被接受的、可能更好的计划等待演进。

SELECT sql_handle, plan_name 
FROM dba_sql_plan_baselines 
WHERE accepted = 'NO' AND sql_text LIKE '%your key SQL text%';

争用与影响：
- 演进过程的开销：计划演进需要实际执行SQL，在生产系统高峰时段进行可能会消耗大量资源。应在维护窗口执行。
- 存储开销：大量的计划基线会占用SYSAUX表空间。
- 解析开销：优化器需要检查基线，可能会给硬解析带来极微小的额外开销。

五、常用管理SQL语句

-- 1. 查看所有SQL计划基线
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin, optimizer_cost, sql_text
FROM dba_sql_plan_baselines;

-- 2. 删除特定计划基线
BEGIN
  DBMS_SPM.DROP_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_7b76323ad90440b9',
    plan_name  => 'SQL_PLAN_7xqj9bhjwawmcb7f2e7d6'
  );
END;
/

-- 3. 修改基线属性（例如，固定一个计划）
BEGIN
  DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_7b76323ad90440b9',
    plan_name  => 'SQL_PLAN_7xqj9bhjwawmcb7f2e7d6',
    attribute_name => 'FIXED',
    attribute_value => 'YES'
  );
END;
/

-- 4. 打包并迁移计划基线（用于生产到测试库的迁移）
-- 创建STGTAB表
BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name => 'SPM_STAGING_TABLE'
  );
END;
/
-- 将基线打包到表中
BEGIN
  DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'SPM_STAGING_TABLE',
    sql_handle => 'SYS_SQL_7b76323ad90440b9'
  );
END;
/
-- 在目标库使用DBMS_SPM.UNPACK_STGTAB_BASELINE解包

第二部分：通俗易懂的解释

让我们用一个比喻来理解SPM的整个工作流程。

把Oracle优化器想象成一个米其林大厨，SQL语句就是菜谱，执行计划就是做菜的详细步骤。

计划突变：大厨今天心情好，用新方法做菜，结果做得巨难吃，客人投诉（性能下降）。
存储大纲（老方法）：餐厅经理把菜谱步骤用铁板刻下来，命令大厨必须严格按照这个步骤做。即使食材变了（数据量变了），大厨也不能调整火候，有时菜还是会不好吃。不灵活。

SQL计划管理（SPM）的新方法：

计划基线（菜谱档案馆）：
- 餐厅建立了一个“金牌菜谱档案馆”（计划基线）。馆里收藏着每个菜谱（SQL）被证明过好吃的做法（已接受的计划，ACCEPTED=YES）。
计划捕获（收集菜谱）：
- 自动捕获：经理规定，大厨每发明一种新做法，必须先交一份到档案馆待审（ACCEPTED=NO）。
- 手动加载：经理翻看历史记录，发现去年有一个做法客人评价很高，于是把这个老菜谱也收进档案馆（从AWR加载）。
计划选择（日常做菜）：
- 每天营业前，大厨还是会自己研究一下今天的食材，想想怎么做最好吃（优化器成本计算）。
- 但他必须先去档案馆查一下。如果档案馆里已经有金牌菜谱了，他就必须从金牌菜谱里选一个来做，绝不能自作主张用自己想的新方法。这保证了菜的味道下限，绝不会难吃到哪里去。
计划演进（新菜品鉴会）：
- 每周一餐厅休息，经理会举办“新菜品鉴会”（演进任务）。
- 他把大厨提交到档案馆待审的新做法（ACCEPTED=NO的计划）拿出来，让一群美食家（测试负载）亲自品尝（实际执行），和现有的金牌菜谱做对比。
- 如果美食家一致认为新做法确实更好吃（性能更好），经理就把这个新做法晋升为金牌菜谱（ACCEPTED=YES）。从此以后，大厨就可以在日常营业中使用这个新方法了。
- 如果新做法不好吃，就把它从档案馆里扔掉。