开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL Oracle兼容性之 - 锁定执行计划(Outline system)

简介:
+关注继续查看

背景

绑定SQL执行计划,大家一定会想到SQL HINT,通过HINT告诉优化器你要用什么访问方法,用什么JOIN方法,JOIN的顺序,驱动表等等。

但是SQL HINT有一定的弊端,它需要修改应用程序中的SQL语句,把SQL加上HINT。

对pg_hint_plan感兴趣的同学,可以参考我写到文档
https://yq.aliyun.com/articles/57945

但是,有什么方法可以在不修改应用程序,不修改SQL的情况下,锁定SQL的执行计划呢?

锁定执行计划

要锁定执行计划,同样要用到PostgreSQL的 HOOK,在生成plan时动手。

源码
https://github.com/postgrespro/sr_plan

原理如下图
parser的hook,用于获取query text。
planner的hook,干了两间事情,
.1. 在开启了writer模式时,判断表中是否有该sql的执行计划,没有的话就将planner记录在表中。
.2. 从表中读取到对应的执行计划,传给executor,从而达到锁定执行计划的目的。
screenshot

用法介绍

安装略

在需要锁定执行计划的数据库中创建扩展

CREATE EXTENSION sr_plan;

生成正确的执行计划
连接到对应数据库,并打开一个会话

使用一些开关,使得接下来要记录的执行计划  
set enable_bitmapscan=off;
......

打开记录模式  
set sr_plan.write_mode = true;

执行需要生成执行计划的QUERY,例如  
select a,b,c from tbl where id=1 and c1=2;  

检查执行计划是否正确  
select * from sr_plans;

如果发现执行计划不正确,删除即可,重新生成。    
delett from sr_plans where query_hash=...;

记得关闭记录模式  
set sr_plan.write_mode = false;

如果要生成下一条QUERY的执行计划,继续前面的步骤即可。  

让优化器使用已生成的执行计划
生成后的执行计划,并不会马上生效,需要更新sr_plans.enable字段

select query_hash, enable, valid, query, explain_jsonb_plan(plan) from sr_plans;

 query_hash | enable | valid |                        query                         |                 explain_jsonb_plan                 
------------+--------+-------+------------------------------------------------------+----------------------------------------------------
 1783086253 | f      | t     | select query_hash from sr_plans where query_hash=10; | Bitmap Heap Scan on sr_plans                      +
            |        |       |                                                      |   Recheck Cond: (query_hash = 10)                 +
            |        |       |                                                      |   ->  Bitmap Index Scan on sr_plans_query_hash_idx+
            |        |       |                                                      |         Index Cond: (query_hash = 10)             +
            |        |       |                                                      | 

explain_jsonb_plan function allows you to display explain execute the plan of which lies in jsonb. 
By default, all the plans are off, you need enable it:

update sr_plans set enable=true where query_hash=1783086253;

(1783086253 for example only) After that, the plan for the query will be taken from the sr_plans.

对于使用参数的QUERY,同样适用。

In addition sr plan allows you to save a parameterized query plan. 
In this case, we have some constants in the query are not essential. 
For the parameters we use a special function _p (anyelement) example:

select query_hash from sr_plans where query_hash=1000+_p(10);

if we keep the plan for the query and enable it to be used also for the following queries:

select query_hash from sr_plans where query_hash=1000+_p(11);
select query_hash from sr_plans where query_hash=1000+_p(-5);

源码

.1. 获取query text
src/backend/optimizer/plan/planner.c

/*****************************************************************************
 *
 *         Query optimizer entry point
 *
 * To support loadable plugins that monitor or modify planner behavior,
 * we provide a hook variable that lets a plugin get control before and
 * after the standard planning process.  The plugin would normally call
 * standard_planner().
 *
 * Note to plugin authors: standard_planner() scribbles on its Query input,
 * so you'd better copy that data structure if you want to plan more than once.
 *
 *****************************************************************************/
PlannedStmt *
planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
{
        PlannedStmt *result;

        if (planner_hook)
                result = (*planner_hook) (parse, cursorOptions, boundParams);
        else
                result = standard_planner(parse, cursorOptions, boundParams);
        return result;
}

.2.
src/backend/parser/analyze.c

/*
 * parse_analyze
 *              Analyze a raw parse tree and transform it to Query form.
 *
 * Optionally, information about $n parameter types can be supplied.
 * References to $n indexes not defined by paramTypes[] are disallowed.
 *
 * The result is a Query node.  Optimizable statements require considerable
 * transformation, while utility-type statements are simply hung off
 * a dummy CMD_UTILITY Query node.
 */
