黄廷忠（网名：认真就输）
云和恩墨技术专家
个人博客：http://www.htz.pw/
本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例，SQL 优化及 SQL审核，是从源头解决性能问题的根本手段，无论是开发人员还是DBA，都应当持续深入的学习 SQL 开发技能，从而为解决性能问题打下根基。
第一篇为：性能为王：SQL标量子查询的优化案例分析
第二篇为：SQL审核：OR展开与子查询优化案例详解
本篇为系列案例之三：IN子查询返回结果集很小
这是不久前在一个客户现场遇到的一条 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?
在上一个案例中，通过了with as 的方式来改写。
这个案例就不修改 SQL，通过提示（Hints）来达到目的。
这里使用 cardinality 提示，在 SQL 解析的时候告诉 CBO 表上存在多少行。表上存在的行数越少，也就意味着访问表的成本越低。
下面我们拿返回8611行的绑定变量来做测试
添加提示后的 SQL 如下：
红色部分就是添加的提示，执行计划如下：
可以看到，子查询的结果集已经作为驱动表了。
统计信息
每次的逻辑读从原来的369，927降低到现在的45 ，性能提升很明显，并且主要解决了 RAC之间的 GC 等待，不影响其它的业务了。
优化 SQL 后，CPU 使用率从原来的70%左右直接下降到25%左右，此系统的主机性能很 NB 的，8路的 PC ，E7 的 CPU。
此案例结束，主要提到两个知识点：
列的值分布不均匀，导致 SQL 性能出问题
通过 cardinality 来指定表的行数，达到指定表做驱动表的目的
SQL优化是一项专业的技能，必须深入了解数据库原理才可能做出准确的判断，在开发过程中强化开发人员培训，引入SQL审核、审计，是确保应用高性能的有力手段。 文章转自数据和云公众号，原文链接
