分布式数据库学习Note82：OceanBase社区版中，如何管理执行计划？

SQL Plan Management（SPM）是一种防止计划回退的机制，能够确保新生成的计划在经过验证后才被使用，以保证计划性能不断优化和更新。

OceanBase 数据库支持在线 SPM 演进机制，即当发现新生成的计划不在基线中时，就会立即自动启动一个演进任务进行计划演进，这样就可以在用户无需手动干预的情况下自动完成计划演进。

OceanBase 数据库的 SPM 使用 DBA_SQL_PLAN_BASELINES 和 DBA_SQL_MANAGEMENT_CONFIG 视图，以及 DBMS_SPM 系统包来管理执行计划。

SPM 的执行机制

SPM 基于 SQL Plan Baseline 实现，SQL Plan Baseline 是执行计划的一个基线，用于持久化存储已经验证过的执行计划信息（Outline Data 等信息），每个执行计划可对应一个 Plan Baseline，通过该 Plan Baseline 可复现一个执行计划。

OceanBase 数据库计划的演进总是由计划生成触发的，SPM 的执行机制的可以概括为：

1. SQL 第一次生成的计划会被默认作为基线并被 ACCEPT。
2. 当新的计划存在于计划基线中，并且基线是 FIXED 或者不存在其它 FIXED 计划基线时，直接使用当前计划。
3. 当存在可复现的 FIXED 基线时，总是优先使用 FIXED 基线计划，不做计划演进。
4. 当不存在可复现的 FIXED基线，但存在可复现的 ACCEPTED 基线计划，则新计划与基线计划进行演进。
5. 如果没有基线计划可以复现，则直接使用新生成的计划。

SPM 的使用限制

OceanBase 数据库的 SPM 具有如下使用限制：

• 处于备份恢复的恢复状态的租户和主备集群的备机无法执行计划演进。
• 系统租户下的 SQL 和 Inner SQL 不能做计划演进。
• SQL 语句包含 INSERT INTO VALUES 时不做计划演进。
• 由于演进的结果是暂存在 OBServer 的 Local Cache 中，会定期同步到 Inner Table 中，所以任何 OBServer 的演进结果对于其它的 OBServer 来讲是无法立即感知的。

SPM 相关视图

DBA_SQL_PLAN_BASELINES

DBA_SQL_PLAN_BASELINES 视图记录了 SPM 中 SQL 的计划基线。该视图各字段的含义如下表所示。

DBA_SQL_MANAGEMENT_CONFIG

DBA_SQL_MANAGEMENT_CONFIG 视图记录了 SPM 中的配置参数。该视图各字段的含义如下表所示。

DBMS_SPM 系统包

DBMS_SPM 系统包用于管理 SQL 计划，支持加载、更改以及删除 Plan Baseline 信息。

下表列出了 OceanBase 数据库当前版本所支持的 DBMS_SPM 子程序和简要描述。

ACCEPT_SQL_PLAN_BASELINE

ACCEPT_SQL_PLAN_BASELINE 过程用于根据演进计划的结果接受一个计划作为基线。

语法

语法如下：

DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE  (
     database_name     VARCHAR(65535),
     sql_handle        VARCHAR(65535),
     plan_name         VARCHAR(65535) DEFAULT NULL);

参数说明

示例

DECLARE
  v_accept_plans NUMBER;
BEGIN
  v_accept_plans := DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE(
     sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
     plan_name => '3388268709115914355'
     );
END;
/

ALTER_SQL_PLAN_BASELINE

ALTER_SQL_PLAN_BASELINE 函数用于修改基线中一个或一组计划的属性。返回值表示被修改的基线数量。

语法

语法如下：

DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( 
  database_name     VARCHAR(65535),
  sql_handle        VARCHAR(65535),
  plan_name         VARCHAR(65535) DEFAULT NULL,
  attribute_name    VARCHAR(65535),
  attribute_value   VARCHAR(65535)
)
RETURN DECIMAL;

参数说明

属性说明

示例

将某个 Plan Baseline 固化后该 SQL 仅使用该计划。

DECLARE
  v_alter_plans NUMBER;
BEGIN
  v_alter_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
     sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
     plan_name => '3388268709115914355',
     attribute_name => 'fixed',
     attribute_value => 'YES' );
END;
/

CANCEL_EVOLVE_TASK

CANCEL_EVOLVE_TASK 过程用于取消一个正在演进的任务。

语法

语法：

DBMS_SPM.CANCEL_EVOLVE_TASK  (
  database_name     VARCHAR(65535),
  task_name         VARCHAR(65535));

参数说明

示例

BEGIN
  DBMS_SPM.CANCEL_EVOLVE_TASK ('SYS_AUTO_SPM_EVOLVE_TASK');
END;

CONFIGURE

CONFIGURE 过程用于设置 SPM 的一些参数。

语法

语法如下：

DBMS_SPM.CONFIGURE (
    database_name     VARCHAR(65535),
    parameter_name    VARCHAR(65535),
    parameter_value   DECIMAL
  );

参数说明

SPM 参数说明

示例

设置计划 20 周后未被使用会被自动淘汰掉。

BEGIN
  DBMS_SPM.CONFIGURE ('plan_retention_weeks', 20);
END;

DROP_EVOLVE_TASK

DROP_EVOLVE_TASK 过程用于丢弃一个演进任务。

语法

语法如下：

DBMS_SPM.DROP_EVOLVE_TASK  (
  database_name     VARCHAR(65535),
  task_name         VARCHAR(65535));

参数说明

示例

BEGIN
  DBMS_SPM.DROP_EVOLVE_TASK ('SYS_AUTO_SPM_EVOLVE_TASK');
END;

DROP_SQL_PLAN_BASELINE

DROP_SQL_PLAN_BASELINE 函数用于用于丢弃一个或多个计划基线。

语法

语法如下：

DBMS_SPM.DROP_SQL_PLAN_BASELINE (
  database_name     VARCHAR(65535),
  sql_handle        VARCHAR(65535),
  plan_name         VARCHAR(65535) DEFAULT NULL
)
RETURN DECIMAL;

参数说明

示例

DECLARE
  v_drop_plans number;
BEGIN
  v_drop_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
     sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
     plan_name => '3388268709115914355' );
END;
/

LOAD_PLANS_FROM_CURSOR_CACHE

LOAD_PLANS_FROM_CURSOR_CACHE 函数用于用于从 Plan Cache 中读取一条或多条 SQL 的计划作为计划基线。

语法如下：

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  database_name     VARCHAR(65535),
  sql_id            VARCHAR(65535),
  plan_hash_value   DECIMAL DEFAULT NULL,
  is_fixed          VARCHAR(65535) DEFAULT 'NO',
  enabled           VARCHAR(65535) DEFAULT 'YES'
)
RETURN DECIMAL;

参数说明

示例

DECLARE
  v_load_plans number;
BEGIN
  v_load_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
     sql_id => '529F6E6454EF579C7CC265D1F6131D70',
     plan_hash_value => 13388268709115914355);
END;
/