MogDB/openGauss学习笔记之 – SQL Patch
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: MogDB/openGauss学习笔记之 – SQL Patch
MogDB 继承了openGauss 3.0引入等SQL Patch功能，并进行了一些增强。我们都知道在数据库优化过程中，尤其是之前在进行Oracle 数据库优化时，需要经常去干预SQL 执行计划，确保应用SQL 走正常的执行计划。因此去固定sql的执行计划就显得十分的重要，这方面Oracle做的非常强大。不过现在MogDB也具备相应的功能了，这简直是DBA的一大福音。下面进行简单的 测试验证：
首先准备好测试表：
|
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 |
+++Session 1 enmotech=> create table test_sql_patch as select * from pg_settings; INSERT 0 773 enmotech=> enmotech=> insert into test_sql_patch select * from test_sql_patch; INSERT 0 773 enmotech=> insert into test_sql_patch select * from test_sql_patch; INSERT 0 1546 enmotech=> insert into test_sql_patch select * from test_sql_patch; INSERT 0 3092 enmotech=> insert into test_sql_patch select * from test_sql_patch; INSERT 0 6184 enmotech=> insert into test_sql_patch select * from test_sql_patch; INSERT 0 12368 enmotech=> insert into test_sql_patch select * from test_sql_patch; INSERT 0 24736 enmotech=> insert into test_sql_patch select * from test_sql_patch; INSERT 0 49472 enmotech=> insert into test_sql_patch select * from test_sql_patch; INSERT 0 98944 。。。。。。 enmotech=> create index idx_test_sql_patch on test_sql_patch(name,setting); CREATE INDEX enmotech=> enmotech=> \timing on Timing is on. enmotech=> select count(1) from test_sql_patch; count -------- 395776 (1 row) Time: 156.625 ms enmotech=> select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows'; count ------- 512 (1 row) Time: 1.791 ms enmotech=> explain performance select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=30.33..30.34 rows=1 width=8) (actual time=0.838..0.839 rows=1 loops=1) Output: count(1) (Buffers: shared hit=7) (CPU: ex c/r=-20846719600616188, ex row=512, ex cyc=-10673520435515488256, inc cyc=41775031060283808) -> Index Only Scan using idx_test_sql_patch on public.test_sql_patch (cost=0.00..29.07 rows=504 width=0) (actual time=0.084..0.602 rows=512 loops=1) Output: name, setting Index Cond: (test_sql_patch.name = 'adaptive_hashagg_min_rows'::text) Heap Fetches: 0 (Buffers: shared hit=7) (CPU: ex c/r=20928311458155804, ex row=512, ex cyc=10715295466575771648, inc cyc=10715295466575771648) Total runtime: 1.093 ms (11 rows) Time: 4.316 ms enmotech=> |
这里我们单独一个窗口来执行创建sql patch的操作：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+++Session 2 enmotech=# select unique_sql_id,query from dbe_perf.statement where query like '%test_sql_patch%' unique_sql_id | query ---------------+---------------------------------------------------------------- 4280879837 | select count(?) from test_sql_patch where name=?; enmotech=# select * from dbe_sql_util.create_hint_sql_patch('patch0619', 4280879837, 'indexonlyscan(test_sql_patch)'); create_hint_sql_patch ----------------------- t (1 row) |
创建完毕后，我们来简单验证一下：
|
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 |
++++Session 1 enmotech=> explain select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows' and setting='10000'; QUERY PLAN ---------------------------------------------------------------------------------------------------- Aggregate (cost=4.34..4.35 rows=1 width=8) -> Index Only Scan using idx_test_sql_patch on test_sql_patch (cost=0.00..4.33 rows=4 width=0) Index Cond: ((name = 'adaptive_hashagg_min_rows'::text) AND (setting = '10000'::text)) (3 rows) Time: 1.123 ms enmotech=> enmotech=# select * from dbe_sql_util.drop_sql_patch('patch0619'); drop_sql_patch ---------------- t (1 row) enmotech=> explain select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows'; NOTICE: Plan influenced by SQL hint patch QUERY PLAN ------------------------------------------------------------------------------------------------------- Aggregate (cost=30.33..30.34 rows=1 width=8) -> Index Only Scan using idx_test_sql_patch on test_sql_patch (cost=0.00..29.07 rows=504 width=0) Index Cond: (name = 'adaptive_hashagg_min_rows'::text) (3 rows) Time: 1.111 ms |
如果是indexonlyscan的情况下，能否改成indexscan呢，我们删除sql patch并重建一下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
enmotech=# select * from dbe_sql_util.drop_sql_patch('patch0619'); drop_sql_patch ---------------- t (1 row) enmotech=# select * from dbe_sql_util.create_hint_sql_patch('patch0619', 4280879837, 'indexscan(test_sql_patch)'); create_hint_sql_patch ----------------------- t (1 row) enmotech=# |
再次验证一下执行计划：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
enmotech=> explain select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows'; NOTICE: Plan influenced by SQL hint patch WARNING: unused hint: IndexScan(test_sql_patch) QUERY PLAN ------------------------------------------------------------------------------------------------------- Aggregate (cost=30.37..30.38 rows=1 width=8) -> Index Only Scan using idx_test_sql_patch on test_sql_patch (cost=0.00..29.11 rows=506 width=0) Index Cond: (name = 'adaptive_hashagg_min_rows'::text) (3 rows) Time: 2.394 ms enmotech=> |
我们可以看有warning提示，说明这种情况下并没起作用，仍然使用了indexonlyscan.
为了查询慢sql监控信息，这里我调整了如下的相关参数：
enable_resource_track=on
instr_unique_sql_count=10000
resource_track_level=query
log_duration=on
log_min_duration_statement=1
除了简单的 控制索引使用，能否支持控制连接方式呢？比如强制使用hash join？ 再测一下：
|
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 86 87 88 89 90 91 92 93 94 95 96 |
enmotech=> create table test0619 as select * from test_sql_patch; INSERT 0 395776 Time: 2246.460 ms enmotech=> explain select a.name,count(1) from test_sql_patch a,test0619 b enmotech-> where a.name=b.name enmotech-> and b.setting=500000 enmotech-> group by a.name enmotech-> order by 2; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Sort (cost=32121.62..32123.55 rows=773 width=28) Sort Key: (count(1)) -> HashAggregate (cost=32076.81..32084.54 rows=773 width=28) Group By Key: a.name -> Nested Loop (cost=0.00..30197.77 rows=375808 width=20) -> Seq Scan on test0619 b (cost=0.00..11440.03 rows=734 width=32) Filter: ((setting)::bigint = 500000) -> Index Only Scan using idx_test_sql_patch on test_sql_patch a (cost=0.00..20.44 rows=512 width=20) Index Cond: (name = b.name) (9 rows) Time: 2.791 ms enmotech=> enmotech=> select a.name,count(1) from test_sql_patch a,test0619 b enmotech-> where a.name=b.name enmotech-> and b.setting=500000 enmotech-> group by a.name enmotech-> order by 2; ERROR: invalid input syntax for type bigint: "off" Time: 3.981 ms enmotech=> select a.name,count(1) from test_sql_patch a,test0619 b enmotech-> where a.name=b.name enmotech-> and b.setting='500000' enmotech-> group by a.name enmotech-> order by 2; name | count ------------------------------+-------- acce_min_datasize_per_thread | 262144 (1 row) Time: 400.892 ms enmotech=> enmotech=# select unique_sql_id,query from dbe_perf.statement where query like '%test0619%'; unique_sql_id | query ---------------+-------------------------------------------------------- 1699566349 | create table test0619 as select * from test_sql_patch; (1 row) enmotech=# select unique_sql_id,query from dbe_perf.statement where query like '%test0619%'; unique_sql_id | query ---------------+--------------------------------------------------------- 1699566349 | create table test0619 as select * from test_sql_patch; 3134227090 | select a.name,count(?) from test_sql_patch a,test0619 b+ | where a.name=b.name + | and b.setting=? + | group by a.name + | order by 2; 3627100211 | select a.name,count(?) from test_sql_patch a,test0619 b+ | where a.name=b.name + | and b.setting=? + | group by a.name + | order by 2; (3 rows) enmotech=# select * from dbe_sql_util.create_hint_sql_patch('patch0619_2', 3627100211, 'hashjoin(test0619 test_sql_patch)'); create_hint_sql_patch ----------------------- t (1 row) enmotech=> explain select a.name,count(1) from test_sql_patch a,test0619 b enmotech-> where a.name=b.name enmotech-> and b.setting='500000' enmotech-> group by a.name enmotech-> order by 2; NOTICE: Plan influenced by SQL hint patch WARNING: Error hint: HashJoin(test0619 test_sql_patch), relation name "test0619" is not found. QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Sort (cost=34548.58..34550.51 rows=773 width=28) Sort Key: (count(1)) -> HashAggregate (cost=34503.77..34511.50 rows=773 width=28) Group By Key: a.name -> Nested Loop (cost=0.00..32660.01 rows=368752 width=20) -> Seq Scan on test0619 b (cost=0.00..14185.20 rows=721 width=20) Filter: (setting = '500000'::text) -> Index Only Scan using idx_test_sql_patch on test_sql_patch a (cost=0.00..20.50 rows=512 width=20) Index Cond: (name = b.name) (9 rows) Time: 2.088 ms enmotech=> |
从测试来看似乎没有识别到我们的hint。可能是写法不对，改成别名试试看。
|
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 |
enmotech=# select * from dbe_sql_util.drop_sql_patch('patch0619_2'); drop_sql_patch ---------------- t (1 row) enmotech=# select * from dbe_sql_util.create_hint_sql_patch('patch0619_1',3627100211, 'hashjoin(a b)'); create_hint_sql_patch ----------------------- t (1 row) enmotech=# enmotech=> explain select a.name,count(1) from test_sql_patch a,test0619 b enmotech-> where a.name=b.name enmotech-> and b.setting='500000' enmotech-> group by a.name enmotech-> order by 2; NOTICE: Plan influenced by SQL hint patch QUERY PLAN -------------------------------------------------------------------------------------------- Sort (cost=36665.86..36667.79 rows=773 width=28) Sort Key: (count(1)) -> HashAggregate (cost=36621.05..36628.78 rows=773 width=28) Group By Key: a.name -> Hash Join (cost=14194.21..34777.29 rows=368752 width=20) Hash Cond: (a.name = b.name) -> Seq Scan on test_sql_patch a (cost=0.00..13195.76 rows=395776 width=20) -> Hash (cost=14185.20..14185.20 rows=721 width=20) -> Seq Scan on test0619 b (cost=0.00..14185.20 rows=721 width=20) Filter: (setting = '500000'::text) (10 rows) Time: 1.957 ms enmotech=> |
此时正是我们需要的执行计划，强制其走hash join。
从官方文档来看，目前MogDB SQL Patch支持多种操作；例如 支持行数、扫描方式、连接方式、连接顺序、PBE custom/generic计划选择、语句级参数等。
基本上跟Oracle SQL patch接近了。
Leave a Reply
You must be logged in to post a comment.