OceanBase SPM 相关 FAQ

Q1：什么是 SPM ？

SPM（SQL Plan Manager）是一种防止计划出现回退的机制。其工作原理是当一条 SQL 产生新的计划时，如果发现新生成的计划不是一个经过验证的基线计划，就会使用一个演进任务利用接下来一定的真实流量验证新的计划是否比基线计划更好。只有验证发现新的执行计划更好的情况下后续 SQL 的执行才会继续使用新的计划，否则使用基线计划。需要注意的是 SPM 本身并不会主动去产生新的执行计划，因此该机制是一种防回退的机制，而不是一种用来产生更优计划的机制。

Q2：SPM 能解决什么问题？

1. 大小账号场景，且大小账号的流量是均匀的场景下计划走偏。 业务表中数据存在倾斜时，优化器生成计划时可能因为使用了特定的大/小账号而导致计划走偏，比较典型的场景是应该走索引 A，但是因为生成计划时使用一个极具代表性的参数导致错误的走到了索引 B。如果业务大小账号的流量是均匀的情况下，使用 SPM 可以及时的验证执行计划在小/大账号的场景下性能不优，从而避免使用错误的执行计划。

2. 分页查询因为代价计算不准引起的计划走偏。 分页查询由于代价模型计算不准确，或者特定索引上的数据分布可以加快执行速度的场景下，优化器容易选错索引导致计划走偏。这种场景下，如果基线计划是正确的索引，那么开启SPM可以避免因为计划走偏导致的分页查询性能下降。

3. 版本升级后，因为优化器优化行为变化引起的计划走偏。 当对数据库做版本升级后，新版本的优化器与旧版本优化器总是会存在或多或少的差异。这些细微的差异可能影响计划的生成过程，导致计划走偏，例如选错索引、连接算法等等。针对这种升级引起的计划走偏，SPM 可以很好的验证新旧计划的性能差异，从而避免使用走偏的计划引起性能回退。

Q3：SPM 不能解决什么问题？

1. 大小账号参数具有明显的时间倾向。 假设当用户 SQL 中存在数据倾斜，但是大小账号参数具有明显的时间倾向。比如晚上跑批期间 SQL 使用的都是大账号，白天营业期间 SQL 使用的都是小账号。这种场景下如果大小账号使用的计划存在差异，SPM 在演进期间无法正确判断新计划的有效性，因为生成了新计划后接下来很长一段时间内全部都是相同的大账号或小账号。

2. 数据量缓慢变化的场景。 这个场景与上面的场景类似，在数据缓慢变化的场景下，如一开始是空表数据慢慢增加，SPM 在演进期间无法正确判断新计划的有效性。例如数据量特别少的情况下，可能差的计划和好的计划性能上没什么差异，导致 SPM 错误的认为差的计划也是可用的。等数据量变高以后，差的计划性能就会越来越慢了。

3. 在 SQL 计划选择极不稳定的环境下开启 SPM。 SPM 的计划防回退，依赖于 SQL 存在一个稳定的计划基线。在没有导入基线就直接开启 SPM 时，SQL 是没有任何的基线计划的，这时 SPM 会把一条 SQL 第一次执行成功的计划当做最初的基线计划。如果这个最初的基线计划刚好是正确的计划，那么此时这条 SQL 就具备了计划走偏时的自动纠偏能力。但如果最初的基线计划刚好是错误的计划，并且这个错误的计划也执行成功了，那么很不幸这条 SQL 可能无法像预期的那样具备自动纠偏的能力。因为即使后续生成正确的计划并通过演进验证，错误的计划依然存在于基线中，后续计划走偏时会因为错误的计划存在于基线中导致错误的计划被直接使用而不是进行演进。

Q4：名词解释

基线计划

基线计划记录的并不是实际的物理执行计划，而是计划对应的 outline。在使用基线时优化器会严格按照 outline 描述的计划生成方式还原计划。如果 outline 中存在任意 hint 无法使用，或者还原计划后 Plan Hash 的值与基线记录的值不同，则认为基线还原失败。

最优基线

