使用SQL PATCH进行调优

SQL PATCH主要设计给DBA、运维人员及其他需要对SQL进行调优的角色使用，用户通过其他运维视图或定位手段识别到业务语句存在计划不优导致的性能问题时，可以通过创建SQL PATCH对业务语句进行基于hint的调优。目前支持行数、扫描方式、连接方式、连接顺序、PBE custom/generic计划选择、语句级参数设置、参数化路径的hint。此外，对于部分由特定语句触发系统内部问题导致系统可服务性受损的语句，在不对业务语句变更的情况下，也可以通过创建用于单点规避的SQL PATCH，对问题场景提前报错处理，避免更大的损失。

特性约束

仅支持针对Unique SQL ID添加补丁，如果存在Unique SQL ID冲突，用于hint调优的SQL PATCH可能影响性能，但不影响语义正确性。
仅支持不改变SQL语义的hint作为PATCH，不支持SQL改写。
不支持逻辑备份、恢复。
不支持创建时校验PATCH合法性，如果PATCH的hint存在语法或语义错误，不影响查询正确执行。
仅初始用户、运维管理员、监控管理员、系统管理员用户有权限执行。
库之间不共享，创建SQL PATCH时需要连接目标库。
配置集中式备机可读时，需要指定主机执行SQL PATCH创建/修改/删除函数调用，备机执行报错。
SQL PATCH同步给备机存在一定延迟，待备机回放相关日志后PATCH生效。
限制在存储过程内的SQL PATCH和全局的SQL PATCH不允许同时存在。
使用PREPARE + EXECUTE语法执行的预编译语句执行不支持使用SQL PATCH。存在特殊情况，请参见特殊说明。
SQL PATCH不建议在数据库中长期使用，只应该作为临时规避方法。遇到内核问题所导致的特定语句触发数据库服务不可用问题，以及使用hint进行调优的场景，需要尽快修改业务或升级内核版本解决问题。并且升级后由于Unique SQL ID生成方法可能变化，可能导致规避方法失效。
当前，除DML语句之外，其他SQL语句（如CREATE TABLE等）的Unique SQL ID是对语句文本直接哈希生成的，所以对于此类语句，SQL PATCH对大小写、空格、换行等敏感，即不同文本的语句，即使语义相同，仍然需要对应不同的SQL PATCH。对于DML，则同一个SQL PATCH可以对不同入参的语句生效，并且忽略大小写和空格。

示例

当前SQL PATCH的实现基于Unique SQL ID，所以需要打开相关的运维参数才可以生效（enable_resource_track = on，instr_unique_sql_count > 0），Unique SQL ID在WDR报告和慢SQL视图中都可以获取到，在创建SQL PATCH时需要指定Unique SQL ID，对于存储过程内的SQL则需要设置参数instr_unique_sql_track_type = 'all'后在dbe_perf.statement_history视图中查询Unique SQL ID。

下面给出简单的使用样例。

场景一：使用SQL PATCH对特定语句进行hint调优。

    
     
       
       
         gaussdb=# CREATE TABLE hint_t1(a int, b int, c int);
CREATE TABLE
gaussdb=# CREATE INDEX ON hint_t1(a);
CREATE INDEX
gaussdb=# INSERT INTO hint_t1 VALUES(1,1,1);
INSERT 0 1
gaussdb=# sEt track_stmt_stat_level = 'L1,L1'; --打开FullSQL统计信息
SET
gaussdb=# SET explain_perf_mode = normal;
SET
gaussdb=# SELECT * FROM hint_t1 t1 WHERE t1.a = 1; --执行SQL语句
 a | b | c
---+---+---
 1 | 1 | 1
