# SPM计划管理
业务数据变动或数据库版本升级等情况可能导致SQL执行计划的改变，这种改变可能提升SQL执行性能，也可能导致性能显著下降。为了确保这种变化始终朝着积极的方向发展，需要增加一个SPM（SQL Plan Management）组件来达成此目的。
#### SPM整体流程
SPM处于SQL引擎中，且处于SQL引擎的两大核心组件优化器和执行器之间，用于完成对计划的固化，如[图1]所示。
图1SPM整体流程   
![](https://support.huaweicloud.com/centralized-devg-v10-gaussdb/figure/zh-cn_image_0000002590205224.png "点击放大")
结合上图，对SPM的关键组成介绍如下：
1. Outline管理组件： Outline管理是SPM中最基础的组件，主要负责将一个具体的计划转化为一个具体的Hint集合，或者将一个具体的Hint集合转换为一个具体的计划。对于一个具体计划，它的Outline是一组能够完全确定该计划的Hint集合。
   
2. 计划捕获组件： 对于一个具体SQL，该组件将优化器给出的计划（如上图中的plan_x）以outline的形式落盘，并将该SQL第一条gplan计划标记为ACC（ACCEPTED的简称）状态，其他计划标记为UNACC（UNACCEPTED的简称）状态，以备后续使用。
   
3. 计划选择组件： 计划选择组件用于决策是否将优化器给出的执行计划交给执行器去执行。如果SPM认为该计划存在性能劣化的风险，SPM会在ACC/FIXED状态的计划中再选择一个计划（如上图中的plan_y，plan_y可能等于plan_x，也可能不相等）交给执行器去执行。
   
4. 计划演进组件： 计划捕获+计划选择组件可以将交给执行器的计划固定下来，计划演进组件是将优化器新产生的计划（已被计划捕获组件固化下来，但为UNACC状态的计划）进行优秀程度的判定，如果被判定的计划符合优秀的判定标准，则可以将其调整为ACC状态，以备计划选择组件使用。
   
 
#### SPM基本使用
SPM计划管理在不同场景中有一些差异，因此在介绍SPM计划管理基本使用之前，需要说明以下几点：
- GUC参数spm_enable_plan_capture取值范围分别是off、auto、manual、store，其中auto和manual分别对应SPM计划捕获行为的自动计划捕获模式和手动计划捕获模式，这两种计划捕获模式的唯一区别在于：前者仅对重复出现的SQL进行捕获，后者没有这一约束。
- 由于pbe下模板SQL的cplan计划存在无法复用的问题， 因此pbe下的所有cplan计划无法被捕获，第一次出现的gplan会被标记为ACC状态。
- 不同的客户端（例如JDBC和gsql）pbe sql的SPM计划管理行为是一致的。
- SPM计划管理会对pbe下gplan的第一组参数进行捕获。
- 如果当前查询存在一条基线，计划选择模块在打开时会检查当前查询计划与基线是否一致，若存在差异，则会进行额外基线捕获。
在不同的场景下，除了上述中的一些差异点外，其他行为在不同的场景中的表现是一致的，下面以在gsql中normal sql的SPM计划管理手动捕获模式的基本使用为例，给出基本操作：
- 初始化数据 初始化数据的SQL如下：
  ```
  gaussdb=# DROP TABLE IF EXISTS tb_a cascade;
  gaussdb=# CREATE TABLE tb_a(id int,c1 int, c2 int, pad text);
  gaussdb=# CREATE INDEX tb_a_idx_c1 ON tb_a(c1);
  gaussdb=# INSERT INTO tb_a select id, (random()*200)::int, (random()*10000)::int, 'ss' FROM (SELECT generate_series(1,10000) id) tb_a;
  gaussdb=# ANALYZE tb_a;
  -- 首先，使用 DROP TABLE 语句删除名为 tb_a 的表，如果该表存在 
  -- CASCADE 关键字用于级联删除与该表相关的依赖项 
  gaussdb=# DROP TABLE IF EXISTS tb_a CASCADE;  
  -- 然后，使用 CREATE TABLE 语句创建一个名为 tb_a 的表 
  -- 该表包含四个列：id 为整数类型，c1 为整数类型，c2 为整数类型，pad 为文本类型 
  gaussdb=# CREATE TABLE tb_a(id INT, c1 INT, c2 INT, pad TEXT);  
  -- 接下来，在表 tb_a 的 c1 列上创建一个索引 
  -- 该索引名为 tb_a_idx_c1，有助于提高对 c1 列的查询性能 
  gaussdb=# CREATE INDEX tb_a_idx_c1 ON tb_a(c1);  
  -- 向表 tb_a 中插入数据
  -- 通过子查询生成一系列的 id 值，范围从 1 到 10000
  -- 对于 c1 列，使用随机函数 random() 乘以 200 并转换为整数
  gaussdb=# INSERT INTO tb_a SELECT id, (RANDOM()*200)::INT, (RANDOM()*10000)::INT, 'ss' FROM (SELECT GENERATE_SERIES(1,10000) id) tb_a; 
  -- 对表 tb_a 进行分析，有助于优化查询性能
  gaussdb=# ANALYZE tb_a;
  ```
  

- 设置前置GUC参数 设置如下GUC参数的目的均已在注释中体现。
  ```
  -- 开启SPM计划捕获
  gaussdb=# SET spm_enable_plan_capture=manual;
  -- 开启SPM计划选择
  gaussdb=# SET spm_enable_plan_selection=on;
  -- 当前SPM只支持gplan，确保生成的计划是gplan
  gaussdb=# SET plan_cache_mode = 'force_generic_plan';
  -- 在pretty模式可以看到baseline的使用情况
  gaussdb=# SET explain_perf_mode=pretty;
  -- 关闭执行器sql bypass的特殊优化
  -- 这里是off， 只是希望测试的sql执行流程更具有一般性，对SPM流程无任何影响
  gaussdb=# SET enable_opfusion=off;
  ```
  

- 计划捕获测试 关键操作的说明已经在注释中体现。
  ```
  -- 捕获tablescan，确保捕获tablescan计划
  gaussdb=# SET enable_seqscan=on;
  gaussdb=# SET enable_indexscan=off;
  gaussdb=# SET enable_bitmapscan=off;
  -- 执行测试sql，结果下如下，可以看到当前sql并没有使用任何baseline计划。
  gaussdb=# PREPARE spm_query AS SELECT * FROM tb_a WHERE c1 = $1;
  gaussdb=# EXPLAIN(costs off) EXECUTE spm_query(1);
   id |      operation
  ----+----------------------
    1 | ->  Seq Scan on tb_a
  (1 row)
   Predicate Information (identified by plan id)
  -----------------------------------------------
     1 --Seq Scan on tb_a
           Filter: (c1 = $1)
  (2 rows)
  -- 查看baseline，可以看到tablescan计划被捕获，且状态为ACC
  gaussdb=# SELECT sql_hash, plan_hash, outline, status, gplan FROM gs_spm_sql_baseline WHERE sql_text LIKE '%tb_a where c1 = $1%';
   sql_hash  | plan_hash  |                 outline                  | status | gplan
  -----------+------------+------------------------------------------+--------+-------
   982135085 | 4251425169 |  begin_outline_data                     +| ACC    | t
             |            |  TableScan(@"sel$1" public.tb_a@"sel$1")+|        |
             |            |  version("1.0.0")                       +|        |
             |            |  end_outline_data                        |        |
  (1 row)
  -- 捕获indexscan确保捕获indexscan计划
  gaussdb=# SET enable_indexscan=on;
  gaussdb=# SET enable_seqscan=off;
  gaussdb=# SET enable_bitmapscan=off;
  -- 执行测试sql，可以看到计划仍然为tablescan，且该计划管理来自SPM(baseline中ACC的计划)，因为从第二条计划开始计划被标记为UNACC状态，UNACC状态的计划是不能被使用的。
  gaussdb=# DEALLOCATE spm_query;
  gaussdb=# PREPARE spm_query AS SELECT * FROM tb_a WHERE c1 = $1;
  gaussdb=# EXPLAIN(costs off) EXECUTE spm_query(1);
   id |      operation
  ----+----------------------
    1 | ->  Seq Scan on tb_a
  (1 row)
   Predicate Information (identified by plan id)
  -----------------------------------------------
     1 --Seq Scan on tb_a
           Filter: (c1 = $1)
  (2 rows)
                     ====== Query Others =====
  ---------------------------------------------------------------
   use_baseline: Yes, sql_hash: 982135085, plan_hash: 4251425169
  (1 row)
  -- 查看baseline，可以看到indexscan的计划被捕获，且状态为UNACC
  gaussdb=# SELECT sql_hash, plan_hash, outline, status, gplan FROM gs_spm_sql_baseline WHERE sql_text LIKE '%tb_a WHERE c1 = $1%';
   sql_hash  | plan_hash  | outline                                              | status | gplan
  -----------+------------+------------------------------------------------------+--------+-------
   982135085 | 4251425169 |  begin_outline_data                                 +| ACC    | t
             |            |  TableScan(@"sel$1" public.tb_a@"sel$1")            +|        |
             |            |  version("1.0.0")                                   +|        |
             |            |  end_outline_data                                    |        |
   982135085 |  808368919 |  begin_outline_data                                 +| UNACC  | t
             |            |  IndexScan(@"sel$1" public.tb_a@"sel$1" tb_a_idx_c1)+|        |
             |            |  version("1.0.0")                                   +|        |
             |            |  end_outline_data                                    |        |
  (2 rows)
  -- 捕获bitmapscan，确保捕获bitmapscan计划
  gaussdb=# SET enable_bitmapscan=on;
  gaussdb=# SET enable_seqscan=off;
  gaussdb=# SET enable_indexscan=off;
  -- 执行测试sql
  gaussdb=# DEALLOCATE spm_query;
  gaussdb=# PREPARE spm_query AS SELECT * FROM tb_a WHERE c1 = $1;
  gaussdb=# EXPLAIN(costs off) EXECUTE spm_query(1);
   id |      operation
  ----+----------------------
    1 | ->  Seq Scan on tb_a
  (1 row)
   Predicate Information (identified by plan id)
  -----------------------------------------------
     1 --Seq Scan on tb_a
           Filter: (c1 = $1)
  (2 rows)
                     ====== Query Others =====
  ---------------------------------------------------------------
   use_baseline: Yes, sql_hash: 982135085, plan_hash: 4251425169
  (1 row)
  -- 查看baseline，可以看到bitmapscan的计划被捕获，且状态为UNACC
  gaussdb=# SELECT sql_hash, plan_hash, outline, status, gplan FROM gs_spm_sql_baseline WHERE sql_text LIKE '%tb_a WHERE c1 = $1%';
   sql_hash  | plan_hash  |                        outline                        | status | gplan
  -----------+------------+-------------------------------------------------------+--------+-------
   982135085 | 4251425169 |  begin_outline_data                                  +| ACC    | t
             |            |  TableScan(@"sel$1" public.tb_a@"sel$1")             +|        |
             |            |  version("1.0.0")                                    +|        |
             |            |  end_outline_data                                     |        |
   982135085 |  808368919 |  begin_outline_data                                  +| UNACC  | t
             |            |  IndexScan(@"sel$1" public.tb_a@"sel$1" tb_a_idx_c1) +|        |
             |            |  version("1.0.0")                                    +|        |
             |            |  end_outline_data                                     |        |
   982135085 |  930064183 |  begin_outline_data                                  +| UNACC  | t
             |            |  BitmapScan(@"sel$1" public.tb_a@"sel$1" tb_a_idx_c1)+|        |
             |            |  version("1.0.0")                                    +|        |
             |            |  end_outline_data                                     |        |
  (3 rows)
  ```
  

- 计划选择测试 SPM会按照如下流程进行计划选择。
  图2计划选择流程   
  ![](https://support.huaweicloud.com/centralized-devg-v10-gaussdb/figure/zh-cn_image_0000002590365158.png "点击放大")
  其中的namespace_oids为当前指定的SEARCH_PATH中有效的，去除本地临时表所在Schema的OID有序列表。由于namespace_oids为新增列，对于507.0.0版本之前所捕获的基线，其默认值为NULL，此时，在计划选择过程中会认为NULL可以和任意search_path进行匹配。
  此外， 从以上测试中可以发现，无论如何调整GUC参数对计划的生成进行预测，计划最终的选择都是ACC状态的tablescan，这说明计划选择不会使用UNACC状态的计划。
  ```
  -- 测试优先使用FIXED状态的计划
  -- 将上方indexscan计划的状态改为ACC，seqscan修改为FIXED状态，并查看baseline，可以发现状态修改成功。
  gaussdb=# SELECT * FROM dbe_sql_util.gs_spm_set_plan_status(982135085, 4251425169, 'FIXED');
  gaussdb=# SELECT * FROM dbe_sql_util.gs_spm_set_plan_status(982135085, 808368919, 'ACC');
  gaussdb=# SELECT sql_hash, plan_hash, outline, status, gplan, cost FROM gs_spm_sql_baseline WHERE sql_text LIKE '%tb_a WHERE c1 = $1%' ORDER BY creation_time;
   sql_hash  | plan_hash  |                        outline                        | status | gplan |  cost
  -----------+------------+-------------------------------------------------------+--------+-------+---------
   982135085 | 4251425169 |  begin_outline_data                                  +| FIXED  | t     |     167
             |            |  TableScan(@"sel$1" public.tb_a@"sel$1")             +|        |       |
             |            |  version("1.0.0")                                    +|        |       |
             |            |  end_outline_data                                     |        |       |
   982135085 |  808368919 |  begin_outline_data                                  +| ACC    | t     | 133.039
             |            |  IndexScan(@"sel$1" public.tb_a@"sel$1" tb_a_idx_c1) +|        |       |
             |            |  version("1.0.0")                                    +|        |       |
             |            |  end_outline_data                                     |        |       |
   982135085 |  930064183 |  begin_outline_data                                  +| UNACC  | t     |  49.467
             |            |  BitmapScan(@"sel$1" public.tb_a@"sel$1" tb_a_idx_c1)+|        |       |
             |            |  version("1.0.0")                                    +|        |       |
             |            |  end_outline_data                                     |        |       |
  (3 rows)
  -- 确保优化器生成的计划是bitmapscan
  gaussdb=# SET enable_bitmapscan=on;
  gaussdb=# SET enable_seqscan=off;
  gaussdb=# SET enable_indexscan=off;
  -- 执行SQL发现，优化器仍然选择了FIXED状态的tablescan，而不是选择了代价更小且状态为ACC状态的indexscan。
  gaussdb=# DEALLOCATE spm_query;
  gaussdb=# PREPARE spm_query AS SELECT * FROM tb_a WHERE c1 = $1;
  gaussdb=# EXPLAIN(costs off) EXECUTE spm_query(1);
   id |      operation
  ----+----------------------
    1 | ->  Seq Scan on tb_a
  (1 row)
   Predicate Information (identified by plan id)
  -----------------------------------------------
     1 --Seq Scan on tb_a
           Filter: (c1 = $1)
  (2 rows)
                     ====== Query Others =====
  ---------------------------------------------------------------
   use_baseline: Yes, sql_hash: 982135085, plan_hash: 4251425169
  (1 row)
  ```
  

- 计划演进测试
  ```
  -- 演进bitmapscan计划，并查看计划评估结果
  gaussdb=# SELECT * FROM dbe_sql_util.gs_spm_evolute_plan(982135085, 930064183);
  gaussdb=# SELECT sql_hash, plan_hash, better, refer_plan, reason FROM gs_spm_sql_evolution WHERE sql_hash=982135085;
   sql_hash  | plan_hash | better | refer_plan |                                  reason
  -----------+-----------+--------+------------+--------------------------------------------------------------------------
   982135085 | 930064183 | t      |  808368919 | target plan execution time:0.448333, refer plan execution time: 0.660667
  (1 row)
  -- 通过上方演进的结果可以看出bitmapscan（plan_hash）的执行时间远小于indexscan（refer_plan）的执行时间。并且计划演进给出的结论(better==t)表示bitmapscan是可以被接受的。
  -- 根据演进结论修改bitmapscan计划状态为ACC
  gaussdb=# SELECT * FROM dbe_sql_util.gs_spm_set_plan_status(982135085, 930064183, 'ACC');
  -- 确保优化器生成的计划是bitmapscan
  gaussdb=# SET enable_bitmapscan=on;
  gaussdb=# SET enable_seqscan=off;
  gaussdb=# SET enable_indexscan=off;
  -- 执行SQL语句，可以看到，优化器推荐的bitmapscan可以被正常放行使用。
  gaussdb=# DEALLOCATE spm_query;
  gaussdb=# PREPARE spm_query AS SELECT * FROM tb_a WHERE c1 = $1;
  gaussdb=# EXPLAIN(costs off) EXECUTE spm_query(1);
   id |                 operation
  ----+--------------------------------------------
    1 | ->  Bitmap Heap Scan on tb_a
    2 |    ->  Bitmap Index Scan using tb_a_idx_c1
  (2 rows)
   Predicate Information (identified by plan id)
  -----------------------------------------------
     1 --Bitmap Heap Scan on tb_a
           Recheck Cond: (c1 = $1)
     2 --Bitmap Index Scan using tb_a_idx_c1
           Index Cond: (c1 = $1)
  (4 rows) 
                    ====== Query Others =====
  --------------------------------------------------------------
   use_baseline: Yes, sql_hash: 982135085, plan_hash: 930064183
  (1 row)
  ```
  
- 清理数据 清理数据的SQL如下：
  ```
  gaussdb=# DROP TABLE tb_a;
  DROP TABLE
  gaussdb=# DROP INDEX tb_a_idx_c1;
  DROP INDEX
  ```
  
 
#### SPM持久化方式
当SPM捕获到一个新的计划、跳变历史等变化时，需将变更信息存储到SPM相关的系统表中，目前提供同步和异步两种持久化方式，具体实现方式如下：
- 同步持久化 由SPM系统函数触发的系统表变更操作，当前均采用同步持久化方式写入磁盘，其执行流程如下图所示：
  图3SPM系统表修改类系统函数持久化流程   
  ![](https://support.huaweicloud.com/centralized-devg-v10-gaussdb/figure/zh-cn_image_0000002590365138.png "点击放大")
  涉及该流程的系统函数有：
  - GS_SPM_SET_PLAN_STATUS(sql_hash, plan_hash, plan_status)
  
  - GS_SPM_DELETE_PLAN(sql_hash, plan_hash)
  
  - GS_SPM_ACCEPT_HISTORICAL_PLAN(sql_input_hash, history_time, plan_status)
  
  - GS_SPM_DELETE_PLAN_HISTORY(sql_hash, plan_hash, plan_hash_previous, userid, creation_time)
  
  
  具体可参见 《参考》中"SQL参考 \> 函数和操作符 \> SPM计划管理函数"章节。
  

- 异步持久化 对于非系统函数触发的，随常规业务查询触发的持久化操作，为降低对业务执行性能的影响，引入异步消息队列并采用异步持久化方式写入磁盘，其执行流程如下图所示：
  图4SPM异步消息持久化流程   
  ![](https://support.huaweicloud.com/centralized-devg-v10-gaussdb/figure/zh-cn_image_0000002620644841.png "点击放大")
  涉及该流程的动作有：
  - 捕获
    - 当捕获到新计划时，需将其持久化，包括向gs_spm_baseline、gs_spm_sql、gs_spm_param以及gs_spm_id_hash_join四张系统表插入新记录。
    
    - 当捕获到新计划跳变时，需将其持久化，包括向gs_spm_plan_history插入新记录。
     
  
  
  
  - 计划选择
    - 校验基线是否可用，若不可用，则刷新系统表中该基线的可用状态，涉及gs_spm_baseline系统表invalid字段值刷新。
    
    - 刷新系统表中基线最新使用时间和被使用次数，涉及gs_spm_baseline系统表last_used_time和jump_intercept_cnt字段值刷新。
     
  
  
  
  由上可知，SPM后台线程通过消费128个独立的消息队列，使用LIBPQ建立到不同数据库的连接以实现异步消息持久化。该功能由GUC参数spm_enable_async_flush和spm_async_sub_mq_maxsize控制，GUC参数的具体行为可参见《参考》中"数据库运行参数说明 \> GUC参数说明 \> SPM计划管理"章节。此外，可通过打开logging_module中的选项值SPM_BG来观测后台线程消费消息的关键流程，其设置方式可参见《参考》中"数据库运行参数说明 \> GUC参数说明 \> 错误报告和日志 \> 记录日志的内容"章节。
  
![](https://support.huaweicloud.com/centralized-devg-v10-gaussdb/public_sys-resources/notice_3.0-zh-cn.png)
- 当线程退出时，未被消费的消息将被清除。
- 当消息队列满时，新消息将被清除。
- SPM后台线程建立连接失败时，仅重试一次。
- 当消息消费异常时，消息队列中未被消费的消息将被清空。
- 当spm_enable_async_flush设置为off时，将停用消息异步持久化功能，相关消息也会停止入队和出队。
- 当spm_async_sub_mq_maxsize设置为0时，将停止异步消息队列入队，但允许消息队列剩余消息继续出队。
 
#### 注意事项
该特性建议仅在同一信任域内使用，由于客户端不可避免地可能出现伪造名称的情况，该选项使用时需要与客户端联合形成一套安全机制，减少特性失效的风险。