1 背景
由于業務數據的變化或者數據庫版本的升級，可能導致SQL的執行計劃發生變化，這種變化不一定是正收益，這時需?要一個防止計劃劣化的機制。該機制需適用于版本升級時固化計劃防止計劃跳變等場景。
2? SPM?的功能
SPM(SQL Plan?Manager)
功能：固化計劃防止計劃跳變，影響業務性能。
?計劃捕獲：SPM能將一個具體SQL的執行計劃落盤，稱之為Plan?baseline(計劃基線)?。
?計劃選擇：SPM會判斷是否將優化器給出的執行計劃交給執行器執行，選擇優化器給出的執行計劃，還是SPM存儲的計劃。
?計劃演進：SPM能將優化器新產生的計劃進行判斷，如果判斷優秀會標記（ACC&UNACC）以備計劃選擇使用。
3 SPM示例
步驟1：數據準備
csdn=> DROP TABLE IF EXISTS tb_a;
DROP TABLE
csdn=> CREATE TABLE tb_a (id int, c1 int, c2 int, pad text);
CREATE TABLE
csdn=> CREATE INDEX tb_a_idx_c1 ON tb_a (c1);
CREATE INDEX
csdn=> INSERT INTO tb_a select id, (random()*200)::int,(random()*10000)::int, 'ss' FROM (SELECT generate_series(1,10000) id) tb_a;
INSERT 0 10000
csdn=> ANALYZE tb_a;
ANALYZE
csdn=>
步驟2：參數設置
csdn=> SET spm_enable_plan_capture=manual; -- 開啟SPM計劃選擇
SET
csdn=> SET spm_enable_plan_selection=on; -- 當前SPM只支持gplan，確保生成的計劃是gplan
SET
csdn=> SET plan_cache_mode = 'force_generic_plan'; -- 在pretty模式可以看到baseline的使用情況
SET
csdn=>
csdn=> SET explain_perf_mode=pretty; --設置Oracle查看計劃顯示格風
SET
csdn=>
步驟3：計劃捕獲
-- 捕獲tablescan，確保捕獲tablescan計劃
csdn=> SET enable_seqscan=on;
SET
csdn=>
csdn=> SET enable_indexscan=off;
SET
csdn=> SET enable_bitmapscan=off;
SET
csdn=>
-- 執行測試sql
csdn=> PREPARE spm_query AS SELECT * FROM tb_a WHERE c1 = $1;
PREPARE
csdn=> EXPLAIN(costs off) EXECUTE spm_query (1);id | operation
----+----------------------1 | -> Seq Scan on tb_a
(1 row)Predicate Information (identified by plan id)
-----------------------------------------------1 --Seq Scan on tb_aFilter: (c1 = $1)
(2 rows)csdn=>csdn=> SELECT sql_hash, plan_hash, outline, status, gplan FROM gs_spm_sql_baseline WHERE sql_text LIKE '%tb_a WHERE c1 = $1%';sql_hash | plan_hash | outline | status | gplan
------------+-----------+----------------------------------------+--------+-------1850279601 | 154472964 | begin_outline_data +| ACC | t| | TableScan(@"sel$1" csdn.tb_a@"sel$1")+| || | version("1.0.0") +| || | end_outline_data | |
(1 row)csdn=>
步驟4：計劃選擇
csdn=> SET enable_bitmapscan=on; -- 確保優化器生成的計劃是bitmapscan
SET
csdn=> SET enable_seqscan=off;
SET
csdn=> SET enable_indexscan=off;
SET
-- 執行SQL
csdn=> DEALLOCATE spm_query;
DEALLOCATE
csdn=> PREPARE spm_query AS SELECT * FROM tb_a WHERE c1 = $1;
PREPARE
csdn=> SET plan_cache_mode = 'force_generic_plan'; --強制走軟解析
SET
csdn=> show plan_cache_mode;plan_cache_mode
--------------------force_generic_plan
(1 row)csdn=> EXPLAIN(costs off) EXECUTE spm_query (1);id | operation
----+--------------------------------------------1 | -> Bitmap Heap Scan on tb_a2 | -> Bitmap Index Scan using tb_a_idx_c1
(2 rows)Predicate Information (identified by plan id)
-----------------------------------------------1 --Bitmap Heap Scan on tb_aRecheck Cond: (c1 = $1)2 --Bitmap Index Scan using tb_a_idx_c1Index Cond: (c1 = $1)
(4 rows)====== Query Others =====
----------------------------------------------------------------use_baseline: Yes, sql_hash: 3237163112, plan_hash: 2994191517--查看現有計劃
csdn=> SELECT sql_hash, plan_hash, outline, status, gplan, cost
csdn-> FROM gs_spm_sql_baseline
csdn-> WHERE sql_text like '%tb_a WHERE c1 = $1%'
csdn-> ORDER BY creation_time;sql_hash | plan_hash | outline | status | gplan | cost
------------+------------+-----------------------------------------------------+--------+-------+--------1850279601 | 154472964 | begin_outline_data +| ACC | t | 166| | TableScan(@"sel$1" csdn.tb_a@"sel$1") +| | || | version("1.0.0") +| | || | end_outline_data | | |3237163112 | 2994191517 | begin_outline_data +| ACC | t | 48.451| | BitmapScan(@"sel$1" csdn.tb_a@"sel$1" tb_a_idx_c1)+| | || | version("1.0.0") +| | || | end_outline_data | | |
(2 rows)csdn=>
注：從查看現有的計劃，cost成本為48.451是正能量，對應的sql_hash、plan_hash分別為：
3237163112、?2994191517
步驟5：計劃演進
我理解為將最優的計劃打上ACC標簽，不優的計劃打上UNACC即可。
-- 使用spm 計劃演進
csdn=> SELECT * FROM dbe_sql_util.gs_spm_evolute_plan(3237163112,2994191517);evolute_status
----------------t
(1 row)csdn=> SELECT sql_hash, plan_hash, better, refer_plan, reason FROM gs_spm_sql_evolution WHERE sql_hash=3237163112;sql_hash | plan_hash | better | refer_plan | reason
------------+------------+--------+------------+-----------------------------------------------------------3237163112 | 2994191517 | f | 0 | execution time is more than 10% greater than the baseline-- 根據演進結論修改seqscan計劃狀態為UNACC
csdn=> SELECT * FROM dbe_sql_util.gs_spm_set_plan_status (1850279601,154472964,'UNACC');gs_spm_set_plan_status
------------------------t
(1 row)csdn=>
--查看UNACC
csdn=> SELECT sql_hash, plan_hash, outline, status, gplan, costFROM gs_spm_sql_baselineWHERE sql_text like '%tb_a WHERE c1 = $1%'ORDER BY creation_time;sql_hash | plan_hash | outline | status | gplan | cost
------------+------------+-----------------------------------------------------+--------+-------+--------1850279601 | 154472964 | begin_outline_data +| UNACC | t | 166| | TableScan(@"sel$1" csdn.tb_a@"sel$1") +| | || | version("1.0.0") +| | || | end_outline_data | | |3237163112 | 2994191517 | begin_outline_data +| ACC | t | 48.451| | BitmapScan(@"sel$1" csdn.tb_a@"sel$1" tb_a_idx_c1)+| | || | version("1.0.0") +| | || | end_outline_data | | |
(2 rows)csdn=>
步驟6：計劃演證
csdn=> SET enable_seqscan=on;
SET
csdn=> SET enable_indexscan=on;
SET
csdn=> SET plan_cache_mode = 'force_generic_plan';
SET
csdn=> SET explain_perf_mode=pretty;
SET
csdn=> DEALLOCATE spm_query;
DEALLOCATE
csdn=> PREPARE spm_query AS SELECT * FROM tb_a WHERE c1 = $1;
PREPARE
csdn=> EXPLAIN(costs off) EXECUTE spm_query (1);id | operation
----+--------------------------------------------1 | -> Bitmap Heap Scan on tb_a2 | -> Bitmap Index Scan using tb_a_idx_c1
(2 rows)Predicate Information (identified by plan id)
-----------------------------------------------1 --Bitmap Heap Scan on tb_aRecheck Cond: (c1 = $1)2 --Bitmap Index Scan using tb_a_idx_c1Index Cond: (c1 = $1)
(4 rows)
4 批注
SPM主要是為要防止因為業務數據變化或版本升級引起的SQL計劃跳變而影響業務性能。
)