(1 row)
gaussdb=# \x --切换扩展显示模式，便于观察计划
Expanded display is on.
gaussdb=# SELECT unique_query_id, query, query_plan FROM dbe_perf.statement_history WHERE query LIKE '%hint_t1%'; --获取查询计划和Unique SQL ID
-[ RECORD 1 ]---+----------------------------------------------------------------------------
unique_query_id | 2311517824
query           | select * from hint_t1 t1 where t1.a = ?;
query_plan      | Datanode Name: sgnode
                | Bitmap Heap Scan on hint_t1 t1  (cost=4.33..14.88 rows=10 width=12)
                |   Recheck Cond: (a = '***')
                |   ->  Bitmap Index Scan on hint_t1_a_idx  (cost=0.00..4.33 rows=10 width=0)
                |         Index Cond: (a = '***')
                |
                |
gaussdb=# \x
Expanded display is off.
gaussdb=# SELECT * FROM dbe_sql_util.create_hint_sql_patch('patch1', 2311517824, 'indexscan(t1)'); -- 对指定的Unique SQL ID指定Hint Patch
 create_hint_sql_patch
-----------------------
 t
(1 row)

gaussdb=# EXPLAIN SELECT * FROM hint_t1 t1 WHERE t1.a = 1; -- 通过explain可以确认Hint是否生效
NOTICE:  Plan influenced by SQL hint patch
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 [Bypass]
 Index Scan using hint_t1_a_idx on hint_t1 t1  (cost=0.00..32.43 rows=10 width=12)
   Index Cond: (a = 1)
(3 rows)
gaussdb=# SELECT * FROM hint_t1 t1 WHERE t1.a = 1; -- 再次执行语句
 a | b | c
---+---+---
 1 | 1 | 1
(1 row)
gaussdb=# \x
Expanded display is on.
gaussdb=# SELECT unique_query_id, query, query_plan from dbe_perf.statement_history WHERE query LIKE '%hint_t1%'; -- 可以看到新的执行记录计划已改变
-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------
unique_query_id | 2311517824
query           | select * from hint_t1 t1 where t1.a = ?;
query_plan      | Datanode Name: sgnode
                | Bitmap Heap Scan on hint_t1 t1  (cost=4.33..15.70 rows=10 p-time=0 p-rows=0 width=12)
                |   Recheck Cond: (a = '***')
                |   ->  Bitmap Index Scan on hint_t1_a_idx  (cost=0.00..4.33 rows=10 p-time=0 p-rows=0 width=0)
                |         Index Cond: (a = '***')
                |
                |
-[ RECORD 2 ]---+--------------------------------------------------------------------------------------------------
unique_query_id | 2311517824
query           | select * from hint_t1 t1 where t1.a = ?;
query_plan      | Datanode Name: sgnode
                | Index Scan using hint_t1_a_idx on hint_t1 t1  (cost=0.00..8.27 rows=1 p-time=0 p-rows=0 width=12)
                |   Index Cond: (a = '***')
                |
                |

        

      

    
   

场景二：使用SQL PATCH对特定语句进行提前报错规避。

gaussdb=# SELECT * FROM dbe_sql_util.drop_sql_patch('patch1'); -- 删去patch1
 drop_sql_patch
----------------
 t
(1 row)
gaussdb=# SELECT * FROM dbe_sql_util.create_abort_sql_patch('patch2', 2311517824); 对该语句的Unique SQL ID创建Abort Patch
 create_abort_sql_patch
------------------------
 t
(1 row)

gaussdb=# SELECT * FROM hint_t1 t1 WHERE t1.a = 1; -- 再次执行语句会提前报错
ERROR:  Statement 2578396627 canceled by abort patch patch2

场景三：针对存储过程内的SQL语句创建SQL PATCH。

gaussdb=# CREATE TABLE test_proc_patch(a int,b int);
CREATE TABLE
gaussdb=# INSERT INTO test_proc_patch VALUES(1,2);
INSERT 0 1
gaussdb=# CREATE INDEX test_a ON test_proc_patch(a);
CREATE INDEX
gaussdb=# CREATE PROCEDURE mypro() AS num int;
gaussdb$# BEGIN
gaussdb$# SELECT b INTO num FROM test_proc_patch WHERE a = 1;
gaussdb$# END;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# SET track_stmt_stat_level = 'L0,L1';  -- 打开记录统计信息
SET
gaussdb=# SELECT b FROM test_proc_patch WHERE a = 1;
 b
