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

黄廷忠 2017-06-20

402

题记：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授课新篇章。