Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page

Tuning with SQL PATCH

Updated on 2024-05-07 GMT+08:00

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

  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 administrator, monitoring 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. 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 precompiled statements that are executed using the PREPARE + EXECUTE syntax.
  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 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.
  11. 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 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 a simple example.

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
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: 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', 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. 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>);

Helpful Links

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

Table 1 System functions, system catalogs, system views, and interface functions related to SQL PATCH

Category

Name

Description

System function

global_sql_patch_func()

SQL patch information on each global node, which is used to return the result of the global_sql_patch view.

System catalog

GS_SQL_PATCH

GS_SQL_PATCH records the status information about all SQL patches.

System view

GLOBAL_SQL_PATCH

GLOBAL_SQL_PATCH stores information about all SQL patches. This view is available only in the PG_CATALOG schema.

Interface function

DBE_SQL_UTIL Schema

DBE_SQL_UTIL.create_hint_sql_patch

create_hint_sql_patch creates hint SQL patches on the connected CN and returns whether the execution is successful.

DBE_SQL_UTIL.create_abort_sql_patch

create_abort_sql_patch creates abort SQL patches on the connected CN and 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.

DBE_SQL_UTIL.create_hint_sql_patch

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.

DBE_SQL_UTIL.create_abort_sql_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.

DBE_SQL_UTIL.create_remote_hint_sql_patch

create_remote_hint_sql_patch creates hint SQL patches on a specified CN and returns whether the execution is successful.

DBE_SQL_UTIL.create_remote_abort_sql_patch

create_remote_abort_sql_patch creates abort SQL patches on a specified CN and returns whether the execution is successful.

DBE_SQL_UTIL.drop_remote_sql_patch

drop_remote_sql_patch deletes SQL patches from a specified CN and returns whether the execution is successful.

DBE_SQL_UTIL.enable_remote_sql_patch

enable_remote_sql_patch enables SQL patches on a specified CN and returns whether the execution is successful.

DBE_SQL_UTIL.disable_remote_sql_patch

disable_remote_sql_patch disables SQL patches on a specified CN and returns whether the execution is successful.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback