经典案例:如何优化Oracle使用DBlink的SQL语句

作者介绍

赵全文

就职于太极计算机股份有限公司,在中央电化教育馆做Oracle DBA的驻场运维工作。具有3年左右的Oracle工作经验,目前擅长Oracle数据库的SQL脚本编写、故障诊断和性能优化,并且乐于分享Oracle技术。

‍‍‍‍‍‍‍‍‍‍‍‍‍‍一般在DBLINK的SQL语句中,将调用远程表的in-line view结果集返回的数据尽量减少,进而达到通过网络传输的数据减少的目的,而且也不会将数据传输的资源消耗在大量的网络等待事件上。在Oracle中这样的等待事件是:SQL*Net message from DBLINK

正巧,前段时间我们的Oracle生产库正好也碰到了这样的几条类似的SQL。所以,今天向大家分享一下,一次针对Oracle中使用DBLINK的SQL语句的优化思路分析过程。

发现问题

首先从EMCC监控上,发现一条SQL语句执行好长时间没有执行完毕。

问题解析(一)

根据其SQL_ID的值抓取出的SQL完整语句如下所示:

发现上面的SQL语句里有一个绑定变量":1"。

通过视图v$sql_bind_capture(或者dba_hist_sqlbind)进行查看具体的值。

或者也可以用SQLT(全称SQLTXPLAIN,关于SQLT的下载、安装和使用,请看Oracle MOS 215187.1)生成分析SQL_ID为83gn36c1fu9dw的报告,从报告中找出绑定变量”:1”具体的值(可能有很多),由于我的数据库服务器上已经部署了SQLT,生成报告的过程这里略过,查看绑定变量值的过程如下图所示。

然后,将上面查出的值”ff80808141c605e20141c9691f5a000c”带入原始的SQL语句并在SQL*Plus里执行,运行5分26秒才显示查询结果。可想而知,在当前的高并发情况下,这样的一条SQL语句花很长时间执行不完也就不足为奇了,整个过程如下图所示。

分析整个SQL语句的结构

其中最外层的SELECT是一个ROWNUM操作,也就是取内层结果集并返回前5行; 再往里的一层完全可以去掉,(这个我经过测试是可行的); 再往里看的一层就是内联视图r (查询远程表sd_res_id_case返回的结果集)与本地表t进行左联接; 最终返回整个查询结果。

大家仔细看一下那个内联视图r,你会发现里面还有一个子查询(就是and rowid in下面的那层)。

生成带统计信息的执行计划,如下图所示:

看第3步的NESTED LOOPS,Starts*E-Rows=1*2=2,而A-Rows=926K,我们说Starts*E-Rows的值和A-Rows的值应该相等或者相差不多,再看第8行的REMOTE,Starts*E-Rows=926K*3,A-Rows=5,这两个值也相差很大。而且这个REMOTE的Starts是926K,这说明要执行这么多次,这个太消耗资源而且还是在远程库的表上。

接下来,在执行计划后面的”Remote SQL Information”中可以看出有两个REMOTE操作,也就是说这条SQL语句的内联视图r并不是整体从远程表上查询出结果再返回到本地库,而是先执行第5步,再执行第8步,总共查询了两次远程表。

那么试想一下看能不能让远程表只查询一次,也就是让内联视图r只执行一次就返回远程表sd_res_id_case的查询结果?

结果当然是可以的,用一个no_merge的Hint放在内联视图r的第一个select 之后,更改之后是下面这样的:

竟然发现大约7秒就查询出结果,如下图所示,

接着,查看附加统计信息的执行计划。

最主要的是,执行时间大大降低,而且在执行计划里只有一个REMOTE操作,第二步变成了HASH JOIN操作(原先的执行计划是NESTED LOOPS),估计行和返回行都是5。

接下来再看第5行的VIEW操作,执行1次,估行行为5754,实际行为66165,这个相差10倍左右,估计还有优化的空间。

远程库上查看内联视图r的数据量

由于远程表的执行计划在本地库上无法查看,那么我们到远程库上查询一下原SQL语句的内联视图r,看看到底有多少数据。

在远程库上做如下操作。

竟然返回196372(约196K)行,这个值高的超乎我想象。

查看带统计信息的执行计划,如下图所示,

第2行的”NESTED LOOPS”操作实际返回行196K,也就是SQL语句中的最外层select count(*)操作; 第7行的”TABLE ACCESS BY USER ROWID”操作也是实际返回行196K(仔细看,Starts的值为196K,也就是执行196K次,这个好恐怖),第7行的操作就是子查询”select min(rowid) from ……”。

这样看来SQL语句的外层select有多少行,里面的子查询就执行多少次,而现在的外层select是196K行,然后呢,196K*196K = ?我都不敢想……

总体上看,加一个no_merge的Hint,先是让SQL的执行时间与原先相比降低了好多。

于是,我和开发同事进行沟通,我才明白SQL是应用服务器里跑的一个定时任务,每天凌晨4点开始执行,最后他给程序里的SQL增加no_merge的Hint。

问题解析(二)

第二天,我用视图v$active_session_history查看凌晨4点到6点的DBLINK等待事件。

从上面的查询,我们可以看出,有两条SQL的DBLINK等待事件总数多的离谱。其实另外一条SQL和我前面分析的那条唯一的区别就是在select最外层又加了一个ROWNUM <= ":2" 的条件,目前我们只分析原先的那条。

