El contenido no se encuentra disponible en el idioma seleccionado. Estamos trabajando continuamente para agregar más idiomas. Gracias por su apoyo.

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
Situation Awareness
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
Help Center/ GaussDB/ Developer Guide(Centralized_8.x)/ SQL Optimization/ Performing Optimization with SQL Patches

Performing Optimization with SQL Patches

Updated on 2024-08-20 GMT+08:00

SQL patches are 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 SQL patches 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.

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. 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 prepared statement 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 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.
  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 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;

However, if the plan cache becomes invalid, the plan cache uses the statement in PREPARE to generate a unique SQL ID again. If the unique SQL ID is used to apply the SQL patch, the SQL patch can be applied normally.

-- Example
-- Create a table.
gaussdb=# DROP TABLE rewrite_rule_hint_t1;
gaussdb=# DROP TABLE rewrite_rule_hint_t2;
gaussdb=# CREATE TABLE rewrite_rule_hint_t1 (a int, b int, c int, d int);
gaussdb=# CREATE TABLE rewrite_rule_hint_t2 (a int, b int, c int, d int);

-- Enable FullSQL statistics.
gaussdb=# SET track_stmt_stat_level = 'L1,L1';

-- Clear the sql_patch and environment.
gaussdb=# SELECT dbe_sql_util.drop_sql_patch('patch1');
gaussdb=# DEALLOCATE ALL;

-- PRARARE
gaussdb=# PREPARE p1 AS SELECT * FROM rewrite_rule_hint_t1,(SELECT * FROM rewrite_rule_hint_t2 WHERE a > 1) tt WHERE rewrite_rule_hint_t1.a = tt.a;

-- View the unique SQL ID.
gaussdb=# SELECT unique_query_id,query FROM dbe_perf.statement_history WHERE query LIKE '%rewrite_rule_hint%' ORDER BY finish_time DESC LIMIT 1;
 unique_query_id |                                                                   query                                                                   
-----------------+-------------------------------------------------------------------------------------------------------------------------------------------
        25719777 | prepare p1 as SELECT * FROM rewrite_rule_hint_t1,(SELECT * FROM rewrite_rule_hint_t2 WHERE a > 1) tt WHERE rewrite_rule_hint_t1.a = tt.a;

-- In this case, the unique SQL ID cannot make the SQL patch take effect.

-- Insert and analyze data to invalidate the cache.
gaussdb=# INSERT INTO rewrite_rule_hint_t1 VALUES(generate_series(1, 10000), generate_series(1, 10000), generate_series(1, 10000),generate_series(1, 10000));
gaussdb=# ANALYZE rewrite_rule_hint_t1;

-- Generate a unique SQL ID again:
gaussdb=# EXPLAIN EXECUTE p1(1);
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Hash Join  (cost=39.62..277.01 rows=592 width=32)
   Hash Cond: (rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a)
   ->  Seq Scan on rewrite_rule_hint_t1  (cost=0.02..169.00 rows=9999 width=16)
         Filter: (a > 1)
   ->  Hash  (cost=32.20..32.20 rows=592 width=16)
         ->  Seq Scan on rewrite_rule_hint_t2  (cost=0.00..32.20 rows=592 width=16)
               Filter: (a > 1)
(7 rows)

-- Check the new unique SQL ID.
gaussdb=# SELECT unique_query_id,query FROM dbe_perf.statement_history WHERE query LIKE '%rewrite_rule_hint%' ORDER BY finish_time DESC LIMIT 1;
 unique_query_id |                                                                   query                                                                   
-----------------+-------------------------------------------------------------------------------------------------------------------------------------------
      1116101547 | prepare p1 as SELECT * FROM rewrite_rule_hint_t1,(SELECT * FROM rewrite_rule_hint_t2 WHERE a > ?) tt WHERE rewrite_rule_hint_t1.a = tt.a;

The unique SQL ID changes and is generated using the SQL statement in PREPARE. In this case, the unique SQL ID is available.

-- Use SQL_PATCH.
gaussdb=# SELECT * FROM dbe_sql_util.create_hint_sql_patch('patch1', 1116101547 , 'set(enable_hashjoin off) NO_EXPAND_SUBQUERY(@sel$2)');

-- Check whether it takes effect.
gaussdb=# EXPLAIN EXECUTE p1(1);
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Merge Join  (cost=873.77..932.65 rows=592 width=32)
   Merge Cond: (rewrite_rule_hint_t1.a = rewrite_rule_hint_t2.a)
   ->  Sort  (cost=808.39..833.39 rows=10000 width=16)
         Sort Key: rewrite_rule_hint_t1.a
         ->  Seq Scan on rewrite_rule_hint_t1  (cost=0.00..144.00 rows=10000 width=16)
   ->  Sort  (cost=65.38..66.86 rows=592 width=16)
         Sort Key: rewrite_rule_hint_t2.a
         ->  Seq Scan on rewrite_rule_hint_t2  (cost=0.00..32.20 rows=592 width=16)
               Filter: (a > 1)
(9 rows)

The SQL patch takes effect and the corresponding plan is generated.

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.

Table 1 System catalogs and functions related to SQL patches

Name

Description

System catalog

GS_SQL_PATCH

GS_SQL_PATCH records the status information about all SQL patches.

Function

DBE_SQL_UTIL Schema

DBE_SQL_UTIL.create_hint_sql_patch

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

DBE_SQL_UTIL.create_abort_sql_patch

create_abort_sql_patch creates abort SQL patches 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 return 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.

Utilizamos cookies para mejorar nuestro sitio y tu experiencia. Al continuar navegando en nuestro sitio, tú aceptas nuestra política de cookies. Descubre más

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback