循序渐进丨MogDB 数据库新特性之SQL PATCH绑定执行计划

1

SQL PATCH

熟悉 Oracle 的DBA都知道，生产系统出现性能问题时，往往是SQL走错了执行计划，紧急情况下，无法及时修改应用代码，DBA可以采用多种方式针对于某类SQL进行执行计划绑定，比如SQL Profile、SPM、SQL Plan Base等等。

MogDB 数据库5.0版本引入了SQL PATCH的特性，SQL PATCH能够在避免直接修改用户业务语句的前提下对查询执行的方式做一定调整。在发现查询语句的执行计划、执行方式未达预期的场景下，可以通过创建查询补丁的方式，使用Hint对查询计划进行调优或对特定的语句进行报错短路处理。

SQL PATCH主要设计给DBA、运维人员及其他需要对SQL进行调优的角色使用，用户通过其他运维视图或定位手段识别到业务语句存在计划不优导致的性能问题时，可以通过创建SQL PATCH对业务语句进行基于Hint的调优。目前支持行数、扫描方式、连接方式、连接顺序、PBE custom/generic计划选择、语句级参数设置、参数化路径的Hint。

特性约束

• • 仅支持针对Unique SQL ID打PATCH，如果存在Unique SQL ID冲突，用于Hint调优的SQL PATCH可能影响性能，但不影响语义正确性。

  • 仅支持不改变SQL语义的Hint作为PATCH，不支持SQL改写。

  • 不支持逻辑备份、恢复。

  • 不支持创建时校验PATCH合法性，如果PATCH的Hint存在语法或语义错误，不影响查询正确执行。

  • 仅初始用户、运维管理员、监控管理员、系统管理员用户有权限执行；库之间不共享，创建SQL PATCH时需要连接目标库。

  • 配置集中式备机可读时，需要指定主机执行SQL PATCH创建/修改/删除函数调用，备机执行报错。

  • SQL PATCH同步给备机存在一定延迟，待备机回放相关日志后PATCH生效。

  • 不支持对存储过程中的SQL语句生效，当前机制不会对存储过程内语句生成Unique SQL ID。

  • 用于规避的Abort Patch不建议在数据库中长期使用，只应该作为临时规避方法；遇到内核问题所导致的特定语句触发数据库服务不可用问题，需要尽快修改业务或升级内核版本解决问题；并且升级后由于Unique SQL ID生成方法可能变化，可能导致规避方法失效。

  • 当前，除DML语句之外，其他SQL语句（如CREATE TABLE等）的Unique SQL ID是对语句文本直接哈希生成的，所以对于此类语句，SQL PATCH对大小写、空格、换行等敏感，即不同的文本的语句，即使语义相对，仍然需要对应不同的SQL PATCH；对于DML，则同一个SQL PATCH可以对不同入参的语句生效，并且忽略大小写和空格。

依赖关系

需要开启enable_resource_track参数并且设置instr_unique_sql_count大于0。对于不同的语句，如果生成的Unique SQL ID冲突，会导致SQL PATCH错误地命中预期外的其他语句。其中用于调优的Hint PATCH副作用相对较小，Abort Patch需要谨慎使用。

2

实际案例

1、创建表

创建表t1和t2；

create table t1(name char(10),id int);
create table t2(name char(10),id int);

2、构造数据

INSERT INTO t1 (name, id)
SELECT 'data_'|| generate_series(1, 1000), generate_series(1, 1000);INSERT INTO t2 (name, id)
SELECT 'data_'|| generate_series(1, 1000), generate_series(1, 1000);CREATE INDEX idx_t1 ON t1 (id);
CREATE INDEX idx_t2 ON t2 (id);

3、获取unique_query_id

执行SQL并获取unique_query_id、执行计划：

