Tuning with SQL PATCH
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.
Feature 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.
- SQL patches in a stored procedure and global SQL patches cannot coexist.
- SQL patches cannot be used for precompiled statements that are executed using the PREPARE + EXECUTE syntax.
- 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.
- 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 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, 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 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: Run the SQL PATCH command 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 SQLpatch for the SQL statements in the 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 invoked 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)
Helpful Links
The following table lists the system catalogs and interface functions related to SQL PATCH.
|
Name |
Description |
|
|---|---|---|
|
System catalog |
GS_SQL_PATCH records the status information about all SQL patches. |
|
|
Interface function |
create_hint_sql_patch creates hint SQL patches and returns whether the execution is successful. |
|
|
create_abort_sql_patch creates abort SQL patches and returns whether the execution is successful. |
||
|
drop_sql_patch deletes SQL patches from the connected CN and returns whether the execution is successful. |
||
|
enable_sql_patch enables SQL patches on the connected CN and returns whether the execution is successful. |
||
|
disable_sql_patch disables SQL patches on the connected CN and returns whether the execution is successful. |
||
|
show_sql_patch displays the SQL patch corresponding to a specified patch name and returns the running result. |
||
|
create_hint_sql_patch creates hint SQL patches and returns whether the execution is successful. This function is an overloaded function of the original function. The value of parent_unique_sql_id can be used to limit the effective range of the hint patch. |
||
|
create_abort_sql_patch creates abort SQL patches and returns whether the execution is successful. This function is an overloaded function of the original function. The value of parent_unique_sql_id can be used to limit the effective range of the abort patch. |
||
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