---
 2
(1 row)
gaussdb=# CALL mypro();
 mypro
-------

(1 row)
gaussdb=# SET track_stmt_stat_level = 'OFF,L0';    -- 暂时关闭记录统计信息
SET
gaussdb=# SELECT unique_query_id, query, query_plan, parent_unique_sql_id FROM dbe_perf.statement_history WHERE query LIKE '%call mypro();%' OR query LIKE '%test_proc_patch%';
 unique_query_id |                   query                    |                          query_plan                           | parent_unique_sql_id
-----------------+--------------------------------------------+---------------------------------------------------------------+----------------------
      2859505004 | select b from test_proc_patch where a = ?; | Datanode Name: sgnode                                        +|                    0
                 |                                            | Seq Scan on test_proc_patch  (cost=0.00..1.01 rows=1 width=4)+|
                 |                                            |   Filter: (a = '***')                                        +|
                 |                                            |                                                              +|
                 |                                            |                                                               |
      3460545602 | call mypro();                              | Datanode Name: sgnode                                        +|                    0
                 |                                            | Function Scan on mypro  (cost=0.25..0.26 rows=1 width=4)     +|
                 |                                            |                                                              +|
                 |                                            |                                                               |
      2859505004 | select b from test_proc_patch where a = ?; | Datanode Name: sgnode                                        +|           3460545602
                 |                                            | Seq Scan on test_proc_patch  (cost=0.00..1.01 rows=1 width=4)+|
                 |                                            |   Filter: (a = '***')                                        +|
                 |                                            |                                                              +|
                 |                                            |                                                               |
(3 rows)

-- 根据parentid可以调用重载函数限制存储过程内生效
gaussdb=# SELECT * FROM dbe_sql_util.create_hint_sql_patch('patch1',2859505004,3460545602,'indexscan(test_proc_patch)');
 create_hint_sql_patch
-----------------------
 t
(1 row)

gaussdb=# SELECT patch_name,unique_sql_id,parent_unique_sql_id,enable,abort,hint_string FROM gs_sql_patch WHERE patch_name = 'patch1';  -- 确认SQLpatch记录没有错误
 patch_name | unique_sql_id |  parent_unique_sql_id | enable | abort |      hint_string
------------+---------------+-----------------------+--------+-------+---------------------------
 patch1     |    2859505004 |            3460545602 | t      | f     | indexscan(test_proc_patch)
(1 row)
gaussdb=# SET track_stmt_stat_level = 'L0,L1';  -- 打开记录统计信息
gaussdb=# SELECT b FROM test_proc_patch where a = 1;
 b
---
 2
(1 row)
gaussdb=# call mypro();
 mypro
-------

(1 row)
gaussdb=# SELECT unique_query_id, query, query_plan, parent_unique_sql_id FROM dbe_perf.statement_history where query like '%test_proc_patch%' order by start_time;
 unique_query_id |                   query                    |                                  query_plan                                  | parent_unique_sql_id
-----------------+--------------------------------------------+------------------------------------------------------------------------------+----------------------
      2859505004 | select b from test_proc_patch where a = ?; | Datanode Name: sgnode                                                       +|                    0
                 |                                            | Seq Scan on test_proc_patch  (cost=0.00..1.01 rows=1 width=4)               +|
                 |                                            |   Filter: (a = '***')                                                       +|
                 |                                            |                                                                             +|
                 |                                            |                                                                              |
      2859505004 | select b from test_proc_patch where a = ?; | Datanode Name: sgnode                                                       +|           3460545602
                 |                                            | Seq Scan on test_proc_patch  (cost=0.00..1.01 rows=1 width=4)               +|
                 |                                            |   Filter: (a = '***')                                                       +|
                 |                                            |                                                                             +|
                 |                                            |                                                                              |
      2859505004 | select b from test_proc_patch where a = ?; | Datanode Name: sgnode                                                       +|                    0
                 |                                            | Seq Scan on test_proc_patch  (cost=0.00..1.01 rows=1 width=4)               +|
                 |                                            |   Filter: (a = '***')                                                       +|
                 |                                            |                                                                             +|
                 |                                            |                                                                              |
      2859505004 | select b from test_proc_patch where a = ?; | Datanode Name: sgnode                                                       +|           3460545602
                 |                                            | Index Scan using test_a on test_proc_patch  (cost=0.00..8.27 rows=1 width=4)+|
                 |                                            |   Index Cond: (a = '***')                                                   +|
                 |                                            |                                                                             +|
                 |                                            |                                                                              |
