见微知著：一条 SQL 性能问题引发的核心系统悲剧

黄廷忠（网名：认真就输）

云和恩墨技术专家

个人博客：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性能问题诊断

下面直接给出常量的 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 的修改

下面就是怎么来优化这个 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优化是一项专业的技能，必须深入了解数据库原理才可能做出准确的判断，在开发过程中强化开发人员培训，引入SQL审核、审计，是确保应用高性能的有力手段。 文章转自数据和云公众号，原文链接

【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高 怎样对数据组合重新排列并去重的问题、通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。本篇文章主要介绍的两个方面，第一个方面曾经有好几个网友和同事问我，第二个问题真的是很多同行的通病，认为分析函数是万金油，一股脑用。
几个必须掌握的SQL优化技巧(五)：Show Profile分析SQL性能 在应用的开发过程中，由于开发初期的数据量一般都比较小，所以开发过程中一般都比较注重功能上的实现，但是当完成了一个应用或者系统之后，随着生产数据量的急剧增长，那么之前的很多sql语句的写法就会显现出一定的性能问题，对生产的影响也会越来越大，这些不恰当的sql语句就会成为整个系统性能的瓶颈，为了追求系统的极致性能，必须要对它们进行优化。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载