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 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

在线重定义的补充测试(r10笔记第26天)

在很多时候,我们都是需要保持业务的可持续性,尽管说DDL的过程持续时间很短,但是在线业务出现,就会阻塞DML,导致业务访问中断,事务收到影响,所以在有些...

3528
来自专栏码神联盟

碎片化 | 第四阶段-48-hibernate概述和配置-视频

本套视频从Java基础到架构模式以及AI算法,整体视频以“碎片化”学习的模式,提供给大家 ,并配备实际项目为案例,让大家在坐车、吃饭、午休、蹲坑的时候,都可以学...

3366
来自专栏数据和云

Oracle数据库中最让人匪夷所思的十大问题盘点

数据的世界无奇不有,常常会遇到一些超出常识之外的故障的发生。这就要求广大的DBA要深入了解数据库的内部机制,面对一些奇葩的故障或者问题能够拨开迷雾找到真相。今...

3045
来自专栏企鹅号快讯

WebGenerate 产品介绍

北京劳恩斯科技有限公司 网址: WebGenerate降本增效的代码生产工具,经过WebGenerate生产的代码适用于Spring、MyBatis、Maven...

1927
来自专栏北京马哥教育

优化 SQL SELECT 语句性能的 6 个简单技巧

SELECT语句的性能调优有时是一个非常耗时的任务,在我看来它遵循帕累托原则。20%的努力很可能会给你带来80%的性能提升,而为了获得另外20%的性能提升你可能...

46611
来自专栏数据和云

2015 OOW:Oracle的Sharding技术

在2015年OOW大会,国内很多小伙伴们一直非常关心Oracle Database 12.2中的Sharding技术实现,可是要知道在Larry Ellison...

2914
来自专栏数据和云

实战课堂:系统CPU高消耗的SQL筛选和最佳索引优化

在一次客户系统性能优化项目中,经过第一阶段的优化之后,数据库的DB Time和物理读都明显降低,但是我们发现CPU并没有明显降低。

1284
来自专栏MySQL实战分享

数据库评测报告第一期:MySQL-5.7

这一期的评测报告就着重针对MySQL-5.7在SELECT和UPDATE 中的性能,通过与MariaDB-5.5的对比,展示下新版本MySQL的性能提升。

7854
来自专栏FreeBuf

经验分享:社会工程学数据库搭建TIPS

最近一直在搞社工库的搭建。网上这方面也有很多文章,但是很少涉及到细节,在此与大家分享一些个人心得。 测试环境 测试坏境:windows server 2012...

2828
来自专栏人工智能LeadAI

ElasticSearch优化系列三:索引过程

大家可能会遇到索引数据比较慢的过程。其实明白索引的原理就可以有针对性的进行优化。ES索引的过程到相对Lucene的索引过程多了分布式数据的扩展,而这ES主要是用...

4069

扫码关注云+社区

领取腾讯云代金券