set track_stmt_stat_level = 'L1,L1'; --track_stmt_stat_level解释：
该参数分为两部分：
--形式为'full sql stat level, slow sql stat level'。
--级别（L2 > L1 > L0），L1在L0的基础上记录了执行计划，L2在L1的基础上记录了锁的详细信息select * from t1 a, t2 b where a.id = b.id;name    |  id  |    name    |  id  
------------+------+------------+------data_1     |    1 | data_1     |    1data_2     |    2 | data_2     |    2data_3     |    3 | data_3     |    3data_4     |    4 | data_4     |    4data_5     |    5 | data_5     |    5data_6     |    6 | data_6     |    6data_7     |    7 | data_7     |    7。。。。
(1000 row)

4、获取查看执行计划

走的全表扫描hash jion执行计划：

explain select * from t1 a, t2 b where a.id = b.id;QUERY PLAN  
--------------------------------------------------------------------------Aggregate  (cost=60.75..60.76 rows=1 width=8)->  Hash Join  (cost=28.50..58.25 rows=1000 width=0)Hash Cond: (a.id = b.id)->  Seq Scan on t1 a  (cost=0.00..16.00 rows=1000 width=4)->  Hash  (cost=16.00..16.00 rows=1000 width=4)->  Seq Scan on t2 b  (cost=0.00..16.00 rows=1000 width=4)

5、查询unique_query_id

select unique_query_id,query,start_time from dbe_perf.statement_history  where query like '%from t1 a%';unique_query_id |                       query                        |          start_time           
-----------------+----------------------------------------------------+-------------------------------3366573496 | select * from t1 a, t2 b where a.id = b.id;        | 2024-01-19 10:08:56.994391+08也可以通过statement_history查询执行计划
select start_time,query_plan  from dbe_perf.statement_history  where unique_query_id = 3366573496;start_time           |                                query_plan                                
-------------------------------+--------------------------------------------------------------------------2024-01-19 10:08:56.994391+08 | Datanode Name: dn_6001_6002                                        +| Hash Join  (cost=28.50..58.25 rows=1000 width=30)                  +|   Hash Cond: (a.id = b.id)                                         +|   ->  Seq Scan on t1 a  (cost=0.00..16.00 rows=1000 width=15)      +|   ->  Hash  (cost=16.00..16.00 rows=1000 width=15)                 +|         ->  Seq Scan on t2 b  (cost=0.00..16.00 rows=1000 width=15)+|                                                                    +

6、SQL PATCH绑定执行计划

call dbe_sql_util.create_hint_sql_patch('enmo patch',3366573496,'indexscan(a)');create_hint_sql_patch 
-----------------------t
(1 row)--参数说明：
enmo patch --SQL PATCH name
3366573496 --unique_query_id
indexscan(a) --Hint文本

7、验证SQL PATCH

执行并检查新的执行计划是否生效：

select * from t1 a, t2 b where a.id = b.id;name    |  id  |    name    |  id  
------------+------+------------+------data_1     |    1 | data_1     |    1data_2     |    2 | data_2     |    2data_3     |    3 | data_3     |    3data_4     |    4 | data_4     |    4data_5     |    5 | data_5     |    5data_6     |    6 | data_6     |    6data_7     |    7 | data_7     |    7。。。。explain select * from t1 a, t2 b where a.id = b.id;
NOTICE:  Plan influenced by SQL hint patchQUERY PLAN                                  
------------------------------------------------------------------------------Hash Join  (cost=28.50..86.50 rows=1000 width=30)Hash Cond: (a.id = b.id)->  Index Scan using idx_t1 on t1 a  (cost=0.00..44.25 rows=1000 width=15) --这里走了索引，表示SQL patch生效->  Hash  (cost=16.00..16.00 rows=1000 width=15)->  Seq Scan on t2 b  (cost=0.00..16.00 rows=1000 width=15)
(5 rows)

查看statement_history的执行计划：

select query_plan,to_char(start_time,'yyyymmdd-hh24:mi:ss') starttime
from dbe_perf.statement_history 
where unique_query_id = 3366573496
order by start_time;Datanode Name: dn_6001_6002                                                 +| 20240119-10:09:54Hash Join  (cost=28.50..86.50 rows=1000 width=30)                           +| Hash Cond: (a.id = b.id)                                                  +| ->  Index Scan using idx_t1 on t1 a  (cost=0.00..44.25 rows=1000 width=15)+| ->  Hash  (cost=16.00..16.00 rows=1000 width=15)                          +| ->  Seq Scan on t2 b  (cost=0.00..16.00 rows=1000 width=15)         +| +| |