最优基线的判定是根据基线计划的历史平均执行时间计算的。这个执行时间不是实时更新的，取自计划加入基线时的执行时间、计划演进期间的执行时间，或从 Plan Cache 导入时执行时间或结合基线原有执行数据计算的平均时间（__all_plan_baseline_item.cpu_time/__all_plan_baseline_item.executions（微秒））。基线中的数据只有经过演进或再次导入才会更新。OceanBase 数据库 V4.2.1 BP9 版版本开始 executions=0 的基线视作最慢的基线。

演进任务

为了验证当前计划是否更优而生成。演进任务生成后，后续用户 SQL 命中 Plan Cache 时，OceanBase 会按照一定的规则分别让用户请求执行基线计划或演进计划，两个计划一共验证 150 次去执行。 演进期间用户流量不是均匀打到两个计划上的，而是有一个动态调整的机制，会让执行更快的计划更多的去执行，让执行慢的计划更少的去执行。其中每个执行计划最少会执行 5 次。演进任务将根据两个计划的平均执行时间判定出性能更好的计划。

Q5：SPM 演进模式

在线演进模式

从 OceanBase 数据库 V4.2.1 版本开始提供的一种模式，在这个模式下，每当硬解析阶段优化器生成计划后，SPM 会检查这个计划是不是一个基线计划。如果不是基线计划，则生成一个演进任务验证新计划的有效性。演进任务会将这条 SQL 接下来 150 次的流量按照一定的策略分配给新计划或基线计划，当两个计划的执行次数达到 150 次后 SPM 会根据两个计划在演进期间的平均执行时间来判定新计划是否优于基线计划。如果新计划优于基线计划，则将新计划加入 Plan Cache 中，并把新计划也保存成基线计划；如果新计划差于基线计划，则将基线计划加入 Plan Cache 中，并更新基线计划的一些信息。

基线优先模式

从 OceanBase 数据库 V4.2.5 版本开始提供的一种模式，在这个模式下 SPM 将总是使用基线计划，即使新生成的计划性能比基线计划更好。仅当基线无法还原时，SPM 会使用新生成的计划并将新计划也保存到基线中。此模式适用于上线前能经过充分的测试，业务 SQL 不再变化或者很少变化，业务 DBA 有一定的维护能力的业务。 使用此模式时，用户需要明确以下两点：

1. 保证 SQL 第一次能够生成好的执行计划，或先通过保存基线或保存基线的方式将好的计划保存成基线。

2. 确认不希望计划发生任何改变，即使优化器能生成更好的计划。

第一条很好理解，基线优先的模式一旦生成了基线就总是会使用基线，因此需要保证基线是稳定的。如果有 SQL 产生的第一个基线是不优的计划，需要用户通过手动运维的方式删除这个不优的基线，并保证下次计划生成可以生成一个稳定的计划。 针对第二条，推荐在业务在确定业务 SQL 不会发生变更或者极少发生变更的情况下开启，此时基线优先模式能够保证业务的稳定性。如果业务真的发生了变更，可以参考如下处理方式：

• 业务产生了新的 SQL。此时需要保证新的 SQL 第一次能够生成好的稳定计划。或先在测试环境找到一个好的执行计划保存成基线，然后通过基线导入导出把测试环境的基线导入到生产环境。

• schema 变更（新建索引、行列存转换）时，如果 schema 的变更是为了优化原有的 SQL，可以将演进模式调为在线演进模式，演进一段时间后改回基线优先的模式。如果 schema 的变更是为了支持新的业务 SQL，推荐不做任何调整，让旧的业务 SQL 继续使用原有的稳定计划，让新的业务 SQL 使用新的 schema 生成稳定基线。

最优基线演进

非最优基线与最优基线演进。OceanBase 数据库 V4.2.1 BP9 新增加的 Feature，如果硬解析生成的执行计划是一个基线计划，但不是最优的基线计划，依然会生成一个演进任务比较当前基线与最优基线的性能，演进后决定 Plan Cache 中保留哪个计划作为缓存。

