Tuning with SQL Patches

Feature Constraints

1. Patches can be created only by unique SQL ID. If unique SQL IDs conflict, SQL patches that are used for hint-based optimization may affect performance but do not affect semantic correctness.
2. Only hints that do not change SQL semantics can be used as patches. SQL rewriting is not supported.
3. This tool is not applicable to logical backup and restoration.
4. The patch validity cannot be verified during patch creation. If the patch hint has syntax or semantic errors, the query execution is not affected.
5. Only the initial user, O&M administrator, monitor administrator, and system administrator have the permission to perform this operation.
6. Patches are not shared between databases. When creating SQL patches, you need to connect to the target database.
7. In the centralized deployment scenario where the standby node is readable, you must specify the primary node to call functions to create, modify, or delete SQL patches and the standby node to report errors.
8. There is a delay in synchronizing an SQL patch to the standby node. The patch takes effect after the standby node replays related logs.
9. SQL patches in a stored procedure and global SQL patches cannot coexist.
10. SQL patches cannot be used for precompiled statements that are executed using the PREPARE + EXECUTE syntax. For details about special cases, see Special Cases.
11. It is not recommended that the SQL patches be used in the database for a long time. It should be used only as a workaround. If the database service is unavailable due to a kernel fault triggered by a specific statement or SQL hints are used for performance tuning, you must rectify the service fault or upgrade the kernel as soon as possible. After the upgrade, the method of generating unique SQL IDs may change. Therefore, the workaround may become invalid.
12. Currently, except DML statements, unique SQL IDs of SQL statements (such as CREATE TABLE) are generated by hashing the statement text. Therefore, SQL patches are sensitive to uppercase and lowercase letters, spaces, and linefeeds. That is, even statements of different texts have the same semantics, you still need to create different SQL patches for them. For DML operations, an SQL patch can take effect for the same statement with different input parameters, regardless of uppercase letters, lowercase letters, and spaces.

Example

The SQL patch is implemented based on the unique SQL ID. Therefore, you need to enable related O&M parameters (enable_resource_track = on, instr_unique_sql_count > 0) for the SQL patch to take effect. The unique SQL ID can be obtained from both the WDR and slow SQL view. You need to specify the unique SQL ID when creating the SQL patch. For SQL statements in a stored procedure, you need to set instr_unique_sql_track_type to 'all' and query unique SQL ID in the dbe_perf.statement_history view.

The following provides simple examples:

Scenario 1: Use an SQL patch to optimize specific statements based on hints.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

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'; -- Enable full SQL statistics.
SET
gaussdb=# SET explain_perf_mode = normal;
SET
gaussdb=# SELECT * FROM hint_t1 t1 WHERE t1.a = 1; -- Execute the SQL statement.
 a | b | c
---+---+---
 1 | 1 | 1
(1 row)
gaussdb=# \x -- Switch to the extended display mode to facilitate plan observation.
Expanded display is on.
gaussdb=# SELECT unique_query_id, query, query_plan FROM dbe_perf.statement_history WHERE query LIKE '%hint_t1%'; -- Obtain the query plan and 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)'); -- Specify a hint patch for the specified unique SQL ID.
 create_hint_sql_patch
-----------------------
 t
(1 row)

gaussdb=# EXPLAIN SELECT * FROM hint_t1 t1 WHERE t1.a = 1; -- Check whether the hint takes effect.
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; -- Execute the statement again.
 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%'; -- The query plan has been changed.
-[ 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 = '***')
                |
                |

Scenario 2: Use an SQL patch to report an error for a specific statement in advance.

gaussdb=# SELECT * FROM dbe_sql_util.drop_sql_patch('patch1'); -- Delete patch 1.
 drop_sql_patch
----------------
 t
(1 row)
gaussdb=# SELECT * FROM dbe_sql_util.create_abort_sql_patch('patch2', 2311517824); -- Create an abort patch for the statement of the unique SQL ID.
 create_abort_sql_patch
------------------------
 t
(1 row)

gaussdb=# SELECT * FROM hint_t1 t1 WHERE t1.a = 1; -- An error is reported in advance when the statement is executed again.
ERROR:  Statement 2578396627 canceled by abort patch patch2

Scenario 3: Create an SQL patch for SQL statements in a stored procedure.

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';  -- Open the statistics information.
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';    -- Temporarily disable the function of recording statistics.
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)

-- The overloaded function can be called based on parentid to restrict the effective range of the SQL patch inside the stored procedure.
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';  -- Verify that the SQL patch record is correct.
 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';  -- Open the statistics information.
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)

Special Cases

According to the example, SQL patches can be used only when the correct unique SQL IDs are used. Therefore, SQL patches do not support precompiled statements executed by the PREPARE + EXECUTE syntax.

-- Generally, an SQL ID with PREPARE is obtained. Therefore, the SQL patch cannot be used.
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;

Helpful Links

For details about the system catalogs and functions related to the SQL patch, see Table 1 System catalogs and functions related to SQL patches.