查看数据库内已定义的SQL Patch：

select patch_name,unique_sql_id,enable,hint_string from gs_sql_patch;patch_name | unique_sql_id | enable | hint_string  
------------+---------------+--------+--------------enmo patch |    3366573496 | t      | indexscan(a)show_sql_patch查看SQL PATCH内容
MogDB=# select  DBE_SQL_UTIL.show_sql_patch('enmo patch');show_sql_patch            
-------------------------------------(3366573496,t,f,"indexscan(a)")
(1 row)

8、Abort Patch

使用Abort PATCH对特定语句进行提前报错规避：

MogDB=# select * from dbe_sql_util.drop_sql_patch('enmo patch'); -- 删去enmo patchdrop_sql_patch
----------------t
(1 row)
MogDB=# select * from dbe_sql_util.create_abort_sql_patch('patch2', 3366573496); -- 对该语句的Unique SQL ID创建Abort Patchcreate_abort_sql_patch
------------------------t
(1 row)MogDB=# select * from t1 a, t2 b where a.id = b.id; -- 再次执行语句会提前报错
ERROR:  Statement 3366573496 canceled by abort patch patch2

9、关闭特定SQL Patch

disable enmo patch
call dbe_sql_util.disable_sql_patch('enmo patch');

执行SQL并检查执行计划是否恢复原始状态：

MogDB=# select  query_plan,to_char(start_time,'yyyymmdd-hh24:mi:ss') starttime
MogDB-#  from dbe_perf.statement_history 
MogDB-#  where  unique_query_id = 3366573496
MogDB-#  order by start_time;query_plan                                |     starttime     
--------------------------------------------------------------------------+-------------------Datanode Name: dn_6001_6002                                             +| 20240119-13:24:52Nested Loop  (cost=0.00..340.00 rows=1000 width=30)                     +| ->  Seq Scan on t1 a  (cost=0.00..16.00 rows=1000 width=15)           +| ->  Index Scan using idx_t2 on t2 b  (cost=0.00..0.31 rows=1 width=15)+| Index Cond: (id = a.id)                                         +| +| | Datanode Name: dn_6001_6002                                             +| 20240119-13:31:49Hash Join  (cost=28.50..58.25 rows=1000 width=30)                       +| Hash Cond: (a.id = b.id)                                              +| ->  Seq Scan on t1 a  (cost=0.00..16.00 rows=1000 width=15)           +| ->  Hash  (cost=16.00..16.00 rows=1000 width=15)                      +| ->  Seq Scan on t2 b  (cost=0.00..16.00 rows=1000 width=15)     +| +|

最新的执行计划已经还原成hash jion。

MogDB 数据库官方文档参考：

• • SQL PATCH特性描述：https://docs.mogdb.io/zh/mogdb/v5.0/sql-patch#特性描述

  • track_stmt_stat_level：https://docs.mogdb.io/zh/mogdb/v5.0/query#track_stmt_stat_level

关于作者

许玉晨，云和恩墨 MogDB 技术支持工程师，有12年左右的金融、保险、政府、地税、运营商等业务关键型系统的运维经验，曾担任公司异常恢复东区接口人，负责紧急异常恢复工作，目前负责国产化MogDB数据库的推广工作。

数据驱动，成就未来，云和恩墨，不负所托！

---

云和恩墨创立于2011年，以“数据驱动，成就未来”为使命，是智能的数据技术提供商。我们致力于将数据技术带给每个行业、每个组织、每个人，构建数据驱动的智能未来。

云和恩墨在数据承载（分布式存储、数据持续保护）、管理（数据库基础软件、数据库云管平台、数据技术服务）、加工（应用开发质量管控、数据模型管控、数字化转型咨询）和应用（数据服务化管理平台、数据智能分析处理、隐私计算）等领域为各个组织提供可信赖的产品、服务和解决方案，围绕用户需求，持续为客户创造价值，激发数据潜能，为成就未来敏捷高效的数字世界而不懈努力。