• 为什么会有最优基线演进？

  SPM 上线后，在一些业务中发现如果一条 SQL 第一次生成的计划是一个不稳定的计划 Plan1，那么即使后续生成了更稳定的好计划 Plan2，也依然无法保证业务 SQL 不会走偏。因为按照目前的设计，当优化器再次生成不稳定的计划 Plan1 时，由于该计划存在于基线中 SPM 会允许直接使用这个不稳定的计划，达不到防止计划回退的目的。

• 什么样的 SQL 做演进？

  当 SQL 中表的数量少于 5 个的时候才会触发。从线上遇到的问题看，这一类问题通常都是简单的单表扫描选错索引，或者是两表连接选错驱动表。

Q6：如何查看 SPM 开启？

系统变量

使用此方法开启 SPM 时，需要业务重新建立连接使系统变量的变化在所有 session 上生效（重启业务或者 OBProxy 的风险）。

• optimizer_use_sql_plan_baselines

  表示当 SQL 生成新的计划时是否使用基线计划进行演进。

• optimizer_capture_sql_plan_baselines

  表示当新的计划优于基线计划时，是否捕获新的计划作为基线。

  -- 查询语句
  select tenant_id, name, value, gmt_modified from oceanbase.__all_virtual_sys_variable_history where tenant_id = 1008 and name like 'optimizer%';
  

修改配置项

可以开启 SPM 的在线演进模式或基线优先模式， 使用此方法开启 SPM 时，不需要业务重新建立连接。

-- 开启在线演进模式
alter system set sql_plan_management_mode = 'OnlineEvolve';

-- 开启基线优先模式
alter system set sql_plan_management_mode = 'BaselineFirst';

-- 关闭
alter system set sql_plan_management_mode = 'Disable';

-- 查询语句
select tenant_id, name, value, gmt_modified from oceanbase.__all_virtual_tenant_parameter where tenant_id = 1008 and name = 'sql_plan_management_mode';

各版本支持的开启方式

Q7：如何判断 SPM 是否生效

1. 在 OceanBase 数据库 V4.2.5 版本中查看 __all_spm_evo_result。

   演进结果不是实时写入内部表的，而是通过一个 15 分钟一次的定时任务写入，因此可能存在一定的延迟。

2. 非 OceanBase 数据库 V4.2.5 版本中查看 __all_plan_baseline_item。

   • 存在 + 第一条（按照 gmt_create 字段排序）- 基线。

   • 存在 + 非第一条 + executions 为 1 - 所有的基线计划都无法还原导致 SPM 被迫把这个计划加入基线的或者演进任务的超时时间内只执行了 1 次且比其他的快。

   • 存在 + 非第一条 + executions 不为 1 - 可能是演进期间所有的参数都是小账号或大账号，使得演进期间内这个计划确实比基线计划更好。

   • 不存在 - 可能是 BUG。

Q8：开启 SPM 后一些特点

• 开启 SPM 后，会存在同一时间点，生成 2 个不同的计划。（可以根据 plan id 来判断是新计划，还是基线计划。）

• 绑定了 outline 之后，SPM 不会再介入了。

• PL 里面的 SQL，在 SPM 场景下不生效，不起作用

• 当前 SPM 开启后，在 V4.2.5BP4 和 V4.3.5BP4 之前的版本中，需要在 3 个小时内累计执行 150 次 才能完成演进；V4.2.5BP4 和 V4.3.5BP4 及之后版本已取消该时间限制，只需累计执行 150 次 即可。

• 目前 SPM 的演进依赖于 Plan Cache，如果 Plan Cache 无法缓存住执行计划， 则可能导致计划不断触发演进，造成执行性能波动。

• SPM 的演进依赖演进计划和基线计划被加入到同一个 plan set 中，因此当演进计划与基线计划的约束不同时是无法演进的。

• 当演进计划或基线计划执行失败时，演进任务会记录两者的失败次数。当任意失败次数超过 3 次时，演进任务直接结束并使用另一种计划作为最终的计划。

• 演进期间关闭计划淘汰。（OceanBase 数据库 V4.2.1 BP10 版本）

