经典案例:如何优化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 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

Eclipse被卡死了或者失去响应了后分析根源的一个小技巧

大家在使用Eclipse的过程中可能会偶尔遇到该IDE失去响应或者一直停留在某个步骤迟迟结束不了的情况。没有经验的朋友可能会用任务管理器强制结束Eclipse进...

814
来自专栏北京马哥教育

DNS从入门到管理(一)

DNS概述 DNS(Domain Name System,域名系统),域名和IP地址相互映射的一个分布式数据库,通过主机名,最终得到该主机名对应的IP地址的过程...

4586
来自专栏CLEAN_CODER

Ruby on Rails 初次冲浪体验

Rails是一个用Ruby编写的Web应用开发框架。它的设计目标是通过预先提供开发人员最开始需要的基础设施,从而让Web应用开发更加容易。它可以让你写更少的代码...

1224
来自专栏程序员宝库

webpack 4 升级指北

2018年2月25日,刚过完年webpack就给了一个加班红包。webpack4经过1个月的缓冲期,终于发布了正式版,那么抛给广大开发者的问题又来了,我是不是要...

6637
来自专栏北京马哥教育

JMeter使用技巧

在这此对新版本jmeter的学习+温习的过程,发现了一些以前不知道的功能,所以,整理出来与大分享。本文内容如下。 如何使用英文界面的jmeter 如何使用镜像...

4559
来自专栏漏斗社区

ZentaoPMS任意文件上传漏洞复现

当斗哥一筹莫展,无从下笔时, 一位从事项目管理职业的小姐姐一语道破天机。 她使用的禅道管理软件引起了我的注意, 为了拉进与小姐姐的直线距离, 斗哥对禅道进行了全...

3646
来自专栏信安之路

史上最强内网渗透知识点总结

文章内容没谈 snmp 利用,可以去乌云等社区获取,没有后续内网持久化,日志处理等内容。

7280
来自专栏编程

vs code上配置python的运行环境

vs code上配置python的运行环境 Thanks for reading this. 上个月的时候花了不少功夫在VS code上配置Python的运行环...

3378
来自专栏coding...

Jenkins + fastlane iOS 双重认证 自动更新配置文件配置Appfile使用sigh最后

这个文件大家应该都知道,无非就是设置一下 name、id、team 做好预设就就不用选了,方便跑自动化

712
来自专栏数据和云

Oracle 12c: arraysize会影响结果集么?

SQL*Plus中ArraySize的设置会影响结果集的数量么?先不要轻易说“NO”,我们来看看Jonathan Lewis最近一篇文章中披露的一个案例。 当确...

2806

扫码关注云+社区