Tuning with SQL PATCH

Updated on 2025-02-27 GMT+08:00

View PDF

SQL PATCH is designed for database administrators (DBAs), O&M personnel, and other roles who need to optimize SQL statements. If performance problems caused by poor plans of service statements are identified through other O&M views or fault locating methods, you can create an SQL patch to optimize service statements based on hints. Currently, the following hints are supported: number of rows, scanning mode, join mode, join sequence, PBE custom/generic plan selection, statement-level parameter setting, and parameterized path. In addition, in case that services are unavailable due to internal system errors that are triggered by specific statements, you can create SQL patches to rectify single-point failures without changing service statements. In this way, errors can be reported in advance to avoid greater loss.

Constraints

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.
Only hints that do not change SQL semantics can be used as patches. SQL rewriting is not supported.
This tool is not applicable to logical backup and restoration.
The patch validity cannot be verified during patch creation. If the patch hint has syntax or semantic errors, the query execution is not affected.
Only the initial user, O&M administrator, monitoring administrator, and system administrator have the permission to perform this operation.
Patches are not shared between databases. When creating SQL patches, you need to connect to the target database.
In the centralized deployment scenario where the standby node is readable, you must specify the primary node to run the SQL PATCH command to create, modify, or delete functions and the standby node to report errors.
There is a delay in synchronizing an SQL patch to the standby node. The patch takes effect after the standby node replays related logs.
This function does not take effect for SQL statements in stored procedures because no unique SQL ID is generated for statements in stored procedures.
It is not recommended that the abort patch 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, 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.
Currently, except DML statements, unique SQL IDs of SQL statements (such as CREATE TABLE) are generated by hashing the statement text. Therefore, SQL PATCH is sensitive to uppercase and lowercase letters, spaces, and line breaks. That is, even statements of different texts are semantically relative, you still need to create different SQL patches for them. For DML operations, SQL PATCH can take effect for the same statement with different input parameters, regardless of uppercase letters, lowercase letters, and spaces.

Examples

SQL PATCH is implemented based on the unique SQL ID. Therefore, to use SQL PATCH, related O&M parameters must be enabled for the SQL patch to take effect. The unique SQL ID can be obtained from both the WDR and slow SQL view. You must specify the unique SQL ID when creating an SQL patch. The following provides a simple example.

The following provides two simple examples.

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

    
     
       
       openGauss=# create table hint_t1(a int);
CREATE TABLE
openGauss=# create index on hint_t1(a);
CREATE INDEX
openGauss=# set track_stmt_stat_level = 'L1,L1'; -- Enable full SQL statistics.
SET
openGauss=# select * from hint_t1 t1 where t1.a = 1; -- Execute the SQL statement.
 a | b | c
---+---+---
 1 | 1 | 1
(1 row)
openGauss=# 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 | 2578396627
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 = '***')
                |
                |
openGauss=# select * from dbe_sql_util.create_hint_sql_patch('patch1', 2578396627, 'indexscan(t1)'); -- Specify a hint patch for the specified unique SQL ID.
-[ RECORD 1 ]---------+--
create_hint_sql_patch | t
openGauss=# 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)
openGauss=# select * from hint_t1 t1 where t1.a = 1; -- Execute the statement again.
 a | b | c
---+---+---
 1 | 1 | 1
(1 row)
openGauss=# 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 | 2578396627
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 | 2578396627
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: Run the SQL PATCH command to report an error for a specific statement in advance.

openGauss=# select * from dbe_sql_util.drop_sql_patch('patch1'); -- Delete patch 1.
 drop_sql_patch
----------------
 t
(1 row)
openGauss=# select * from dbe_sql_util.create_abort_sql_patch('patch2', 2578396627); -- Create an abort patch for the unique SQL ID of the statement.
 create_abort_sql_patch
------------------------
 t
(1 row)

openGauss=# 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

Helpful Links

The following table lists the system catalogs and interface functions related to SQL PATCH.

**Table 1** System catalogs and interface functions related to SQL PATCH
Name		Description
System catalog	GS_SQL_PATCH	GS_SQL_PATCH records the status information about all SQL patches.
Interface functions DBE_SQL_UTIL Schema	DBE_SQL_UTIL.create_hint_sql_patch	create_hint_sql_patch is an interface function used to create SQL patches for hints. It returns whether the execution is successful.
	DBE_SQL_UTIL.create_abort_sql_patch	create_abort_sql_patch is an interface function used to create abort SQL patches. It returns whether the execution is successful.
	DBE_SQL_UTIL.drop_sql_patch	drop_sql_patch deletes SQL patches from the connected CN and returns whether the execution is successful.
	DBE_SQL_UTIL.enable_sql_patch	enable_sql_patch enables SQL patches on the connected CN and returns whether the execution is successful.
	DBE_SQL_UTIL.disable_sql_patch	disable_sql_patch disables SQL patches on the connected CN and returns whether the execution is successful.
	DBE_SQL_UTIL.show_sql_patch	show_sql_patch displays the SQL patch corresponding to a specified patch name and returns the running result.