那么,再查询6点到7点的情况,已经没有DBLINK的等待事件,说明那些相关的SQL执行完毕,如下图所示。

另外,我们从AWR的对比报告中也可以看出上面的查询结果(AWR是从视图DBA_HIST_ACTIVE_SESS_HISTORY中读取相关信息)。

从上面的AWR图中我们还可以看出那两条SQL的执行次数分别为3106和3039。

从前面的执行计划分析,我们了解到SQL主要慢在内联视图r的返回行很多,那么继续优化就是要改写内联视图。

首先,将内联视图r的外层select查询中增加和内层select查询中同样的where条件,这样就能过滤掉许多行,同时将两层select查询中的school_id字段进行关联,如下图所示。

然而只需4毫秒就显示查询结果,带统计信息的执行计划如下图所示,

接下来,我和开发同事进行了沟通并把我改写后的SQL发给他,他测试运行和原先SQL相比,也认为在运行时间上差了一个数量级。后来,他根据业务的需求改写了原来的SQL,整个改写后的SQL语句如下图所示。

查看带统计信息的执行计划,如下图所示。

通过上面的执行计划,大家可以看出Starts、E-Rows、A-Rows的值都变得很小了,A-Time的值为1~2毫秒。

第三天,再次查看相应时间段的DBLINK等待事件总数,发现与原来相比已经降低了很多。

再查看SQL_ID为a50rh3659p44q的SQL在相应对间段的执行次数,见下图。

同样的,从下面折AWR报告中也能看出和上面的查询一样的效果。

总结

最后对使用DBLINK的SQL优化过程总结:

(1) 从EMCC监控上抓取有问题的SQL;

(2) 通过给SQL增加gather_plan_statistics的Hint通过实际运行测试;

(3) 生成相应的行源执行计划并分析哪一步操作最消耗时间;

(4) 找出对应的方法(并不一定是改写,这个根据具体情况而定),再次进行测试;

(5) 与开发人员沟通,并重新审核修改SQL代码。(若无需更改代码的优化,那就再好不过了)

相关文献参考:

https://community.oracle.com/thread/4083373

https://community.oracle.com/thread/4100882

特别鸣谢(排名不分先后):

Jonathan Lewis, Andrew Sayer, Billy~Verreynne, BEDE, Manik, perfdba, Paulzip, Mustafa KALAYCI, Cookiemonster76, Sven W., padders

等国外Oracle的技术专家们,是他们在Oracle Developer Community(https://community.oracle.com/welcome)上针对我的发贴提问给予了细心的指导。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-12-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java学习网

MySQL大表优化方案

  当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化:   单表优化   除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分...

95860
来自专栏更流畅、简洁的软件开发方式

利用虚拟硬盘(把内存当作硬盘)来提高数据库的效率(目前只针对SQL Server 2000)可以提高很多

      虚拟硬盘:就是把内存当作硬盘来用,比如有2G的内存,那么可以拿出来1G的内存当作硬盘来用。       自从知道了“虚拟硬盘”这个东东,我就一直在想...

53250
来自专栏腾讯Bugly的专栏

移动客户端中高效使用 SQLite

导语 iOS 程序能从网络获取数据。少量的 KV 类型数据可以直接写文件保存在 Disk 上,App 内部通过读写接口获取数据。稍微复杂一点的数据类型,也可以将...

64860
来自专栏PHP在线

PHP开发人员常犯的10个MysqL错误

对于大多数web应用来说,数据库都是一个十分基础性的部分。如果你在使用PHP,那么你很可能也在使用MySQL—LAMP系列中举足轻重的一员。 对于很多新手们来说...

31340
来自专栏更流畅、简洁的软件开发方式

用node.js实现ORM的一种思路

  ORM是O和R的映射。O代表面向对象,R代表关系型数据库。二者有相似之处同时也各有特色。就是因为这种即是又非的情况,才需要做映射的。   理想情况是,根据关...

27290
来自专栏happyJared

Python爬取微信公众号文章和评论 (基于Fiddler抓包分析)

  感觉微信公众号算得是比较难爬的平台之一,不过一番折腾之后还是小有收获的。没有用Scrapy(估计爬太快也有反爬限制),但后面会开始整理写一些实战出来。简单介...

1.3K20
来自专栏Java架构沉思录

MySQL 性能优化,优化设计及设计原则解读

1. 关系明确(理清表之间的关系,可以通过冗余的方式提高效率) 2. 节省空间(根据业务经验,设置字段长短) 3. 提高效率

8520
来自专栏源码之家

批量删除WP的垃圾评论

19440
来自专栏大数据和云计算技术

数据库中的元数据

刘耀铭同学元数据系列作品的第三篇,大家支持! 今天跟大家谈谈数据库中的元数据 数据库中的元数据无非就是对数据库中数据的描述与定义。 ? 我们先举个现实生活中...

31360
来自专栏吴伟祥

mysql 自增id和UUID做主键性能分析,及最优方案

UUID 是 通用唯一识别码(Universally Unique Identifier)的缩写,是一种软件建构的标准,亦为开放软件基金会组织在分布式计算环境领...

60420

扫码关注云+社区

领取腾讯云代金券