Query *
parse_analyze(Node *parseTree, const char *sourceText,
                          Oid *paramTypes, int numParams)
{
        ParseState *pstate = make_parsestate(NULL);
        Query      *query;

        Assert(sourceText != NULL); /* required as of 8.4 */

        pstate->p_sourcetext = sourceText;

        if (numParams > 0)
                parse_fixed_parameters(pstate, paramTypes, numParams);

        query = transformTopLevelStmt(pstate, parseTree);

        if (post_parse_analyze_hook)
                (*post_parse_analyze_hook) (pstate, query);

        free_parsestate(pstate);

        return query;
}

/*
 * parse_analyze_varparams
 *
 * This variant is used when it's okay to deduce information about $n
 * symbol datatypes from context.  The passed-in paramTypes[] array can
 * be modified or enlarged (via repalloc).
 */
Query *
parse_analyze_varparams(Node *parseTree, const char *sourceText,
                                                Oid **paramTypes, int *numParams)
{
        ParseState *pstate = make_parsestate(NULL);
        Query      *query;

        Assert(sourceText != NULL); /* required as of 8.4 */

        pstate->p_sourcetext = sourceText;

        parse_variable_parameters(pstate, paramTypes, numParams);

        query = transformTopLevelStmt(pstate, parseTree);

        /* make sure all is well with parameter types */
        check_variable_parameters(pstate, query);

        if (post_parse_analyze_hook)
                (*post_parse_analyze_hook) (pstate, query);

        free_parsestate(pstate);

        return query;
}

小结

  • 对相应的表执行DDL之后,记得清理在sr_plans中对应表的执行计划。

祝大家玩得开心,欢迎随时来 阿里云促膝长谈 业务需求 ,恭候光临。

阿里云的小伙伴们加油,努力做 最贴地气的云数据库

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
RDS MySQL 5.7三节点企业版重磅发布 企业级业务云上数据库首选
随着云计算技术的逐渐普及,使用云服务的客户行业、场景的边界也在不断地被拓宽,不断提出新的需求。
343 0
RDS MySQL 5.7三节点企业版重磅发布 企业级业务云上数据库首选
10月23日15:00 多名专家联袂讲解 https://yq.aliyun.com/live/1536 随着云计算技术的逐渐普及,使用云服务的客户行业、场景的边界也在不断地被拓宽,不断提出新的需求。
3385 0
RDS数据库与自建库的gtid主从同步
一、在centos7上部署MySQL数据库1、从MYSQL官网下载安装文件wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.
1928 0
阿里云云数据库RDS如何监控、备份及克隆实例?
监控报警及备份恢复是DBA日常工作里面用的最多的功能。这里我们来对这个功能进行一个简单的介绍。 点击监控与报警。监控类型包括资源监控及引擎监控。资源监控,包括CPU和内存利用率、磁盘空间、IOPS链接数和网络流量;引擎监控,主要针对数据库引擎内部的深度监控,由mysql里面提供TPS、QPS、命中率、读写量、缓存请求次数、日志读写以及更多的深入监控信息。
1977 0
RDS for MySQL8.0物理备份恢复到本地自建数据库
此文章是centos7下的恢复流程。 1、安装MySQL8.0(采用yum方式安装):wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm yum localinstall mysql80-community-release-el7-1.noarch.rpm yum -y install yum-utils 默认安装的就是8.0版本yum install mysql-community-server 安装好了不要启动数据库。
3123 0
【阿里云新品发布·周刊】第11期:云数据库 MySQL 8.0 重磅发布,更适合企业使用场景的RDS数据库
2019年5月29日15时,阿里云云数据库 MySQL 8.0 重磅发布,2倍以上性能提升,SQL窗口函数、JSON扩展语法等企业级新功能震撼上市!主要从技术层面介绍MySQL 8.0的优势和与过去版本对比。
3304 0
小微企业阿里云最佳实践系列(二):RDS 数据库与DMS 数据库管理(数据管理)
在上一篇博文中主要介绍了 ECS 服务器与 RDS 数据库,在本篇重点为大家讲解我们的数据如何进行管理、在日常的管理过程中存在哪些风险、遇到突然事件如何通过日志分析问题以及解决问题。
2886 0
小微企业阿里云最佳实践系列(一):ECS 服务器与 RDS 数据库
本博文主要使用传统服务器架构与云服务架构进行横向对比,解决企业在搭建软件系统中所遇到等问题和痛点,以及为小微企业降低成本的同时尽可能提高软件系统的高可通、低延迟、高规范、低人力投入。
2982 0
自建Percona5.7.23同步阿里云RDS(MySQL5.6)TokuDB数据库
阿里云RDS支持TokuDB引擎,具有高压缩,高写入性能,读性能和InnoDB差不多。本文详解Percona MySQL5.7.23(包含TokuDB引擎)二进制的搭建,以及使用RDS备份文件恢复数据和搭建GTID同步到自建Percona MySQL5.7.23的过程。
2333 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
Oracle 至PostgreSQL案例分享
立即下载
ORACLE 10g 数据库体系结构图
立即下载
PostgresChina2018_刘成伟_oracle到Postgres数据库迁移工具
立即下载