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

题记: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来指定表的行数,达到指定表做驱动表的目的。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-06-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

深入内核:Asm Rebalance 原理

李真旭@killdb Oracle ACE,云和恩墨技术专家 个人博客:www.killdb.com 编辑手记:ASM Rebalance 的过程具体发生了什么...

3616
来自专栏流柯技术学院

Oracle的AWR报告分析

* 定义:awr报告是oracle 10g下提供的一种性能收集和分析工具,它能提供一个时间段内整个系统资源使用情况的报告,通过这个报告,我们就可以了解一个系统的...

1161
来自专栏微信终端开发团队的专栏

微信终端跨平台组件 mars 系列(一):高性能日志模块xlog

mars 是微信官方的终端基础组件,是一个使用 C++ 编写的业务性无关,平台性无关的基础组件。本文章是 mars 系列的第一篇:高性能跨平台日志模块。

2790
来自专栏数据和云

深入剖析:关于cache buffers chains的经典案例处理详解

? 卢文星 目前就职云和恩墨,南区交付工程师,有超过8年超大型数据库管理经验,擅长Oracle数据库性能优化与升级迁移。 作者介绍 故障现象 某省税务核心业务...

2646
来自专栏FreeBuf

初识Linux渗透:从枚举到内核利用

许多人都认为Linux是最安全的操作系统,因此在对Linux的安全问题上也放松了警惕。那么事实真的如此吗?其实安全从来都只是相对的,Linux也不例外。虽然它加...

1256
来自专栏FreeBuf

有工具了,如何快速发现Windows中毒(含工具下载)

本文是之前《没有外部工具,如何快速发现Windows中毒了》的姊妹篇,探讨Windows电脑感染多种典型病毒后,在没有专业杀毒软件情况下的快速检测方法。 Pro...

1869
来自专栏用户2442861的专栏

哪个版本的gcc才支持c11

(而我此处的eglibc 2.17,和那人的glibc-2.16.0,都是需要支持c11的gcc的)

962
来自专栏CSDN技术头条

如何最有效地编写SQL

解决数据库级(SQL)工作上的问题,应该采用的是SET方法(整体的)而不是过程式的方法。下面来看看作者为什么这么说。 编写有效的SQL查询是企业软件世界中最大的...

2076
来自专栏黑白安全

Github寻找敏感信息

0x01 引子 先给不知道什么是Github的朋友们科普一下什么是Github Github是一个分布式的版本控制系统,目前拥有140多万开发者用户。随着越...

1652
来自专栏更流畅、简洁的软件开发方式

利用虚拟硬盘(把内存当作硬盘)来提高数据库的效率 续

续 上一篇引起了大家的讨论,看着讨论我是比较晕的,这也怪我没有说清楚,所以再补一个续把问题说清楚吧。 笔记本配置 CPU:Core 2 7250 2.0G ...

1859

扫码关注云+社区