<< 返回文章列表

MogDB数据库新特性之SQL PATCH绑定执行计划

2024年3月8日
M
o
g
D
B
,
数
据
库
技
术
,
S
Q
L
执
行
计
划
,
数
据
库
特
性
许玉晨
26

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     |    1 data_2     |    2 | data_2     |    2 data_3     |    3 | data_3     |    3 data_4     |    4 | data_4     |    4 data_5     |    5 | data_5     |    5 data_6     |    6 | data_6     |    6 data_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 name3366573496 --unique_query_idindexscan(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     |    1 data_2     |    2 | data_2     |    2 data_3     |    3 | data_3     |    3 data_4     |    4 | data_4     |    4 data_5     |    5 | data_5     |    5 data_6     |    6 | data_6     |    6 data_7     |    7 | data_7     |    7 。。。。
explain select * from t1 a, t2 b where a.id = b.id;NOTICE:  Plan influenced by SQL hint patch                                  QUERY 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') starttimefrom dbe_perf.statement_history where unique_query_id = 3366573496order by start_time; Datanode Name: dn_6001_6002                                                 +| 20240119-10:09:54 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)+|    ->  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 patch drop_sql_patch---------------- t(1 row)MogDB=# select * from dbe_sql_util.create_abort_sql_patch('patch2', 3366573496); -- 对该语句的Unique SQL ID创建Abort Patch create_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 patchcall dbe_sql_util.disable_sql_patch('enmo patch');
执行SQL并检查执行计划是否恢复原始状态：
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
MogDB=# select  query_plan,to_char(start_time,'yyyymmdd-hh24:mi:ss') starttimeMogDB-#  from dbe_perf.statement_history MogDB-#  where  unique_query_id = 3366573496MogDB-#  order by start_time;                                query_plan                                |     starttime     --------------------------------------------------------------------------+------------------- Datanode Name: dn_6001_6002                                             +| 20240119-13:24:52 Nested 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:49 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)     +|                                                                          +| 
最新的执行计划已经还原成hash jion。
MogDB 数据库官方文档参考：