Performing Optimization with SQL Patches

Restrictions

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. SQL patches cannot be created on DNs.
5. Only the initial user, O&M administrators, monitor administrators, and system administrators 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. If the CN where the SQL PATCH is created is removed and a full build is triggered, the SQL PATCH in the target CN of the full build is inherited. Therefore, you are advised to create the corresponding SQL PATCH on each CN.
7. CNs do not share SQL patches because their unique SQL IDs are different. You need to manually create SQL patches on different CNs.
8. SQL patches in a stored procedure and global SQL patches cannot coexist.
9. SQL patches cannot be used for prepared statement that are executed using the PREPARE + EXECUTE syntax. There are special cases. For details, see Special Cases.
10. 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 optimization, 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.
11. 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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

gaussdb=#  create table hint_t1(a int, b int, c int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# create index on hint_t1(a);
CREATE INDEX
gaussdb=# insert into hint_t1 values(1,1,1);
INSERT 0 1
gaussdb=# analyze hint_t1;
ANALYZE
gaussdb=# set track_stmt_stat_level = 'L1,L1'; -- Enable full SQL statistics.
SET
gaussdb=#  set enable_fast_query_shipping = off; -- Disable statement pushdown so that plans are generated on the CN.
SET
gaussdb=# set explain_perf_mode = normal; -- Adjust the plan display format.
SET
gaussdb=#  select * from hint_t1 where hint_t1.a = 1; -- Execute SQL statements.
 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. This statement needs to query the slow SQL view dbe_perf.statement_history in the postgres database.
-[ RECORD 1 ]---+-------------------------------------------------------------
unique_query_id | 3929365485
query           | select * from hint_t1 where hint_t1.a = ?;
query_plan      | Coordinator Name: coordinator1
                | Streaming (type: GATHER)  (cost=0.06..1.11 rows=1 width=12)
                |   Node/s: datanode1
                |   ->  Seq Scan on hint_t1  (cost=0.00..1.01 rows=1 width=12)
                |         Filter: (a = '***')
                |
                |
gaussdb=# \x -- Disable the extended display mode.

gaussdb=# select * from dbe_sql_util.create_hint_sql_patch('patch1', 3929365485, 'indexscan(hint_t1)');
 create_hint_sql_patch
-----------------------
 t
(1 row)
gaussdb=# set track_stmt_stat_level = 'L1,L1'; -- Reset parameters after the switching.
SET
gaussdb=# set enable_fast_query_shipping = off;
SET
gaussdb=# explain select * from hint_t1 where hint_t1.a = 1;
NOTICE:  Plan influenced by SQL hint patch
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=0.06..8.36 rows=1 width=12)
   Node/s: datanode1
   ->  Index Scan using hint_t1_a_idx on hint_t1  (cost=0.00..8.27 rows=1 width=12)
         Index Cond: (a = 1)
(4 rows)

gaussdb=# select * from hint_t1 where hint_t1.a = 1; -- Run 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 | 3929365485
query           | select * from hint_t1 where hint_t1.a = ?;
query_plan      | Coordinator Name: coordinator1
                | Streaming (type: GATHER)  (cost=0.06..1.11 rows=1 width=12)
                |   Node/s: datanode1
                |   ->  Seq Scan on hint_t1  (cost=0.00..1.01 rows=1 width=12)
                |         Filter: (a = '***')
                |
                |
-[ RECORD 2 ]---+-----------------------------------------------------------------------------------
unique_query_id | 3929365485
query           | select * from hint_t1 where hint_t1.a = ?;
query_plan      | Coordinator Name: coordinator1
                | Streaming (type: GATHER)  (cost=0.06..8.36 rows=1 width=12)
                |   Node/s: datanode1
                |   ->  Index Scan using hint_t1_a_idx on hint_t1  (cost=0.00..8.27 rows=1 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', 3929365485); -- Create an abort patch for the unique SQL ID of the statement.
 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);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# insert into test_proc_patch values(1,2);
INSERT 0 1
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=# 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 = ?; |                                                          |                    0
      2502737203 | call mypro();                              | Coordinator Name: cn1                                   +|                    0
                 |                                            | Function Scan on mypro  (cost=0.25..0.26 rows=1 width=4)+|
                 |                                            |                                                         +|
                 |                                            |                                                          |
      2859505004 | select b from test_proc_patch where a = ?; | Coordinator Name: cn1                                   +|           2502737203
                 |                                            | Data Node Scan  (cost=0.00..0.00 rows=0 width=0)        +|
                 |                                            |   Node/s: datanode1                                     +|
                 |                                            |                                                         +|
                 |                                            |                                                          |
(3 rows)

gaussdb=# select * from dbe_sql_util.create_abort_sql_patch('patch1',2859505004,2502737203);  -- Restrict that the abort patch takes effect only for statements in the stored procedure.
 create_abort_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';  -- Check whether the patch is correctly created and takes effect.
 patch_name | unique_sql_id | parent_unique_sql_id | enable | abort | hint_string
------------+---------------+----------------------+--------+-------+-------------
 patch1     |    2859505004 |           2502737203 | t      | t     |
(1 row)

gaussdb=# select b from test_proc_patch where a = 1;
 b
---
 2
(1 row)

gaussdb=# call mypro();
ERROR:  Statement 2859505004 canceled by abort patch patch1
CONTEXT:  SQL statement "select b          from test_proc_patch where a = 1"
PL/SQL function mypro() line 3 at SQL statement

Scenario 4: Install the SQL patch for the same slow SQL statement on each CN.

-- Find the slow SQL statement and plan on each node. (This function requires the monadmin permission.)
select node_name, unique_query_id, start_time, query, query_plan from dbe_perf.get_global_full_sql_by_timestamp(<start_time>, <end_time>);

-- Observe and analyze the returned slow SQL statement and plan, and perform local optimization and verification to obtain a proper hint_str.

-- Run the following statement on any CN to create an SQL patch, where node_name and unique_query_id are obtained from step 1:
select * from dbe_sql_util.create_remote_hint_sql_patch(<node_name>, <patch_name>, <unique_query_id>, <hint_str>);

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 prepared statement 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 functions, system catalogs, system views, and functions related to the SQL patch, see Table 1 System functions, system catalogs, system views, and functions related to SQL PATCH.