0

SQL之美 - Oracle 子查询优化系列精讲

黄廷忠 2017-06-20
177

题记:SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基。


本系列经典文章

之一:标量子查询优化

之二:OR展开与子查询优化案例详解。

今天是系列第三讲:IN子查询返回结果集异常


作者简介:


黄廷忠(网名:认真就输)

云和恩墨技术专家

个人博客:http://www.htz.pw/


正文

这是我们在一个客户现场遇到的一条SQL性能问题,此SQL子查询结果集返回最多10行,但是整个SQL的性能切不好,此SQL最后还导致了一个核心系统故障,引起了一个悲剧的事情。

 

业务反应慢,查询v$session发现同时有24个回话在执行此SQL:7ug8q9myb0bsz,由于此SQL性能不好引起大量的GC等待,导致其它的业务受影响。

  

下面直接给出常量的SQL


Child_number 0



Child_number 1



首先说明一下,是OLTP环境。也就以为着要快速的返回结果,并且多数情况下,SQL返回的结果集不多。

 

在上图SQL中,有两处我们用红色的箭头标识出来。说明这部分信息需要我们关注的。在整个SQL中,就只存在2处过滤信息,一个是redu_owner_id,一个是status_cd。但是status_cd在两个子执行计划中都是相同的,所以这里就只剩余redu_owner_id这列了,我们也可以执行redu_owner_id所在的OP这个表,肯定是驱动表,并且redu_owner_id这列应该存在数据倾斜的情况。那么redu_owner_id返回的结果集将直接影响整个SQL性能的好坏。

 

下面继续查看SQL部分,可以发现一个重要的信息就是在子查询中存在rownum<10,也就意味子查询最多返回10行。在OLTP系统中,存在一个表最后最多返回10行的情况,这里也就大概想到了用子查询做去驱动表了,如果执行计划中,没有用子查询做驱动表,那么很有肯能执行计划就是错误的,那么这里的自己认为的驱动表与之前根据SQL前部分猜测出来当前执行的驱动表(OP)不一样。

 

下面查看执行计划



在执行计划中,我们看到当前执行计划的驱动表示OFFER_PROD(OP)这个表,与之前我们猜想一样,那么基本可以肯定,redu_owner_id列的数据存在倾斜,当返回大量结果集时,性能就很不好。

 

在执行计划中,这里特意把子查询标记出来,就是需要引起重视,子查询当着一个整体与主查询做HASH链接,没有作为驱动表走NL,也就可以肯定整个执行计划连最基本的驱动表都选择错误。下图可以更直观的看到。


 

这里做个补充:子查询当作整体,也就是被当作一个视图与主机做关联,什么情况下子查询会当作一个整体呢?

 

其实MOS有相关的文档说明的,大家可以去MOS一下,在本案例是由于ROWNUM<10导致的。

 

在V$SQL中查看每个child的统计信息



这里看到,存在两个子游标,他们的执行计划相等,但是两个子优化的性能相差很大,并且性能不好的子优化执行次数很多。

 

在上面我们提到主查询就只存在两个过滤条件。执行计划+谓词信息可以看到驱动表使用那个列来过滤数据。



在上面一直在说redu_owner_id这个列存在数据倾斜,那么下面早证实一下:

 

下面来查看,redu_owner_id的值的分布。首先看看两个不同绑定变量返回的行数:



通过这个信息,我们知道了,上面SQL由于列的值存在倾斜,导致SQL执行计划部分值执行很快,部分值执行很慢。

 

大家可能会说,在11G中,SQL引入了ACS功能,但是很不幸的事在客户这里ACS都是禁用了的。

  

下面就是怎么来优化这个SQL。在上面提到了子查询中最多返回10行,可以用于做NL的驱动,要让子查询的表做驱动表,应该怎么来修改SQL?

 

当然我们也可以通过修改SQL为 with as 的方式,由于在这个系列的其他案例中使用过,因此我们换一种方式来实现。也就是通过提示来达到目的。

 

这里使用cardinality提示,在SQL解析的时候告诉CBO表上存在多少行。表上存在的行数越少,也就意味着访问表的成本越低。

 

下面我们拿返回8611行的绑定变量来做测试

 

添加提示后的SQL如下:



红色部分就是添加的提示

 

执行计划



可以看到,子查询的结果集已经作为驱动表了。

 

统计信息



每次的逻辑读从原来的369927降低到现在的45 ,性能提升很明显,并且主要解决了RAC之间的GC等待,不影响其它的业务了。

 

优化SQL后,CPU使用率从原来的70%左右直接下降到25%左右,此系统的主机性能很NB的,8路的PC ,E7的CPU。

 

此案例结束,主要提到两个知识点:

1,  列的值分布不均匀,导致SQL性能出问题。

2,  通过cardinality来指定表的行数,达到指定表做驱动表的目的。



好消息:恩墨学院近期推出Oracle ACE 总监专家课程之顶级优化案例课程,在刚刚过去的周末,Oracle百科全书杨廷琨老师和中国地区首位 Oracle ACE总监盖国强老师分别进行了为期一天的精彩分享。从原理讲解、优化思路到身临其境模拟及演示,这才是 Oracle 数据库实战技能正确的打开方式。现场座无虚席,远程同步直播百人在线学习。开启恩墨学院2.0授课新篇章。


后期精彩课程咨询恩墨学院李老师:18311372955

扫码加微信


加入"云和恩墨大讲堂"微信群,参与讨论学习

搜索 盖国强(Eygle)微信号:eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。


关注公众号,获得后续精彩分享


「喜欢文章,快来给作者赞赏墨值吧」
文章转载自黄廷忠,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部