经典案例：如何优化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下⾯的那层)。
⽣成带统计信息的执⾏计划，如下图所⽰：
⾏这么多次，这个太消耗资源⽽且还是在远程库的表上。
接下来，在执⾏计划后⾯的”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增加
数据库优化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代码。（若⽆需更改代码的优化，那就再好不过了）相关⽂献参考：
特别鸣谢(排名不分先后)：
Jonathan Lewis,
Andrew Sayer,
Billy~Verreynne,
BEDE,
Manik,
perfdba,
Paulzip,
Mustafa KALAYCI,
Cookiemonster76,
Sven W.,
padders

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。