【数据库使用】 Plan management绑定计划用例

问题背景

select current_database(), n.nspname,c.relname,0 from pg_class c , pg_namespace n where n.oid = c.relnamespace and (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND NOT pg_is_other_temp_schema(n.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) and n.nspname = 'public' order by c.relname limit 20 offset 0;

语句执行慢，管理员用户很快，普通用户执行慢

发现是使用系统视图时，做了很多用or连接的权限判断：pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)

由于dabadmin用户pg_has_role总能返回true，因此or之后的条件无需继续判断；而普通用户的or条件需要逐一判断，如果数据库中表个数比较多，最终会导致普通用户比dbadmin需要更长的执行时间。

加hint， 走index + nestloop的优化后，时间从156秒优化到24秒。

/*+ nestloop(n c) leading((n c)) set global(enable_seqscan  off)*/

但是客户代码已上线，无法修改，无法加hint，使用管理员用户又不符合安全要求。

可通过配置，使用 Plan management功能进行计划绑定。即根据sql_hash，使语句和outline进行绑定。

版本要求：910及以上

Plan management特性基本原理：

在CN上，对每个sql生成的计划（除FQS计划或CN轻量化）进行遍历，将计划中的join算子、scan（table scan和index scan）算子、join和agg上的倾斜优化信息、join两端的stream算子以及表的关联顺序提取为outline（即一组hint），并将outline进行保存（dbms_om.sql_outline）。用户可通过topsql分析出哪个计划是优的，并将比较优的计划的outline绑定给该sql。绑定后，该sql再次生成计划时，会通过应用该hint来固定执行计划。

除FQS和CN强量化计划外，其他计划都会生成outline。

方法步骤：

1.设置开启plan management

需要后台开启集群参数，无需重启
SET planmgmt_options='plan_save_mode_outline,enable_plan_baseline,plan_save_level_topsql';
SET enable_planmgmt_backend=on;

planmgmt_options中的plan_save_level_topsql可在配置完成功后去掉，避免sql_outline表过大，开启plan_save_level_topsql后，满足记录topsql条件的语句，都会往dbms_om.sql_outline表中记录相应的outline。

2.获取要绑定outline的sql_hash
可从topsql或执行计划中获取'sql_hash'

3.语句调优

获取需要绑定的OUTLINE，根据实际情况进行调优

加hint调优：/*+ nestloop(n c) leading((n c)) set global(enable_seqscan  off)*/
explain (verbose on, blockname on, outline on) select /*+ nestloop(n c) leading((n c)) set global(enable_seqscan  off)*/ current_database(), n.nspname,c.relname,0 from pg_class c , pg_namespace n where n.oid = c.relnamespace and (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND NOT pg_is_other_temp_schema(n.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) and n.nspname = 'public' order by c.relname limit 20 offset 0;

调优时，如果计划内容不够详细，可以set explain_perf_mode=normal;后打计划，可以看到更详细的计划，该参数默认为pretty。

4.获取OUTLINE

从dbms_om.sql_outline系统表获取OUTLINE，也可手动创建OUTLINE

查系统表：用优化后的sql_hash获取outline_name，

SELECT sql_hash, plan_hash, outline_name, outline FROM dbms_om.sql_outline where sql_hash like '%xxx%';

当系统表中记录的outline不满足诉求时，也可手动创建：

OUTLINE名称要以“outline_”开头，sql_hash需要与被绑定的语句保持一致，USING 后为OUTLINE的具体内容。

CREATE OUTLINE outline_name_1 FOR sql_7cd3bd7d2be865a0e315e3fdf29e2c0e USING '/*+
       begin_outline_data
        Leading[@"sel$1" n@"sel$1" c@"sel$1"]
        NestLoop(@"sel$1" n@"sel$1" c@"sel$1")
        IndexScan(@"sel$1" n@"sel$1" pg_namespace_nspname_index)
        IndexScan(@"sel$1" c@"sel$1" pg_class_relname_nsp_index)
       end_outline_data
   */
';

5.绑定outline

使用sql_hash，outline_name进行绑定
SELECT pgxc_bind_plan('sql_hash', 'outline_name_1');

6.验证

7.解绑

使用sql_hash解绑

SELECT pgxc_unbind_plan('sql_7cd3bd7d2be865a0e315e3fdf29e2c0e');

注：绑定OUTLINE后，手工写的hint有些会不生效，在进行hint调优时，建议解绑后再hint。