1 業務背景
GaussDB的優化器生成計劃不優的情況下，對DBA調優過程中不對業務sql修改場景下，提供3種計劃管理，分別為plan hint,sql patch,spm。
2 sql patch的使用場景
在發現查詢語句的執行計劃、執行方式未達預期的場景下，可以通過創建查詢補丁的方式，?使用?Hint對查詢計劃進行調優或對特定的語句進行報錯短路處理。
SQL??Patch能夠在避免直接修改用戶業務語句的前提下對查詢執行的方式做一定調整。
3 使用前提
1)、SQL Patch的實現基于Unique SQL ?ID，所以需要打開相關的運維參數才可以生效 （ enable_resource_track = on, ? instr_unique_sql_count > 0）。
2)、Unique SQL ?ID在WDR報告和慢SQL視圖中都可以獲取到，在創建SQL ?Patch時需要指定Unique SQL ?ID 。
3)、對于存儲過程內的SQL則需要設置參數 instr_unique_sql_track_type ?= ?'all' 后在 dbe_perf.statement_history視圖中查詢Unique SQL ID 。
4 使用舉例
步驟1：創建測試數據
[Ruby@dtest1 ~]$ gsql -h xx.xxx.xx.71 -dcsdn -p8000 -U csdn -W '******' -ar
gsql ((GaussDB Kernel 505.2.1 build 159cea95) compiled at 2024-12-27 09:22:44 commit 10161 last mr 21504 release)
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
Type "help" for help.csdn=>
csdn=> set search_path=csdn;
SET
csdn=> CREATE TABLE hint_t1(a int, b int, c int);
CREATE TABLE
csdn=> CREATE INDEX ON hint_t1(a);
CREATE INDEX
csdn=> INSERT INTO hint_t1 VALUES(1, 1, 1);
INSERT 0 1步驟2：打開FullSQL統計信息(track_stmt_stat_level:L1,L1)
csdn=> SET track_stmt_stat_level = 'L1,L1';
SET
csdn=> SET explain_perf_mode = normal;
SET步驟3：登入postgres庫獲取unique sql id
gsql -h ***.***.***71 -p 8000 -d postgres -U root -W ******* -ar
gsql ((GaussDB Kernel 505.2.1 build 159cea95) compiled at 2024-12-27 09:22:44 commit 10161 last mr 21504 release)
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
Type "help" for help.gaussdb=> SELECT unique_query_id, query, query_plan FROM dbe_perf.statement_history WHERE query LIKE '%hint_t1%';unique_query_id | query | query_plan-----------------+-------------------------------------------+-------------------------------------------------------------------
----------868389431 | SELECT * FROM hint_t1 t1 WHERE t1.a = ?; | Datanode Name: dn_6001_6002_6003+| | 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=10width=0)+| | Index Cond: (a = '***')+| |+| |
(1 row)
得到的unique sql id為：868389431
步驟4：登入業務庫，對指定SQL使用SQL?Patch
SELECT * FROM dbe_sql_util.create_hint_sql_patch('patch1', 868389431, 'indexscan(t1)');create_hint_sql_patch
-----------------------t
(1 row)csdn=> set search_path=csdn;
SET
步驟5：驗證計劃結果：
explain SELECT * FROM hint_t1 t1 WHERE t1.a = 1;
SET
NOTICE: Plan influenced by SQL hint patchid | operation | E-rows | E-width | E-costs
----+--------------------------------------------------+--------+---------+---------------1 | -> Index Scan using hint_t1_a_idx on hint_t1 t1 | 10 | 12 | 0.000..32.425
(1 row)Predicate Information (identified by plan id)
----------------------------------------------------1 --Index Scan using hint_t1_a_idx on hint_t1 t1Index Cond: (a = 1)
(2 rows)====== Query Others =====
---------------------------Bypass: Yes
(1 row)
執行計劃結果：Plan influenct by SQL hint patch
步驟6:查看sql patch
csdn=> select * from dbe_sql_util.show_sql_patch('patch1');unique_sql_id | enable | abort | hint_str
---------------+--------+-------+---------------868389431 | t | f | indexscan(t1)
(1 row)csdn=>
步驟7：禁用sql patch
csdn=> select * from dbe_sql_util.disable_sql_patch('patch1');disable_sql_patch
-------------------t
(1 row)csdn=>
步驟8：驗證禁用之后sql patch
csdn=> explain SELECT * FROM hint_t1 t1 WHERE t1.a = 1;id | operation | E-rows | E-width | E-costs
----+----------------------------------------------+--------+---------+---------------1 | -> Bitmap Heap Scan on hint_t1 t1 | 10 | 12 | 4.328..14.8832 | -> Bitmap Index Scan using hint_t1_a_idx | 10 | 0 | 0.000..4.325
(2 rows)Predicate Information (identified by plan id)
-----------------------------------------------1 --Bitmap Heap Scan on hint_t1 t1Recheck Cond: (a = 1)2 --Bitmap Index Scan using hint_t1_a_idxIndex Cond: (a = 1)
(4 rows)csdn=>
講解)