• 演进中刷 Plan Cache 导致老的演进无法结束，新的演进无法创建。（OceanBase 数据库 V4.2.1 BP9 版本）

• 支持批量导入/删除 计划到 SPM baseline。（OceanBase 数据库 V4.2.1 BP10 版本）

Q9：SPM 的一些限制

• 处于备份恢复的恢复状态的租户和主备集群的备机无法做计划演进。

• 系统租户下的 SQL 和 Inner SQL 不做计划演进。

• insert into values 不做演进。

• 任何 server 的演进结果其它的 server 无法立即感知，因为演进的结果是暂存在 server 的 Local Cache 中的，会定期同步到 Inner Table 中。

• Plan Cache 内存达到上限，会导致 SPM 无法正常演进。

Q10：在日志中定位 SPM 的关键字

Q11：SPM 相关的系统表和视图

• gv$ob_plan_cache_plan_stat： 记录了计划缓存的相关信息。

• __all_plan_baseline_item： 记录 SPM 中所有可用的基线计划。

• __all_virtual_plan_baseline_item： 记录了基线计划相关信息。

• __all_spm_evo_result（仅 OceanBase 数据库 V4.2.5 版本）： 记录 SPM 运行的一些情况，包括一条 SQL 何时产生了第一条基线、一条 SQL 何时所有的可用基线都无法还原、一次演进任务的演进结果、基线优先模式何时使用了基线计划替代了新计划。这些记录最多会保存 15 天。

• __all_spm_config： SPM 配置相关信息。

Q12：相关操作

查询基线

select tenant_id, database_id, plan_hash_value, gmt_create, gmt_modified, plan_type, origin, usec_to_time(last_executed), usec_to_time(last_verified), elapsed_time, cpu_time, executions from oceanbase.__all_virtual_plan_baseline_item where tenant_id = 1002 and sql_id ='${sql_id}';

查询计划命中次数

select PLAN_ID,PLAN_HASH,SQL_ID,STATEMENT,LAST_ACTIVE_TIME,HIT_COUNT,OUTLINE_DATA from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='${sql_id}';

如何删除掉某条 SQL 的 SPM 基线？

先删基线，再删缓存，示例如下。

-- 删除计划缓存
ALTER SYSTEM FLUSH PLAN CACHE sql_id='${sql_id}' databases='${database_name}' tenant='${tenant_name}' GLOBAL;
--删除基线
select DBMS_SPM.DROP_SQL_PLAN_BASELINE('${tenant_name}','${sql_handle}','${plan_name}') from dual;

有关 DBMS_SPM.DROP_SQL_PLAN_BASELINE 详情参见：执行计划管理。

如何查看演进中的计划

gv$ob_plan_cache_plan_stat 查 evolution = 1 的就是正在演进中的

如何查看 SPM 是哪个版本打开的

select outline_data from __all_virtual_plan_baseline_item where tenant_id = 1002 and database_id = 500008 and sql_id = '${sql_id}' \G
*************************** 1. row ***************************
outline_data: /*+BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "${tenant_name}"."learning_activity"@"SEL$1" "idx_parent_id") OPTIMIZER_FEATURES_ENABLE('4.2.1.9') END_OUTLINE_DATA*/

SQL PLAN Monitor 可以获取 SQL 执行的物理计划

select plan_line_id,
    concat(lpad(' ', PLAN_DEPTH, ' '), plan_operation) as OPERATION,
    sum(output_rows) as output_rows,
    min(first_change_time) as start_time,
    max(last_change_time) as end_time,
    count(*) as worker_count,
    count(
        case
            when output_rows = 0 then 1
            else null
        end
    ) idl_worker
from GV$SQL_PLAN_MONITOR
where trace_id = 'YB420A6824DE-00061BD944490D3A-0-0'
group by PLAN_DEPTH,
    PLAN_OPERATION,
    plan_line_id,
    PLAN_PARENT_ID
order by plan_line_id;

适用版本

OceanBase 数据库 V4.2.x、V4.3.x 版本。