(4 rows)

特殊说明

由示例可知，SQL PATCH需要正确的Unique SQL ID才可以正常使用，所以在通常情况下SQL PATCH不支持PREPARE + EXECUTE语法执行的预编译语句。

-- 通常情况下得到是带PREPARE的SQL ID，SQL PATCH无法使用
unique_query_id |                          query                           
-----------------+----------------------------------------------------------
       658407023 | prepare p1 as                                           +
                 | SELECT /*+ tablescan(rewrite_rule_hint_t1)*/*           +
                 | FROM rewrite_rule_hint_t1,                              +
                 |      (SELECT * FROM rewrite_rule_hint_t2 WHERE a > 1) tt+
                 | WHERE rewrite_rule_hint_t1.a = tt.a;

如果发生plan cache失效，plan cache会使用PREPARE内的语句重新生成一遍Unique SQL ID。如果使用此时的Unique SQL ID应用SQL PATCH，则SQL PATCH能正常使用。

-- 示例
-- 创建表
gaussdb=# DROP TABLE rewrite_rule_hint_t1;
gaussdb=# DROP TABLE rewrite_rule_hint_t2;
gaussdb=# CREATE TABLE rewrite_rule_hint_t1 (a int, b int, c int, d int);
gaussdb=# CREATE TABLE rewrite_rule_hint_t2 (a int, b int, c int, d int);

-- 打开FullSQL统计信息
gaussdb=# SET track_stmt_stat_level = 'L1,L1';

-- 清理sql_patch和环境
gaussdb=# SELECT dbe_sql_util.drop_sql_patch('patch1');
gaussdb=# DEALLOCATE ALL;

-- PRARARE
gaussdb=# PREPARE p1 AS SELECT * FROM rewrite_rule_hint_t1,(SELECT * FROM rewrite_rule_hint_t2 WHERE a > 1) tt WHERE rewrite_rule_hint_t1.a = tt.a;

-- 查看Unique SQL ID
gaussdb=# SELECT unique_query_id,query FROM dbe_perf.statement_history WHERE query LIKE '%rewrite_rule_hint%' ORDER BY finish_time DESC LIMIT 1;
 unique_query_id |                                                                   query                                                                   
-----------------+-------------------------------------------------------------------------------------------------------------------------------------------
        25719777 | prepare p1 as SELECT * FROM rewrite_rule_hint_t1,(SELECT * FROM rewrite_rule_hint_t2 WHERE a > 1) tt WHERE rewrite_rule_hint_t1.a = tt.a;

-- 此时的Unique SQL ID不能让SQL PATCH生效。

-- 插入数据并analyze使cache失效
gaussdb=# INSERT INTO rewrite_rule_hint_t1 VALUES(generate_series(1, 10000), generate_series(1, 10000), generate_series(1, 10000),generate_series(1, 10000));
gaussdb=# ANALYZE rewrite_rule_hint_t1;

-- 执行再次生成Unique SQL ID
gaussdb=# EXPLAIN EXECUTE p1(1);
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Hash Join  (cost=39.62..277.01 rows=592 width=32)
   Hash Cond: (rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a)
   ->  Seq Scan on rewrite_rule_hint_t1  (cost=0.02..169.00 rows=9999 width=16)
         Filter: (a > 1)
   ->  Hash  (cost=32.20..32.20 rows=592 width=16)
         ->  Seq Scan on rewrite_rule_hint_t2  (cost=0.00..32.20 rows=592 width=16)
               Filter: (a > 1)
(7 rows)

-- 查看新的Unique SQL ID
gaussdb=# SELECT unique_query_id,query FROM dbe_perf.statement_history WHERE query LIKE '%rewrite_rule_hint%' ORDER BY finish_time DESC LIMIT 1;
 unique_query_id |                                                                   query                                                                   
-----------------+-------------------------------------------------------------------------------------------------------------------------------------------
      1116101547 | prepare p1 as SELECT * FROM rewrite_rule_hint_t1,(SELECT * FROM rewrite_rule_hint_t2 WHERE a > ?) tt WHERE rewrite_rule_hint_t1.a = tt.a;

可以看到Unique SQL ID发生变化，其使用的是prepare内的SQL语句生成，此时的Unique SQL ID是可用的。

-- 使用SQL_PATCH
gaussdb=# SELECT * FROM dbe_sql_util.create_hint_sql_patch('patch1', 1116101547 , 'set(enable_hashjoin off) NO_EXPAND_SUBQUERY(@sel$2)');

-- 查看是否生效
gaussdb=# EXPLAIN EXECUTE p1(1);
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Merge Join  (cost=873.77..932.65 rows=592 width=32)
   Merge Cond: (rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a)
   ->  Sort  (cost=808.39..833.39 rows=10000 width=16)
         Sort Key: rewrite_rule_hint_t1.a
         ->  Seq Scan on rewrite_rule_hint_t1  (cost=0.00..144.00 rows=10000 width=16)
   ->  Sort  (cost=65.38..66.86 rows=592 width=16)
         Sort Key: rewrite_rule_hint_t2.a
         ->  Seq Scan on rewrite_rule_hint_t2  (cost=0.00..32.20 rows=592 width=16)
               Filter: (a > 1)
(9 rows)

可以看到SQL PATCH 生效生成相应计划。

表1 SQL PATCH相关系统表、接口函数介绍
名称	说明
系统表	GS_SQL_PATCH	GS_SQL_PATCH系统表存储所有SQL_PATCH的状态信息。
接口函数 DBE_SQL_UTIL Schema	DBE_SQL_UTIL.create_hint_sql_patch	create_hint_sql_patch是用于创建调优SQL PATCH的接口函数，返回执行是否成功。
DBE_SQL_UTIL.create_abort_sql_patch	create_abort_sql_patch是用于创建避险SQL PATCH的接口函数，返回执行是否成功。
DBE_SQL_UTIL.drop_sql_patch	drop_sql_patch是用于在当前建连的CN上删除SQL PATCH的接口函数，返回执行是否成功。
DBE_SQL_UTIL.enable_sql_patch	enable_sql_patch是用于在当前建连的CN上开启SQL PATCH的接口函数，返回执行是否成功。
DBE_SQL_UTIL.disable_sql_patch	disable_sql_patch是用于在当前建连的CN上禁用SQL PATCH的接口函数，返回执行是否成功。
DBE_SQL_UTIL.show_sql_patch	show_sql_patch是用于显示给定patch_name对应SQL PATCH的接口函数，返回运行结果。
DBE_SQL_UTIL.create_hint_sql_patch	create_hint_sql_patch是用于创建调优SQL PATCH的接口函数，返回执行是否成功。本函数是原函数的重载函数，支持通过parent_unique_sql_id值限制hint patch的生效范围。
DBE_SQL_UTIL.create_abort_sql_patch	create_abort_sql_patch是用于创建避险SQL PATCH的接口函数，返回执行是否成功。本函数是原函数的重载函数，支持通过parent_unique_sql_id值限制abort patch的生效范围。

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！您可在我的云声建议查看反馈及问题处理状态。

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消

如您有其它疑问，您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问

使用SQL PATCH进行调优

特性约束

示例

特殊说明

相关链接

相关文档

意见反馈

文档内容是否对您有帮助？

7*24

备案

专业服务

退订

建议反馈

售前咨询热线