性能为王:SQL标量子查询的优化案例分析

黄廷忠(网名:认真就输)

云和恩墨技术专家

个人博客:http://www.htz.pw/

本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基。

本篇为系列案例之一:标量子查询优化

以下案例来自于某省电信系统EDW性能优化实践,数据库版本为11.2.0.3,运行在ORACLE Exadata一体机上,是个典型的OLAP环境,表上无索引,表无统计信息

SQL性能问题诊断

SQL的基本逻辑如下:

SELECT OFFER_SERV_SUM AS N37364, LOCAL_CODE LOCAL_CODE, AREA_ID AREA_ID, DVLP_AREA_ID MG_AREA_ID, DVLP_ORG_ID ORG_ID, CASE WHEN OFFER_SPEC_ID IN (SELECT LOCAL_ITEM_CODE FROM PU_META_DIM.CODE_ITEM WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR(OFFER_SPEC_ID) IN (SELECT LOCAL_ITEM_CODE FROM PU_META_DIM.CODE_ITEM A WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71)) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR(OFFER_SPEC_ID) IN (SELECT OFFER_SPEC_ID FROM PU_META_DIM.D_DRAGON_PLAN WHERE BT_CODE = 1 AND (UPPER(NAME) LIKE'%4S%'ORNAMELIKE'%??%')) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR(OFFER_SPEC_ID) IN (SELECT LOCAL_ITEM_CODE FROM PU_META_DIM.CODE_ITEM WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR(OFFER_SPEC_ID) IN (SELECT OFFER_SPEC_ID FROM PU_META_DIM.TY_SUIT_CFG) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR(OFFER_SPEC_ID) IN (SELECT OFFER_SPEC_ID FROM PU_META_DIM.D_DRAGON_PLAN WHERE BT_CODE = 2 AND (UPPER(NAME) LIKE'%4S%'ORNAMELIKE'%??%')) THEN 1 ELSE 0 END | C_ALL FROM PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407 PARTITION(P20140727) A WHERE LOCAL_CODE = '028'

这个SQL一共格式化出来有600多行,由于篇幅的限制,这里把其它一些无用的列的信息去掉。下面来查询一下SQL的执行计划:

从上面的关键字部分STORAGE FULL FIRST ROWS这部分,我们也可以看到是一个Exadata一体机的环境。

SQL的执行计划很简单,一共只有10行。FROM后对一个分区表的一个子分区执行全分区扫描。

下面来看看这个SQL每次执行消耗的物理读与逻辑读。

这里需要关注几点:

1, 每次执行消耗的物理读(diskpre exec) 2, 每次执行平均消耗的逻辑读(getpre exec) 3, 每次执行平均返回的行数(rowspre exec)

这个脚本的输出我们还需要计算一下

1, 每次执行SQL,返回的每行平均消耗的逻辑读338280770/4302704=78.62,大概逻辑读78才能换回一行。 2, 每次执行SQL,返回的每行平均消耗的物理读22610/4302704=0.005

从上面几点,大概知道这个SQL存在性能问题。

基础信息分析

PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407是个分区表,下面查询一下表分区的信息:

这里可以看到表是一个RANGE-LIST的分区表。下面查询SQL关联的表的大小:

由于是分区表,所有这里需要去查询访问的分区的大小:

这里看到访问的分区只有84M,加上code_item,ty_suit_cfg,d_dragon_plan表一共才4288+84=4372M,等于 =559616 个BLOCK。

下面我们考虑一种极端的条件下,SQL访问的几张表都走全表扫描,并且走HASH连接。那么此时物理读加逻辑读应该接近 559,616 (这里不考虑TEMP等消耗,不考虑事务一致性等原因,只考虑表的大小),但是整个SQL消耗的物理都为22610,逻辑读是:338,280,770。远远大于SQL访问的表占用的物理大小。所以初步判断在执行计划中存在某个对象被轮询。

下面查询访问的分区的行数,这里由于是OLAP系统,无统计信息,只能手动运行SQL来查询,如果有统计信息,可以大概根据统计信息来计算,虽然不是很准确,但是我觉得足够我们判断SQL性能了,何况CBO都是基于统计信息的。

这里看到028返回了400W行的数据。

问题定位与SQL改写

通过上面的信息,可以知道SQL变慢的原因 :

由于PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407 PARTITION(P20140727) 返回一行,就需要去执行所有的标量子查询,虽然在一体机环境这里有first rows,但是所有的标量子查询被轮询的次数达到了4,302,704次,并且子查询的表走的全表扫描,所以出现了逻辑读很高。

逻辑读比物理读性能好,并且逻辑读消耗的时间很短,但是过高的逻辑读会带来CPU使用率的增加,RAC环境会导致过多的GC等待,还有可能会影响后来的一些TX,INDEX ITL等等待事件的出现,前不久就曾经遇到一个逻辑读导致GC等待,又引起了TX,INDEX ITL,BBW等待事件,没有多久,业务连接池就满了,最后整个业务受影响。

为了减少标量子查询被轮询的次数,这里可以把标量子查询改为外连接。

为了减少SQL长度,下面是去掉SELECT中一些不需要的部分,我们手动测试一下SQL改为外连接与原SQL执行SQL的差异:

SELECT OFFER_SERV_SUM AS N37364, LOCAL_CODE LOCAL_CODE, AREA_ID AREA_ID, DVLP_AREA_ID MG_AREA_ID, DVLP_ORG_ID ORG_ID, CASE WHEN a.OFFER_SPEC_ID = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (a.OFFER_SPEC_ID) = e.LOCAL_ITEM_CODE THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (a.OFFER_SPEC_ID)= c.offer_spec_id AND c.bt_code = 1 THEN 1 ELSE 0 END || CASE WHEN a.OFFER_SPEC_ID = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (a.OFFER_SPEC_ID) = d.OFFER_SPEC_ID THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (a.OFFER_SPEC_ID)= c.offer_spec_id AND c.bt_code = 2 THEN 1 ELSE 0 END C_ALL FROM PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407 PARTITION (P20140727) A, (SELECT DISTINCT LOCAL_ITEM_CODE FROM PU_META_DIM.CODE_ITEM WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64) b, (SELECT DISTINCT OFFER_SPEC_ID, bt_code FROM PU_META_DIM.D_DRAGON_PLAN WHERE BT_CODE IN (1, 2) AND (UPPER (NAME) LIKE '%4S%' ORNAME LIKE '%??%')) c, (SELECT DISTINCT OFFER_SPEC_ID FROM PU_META_DIM.TY_SUIT_CFG) d, (SELECT DISTINCT LOCAL_ITEM_CODE FROM PU_META_DIM.CODE_ITEM A WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71)) e WHERE LOCAL_CODE = '028' AND a.OFFER_SPEC_ID = b.LOCAL_ITEM_CODE(+) AND TO_CHAR (a.OFFER_SPEC_ID) = c.OFFER_SPEC_ID(+) AND TO_CHAR (a.OFFER_SPEC_ID) = d.OFFER_SPEC_ID(+) AND TO_CHAR (a.OFFER_SPEC_ID) = e.LOCAL_ITEM_CODE(+)

执行计划及统计信息:

这里可以看到所有的表都是通过HASH 来做UNIQUE

这里看到逻辑读是303475,行返回是4,302,704,不知道大家是否还记得在之前我们获取SQL的统计信息的时候,看到SQL每次执行平均换回的行数是4,302,704。

两个值是一样的,说明我们在此条SQL改写后是等价的。

这里用到了”此条”,因为如果在连接列有一些空值的情况下得到的结果可以不一样,大家可以测试一下。

性能优化效果对比

下面看看原SQL去掉SELECT不需要的部分执行的信息

SELECT OFFER_SERV_SUM AS N37364, LOCAL_CODE LOCAL_CODE, AREA_ID AREA_ID, DVLP_AREA_ID MG_AREA_ID, DVLP_ORG_ID ORG_ID, CASE WHEN OFFER_SPEC_ID IN (SELECT LOCAL_ITEM_CODE FROMPU_META_DIM.CODE_ITEM WHERE PROV_TYPE_ID = 49AND PROV_ITEM_ID = 64) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (OFFER_SPEC_ID) IN (SELECT LOCAL_ITEM_CODE FROMPU_META_DIM.CODE_ITEM A WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_IDIN (85, 86, 68, 69, 70, 71)) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (OFFER_SPEC_ID) IN (SELECT OFFER_SPEC_ID FROMPU_META_DIM.D_DRAGON_PLAN WHERE BT_CODE = 1 AND ( UPPER (NAME) LIKE '%4S%' OR NAME LIKE'%??%')) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (OFFER_SPEC_ID) IN (SELECT LOCAL_ITEM_CODE FROMPU_META_DIM.CODE_ITEM WHERE PROV_TYPE_ID = 49AND PROV_ITEM_ID = 64) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (OFFER_SPEC_ID) IN (SELECT OFFER_SPEC_ID FROMPU_META_DIM.TY_SUIT_CFG) THEN 1 ELSE 0 END || CASE WHEN TO_CHAR (OFFER_SPEC_ID) IN (SELECT OFFER_SPEC_ID FROM PU_META_DIM.D_DRAGON_PLAN WHERE BT_CODE = 2 AND ( UPPER (NAME) LIKE '%4S%' OR NAME LIKE'%??%')) THEN 1 ELSE 0 END C_ALL FROM PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407 PARTITION (P20140727) A WHERE LOCAL_CODE = '028'

这条语句,运行了30分钟,都没有任何行返回:

可以看到执行了27分钟,才处理1908315,接近1/2的结果集行数。如果要处理完,估计需要60分钟以上。

总结

下面来总结一下此案例:

1. 在标量子查询中,当主查询返回一行数据时,所有的标量子查询就要执行一次,如果在连接列有索引时,标量子查询在主表返回的行很少的情况下,对性能影响不大,常常出现在OLTP环境,并且连接列一般都有索引;如果在OLAP环境中,看到标量子查询千万要小心,通常,主表返回的行很多,并且子查询中的表通常在连接列上面无索引,导致性能很低下,本案例就是这种情况; 2. 平均每次执行时消耗的逻辑读、物理读,返回的行平均消耗的逻辑读、物理读,平均返回的行数等信息可以用于初步判断SQL是否存在性能问题

遇到这种标量子查询,就得修改SQL,也就意味着业务需要修改代码,像电信运营商这种环境可能要好一点,有专门的开发团队,但是估计提交修改申请、开发修改、业务测试上线,差不多也要1到2个月的时间,如果在一些小的环境,估计开发商都找不到了,就跟谈不上改业务了。

12c新特性改进

对于类似以上的情况,在Oracle Database 12C中,优化器已经可以自动实现等价改写,但是需要注意的在12.1.0.2版本中有BUG,可能导致结果集不准确。

在12C中,标量子查询自动改写的功能由隐含参数 _optimizer_unnest_scalar_sq 控制,默认是TRUE,意味着开启,如果遇到BUG或者性能问题,可以更改为FALSE。

近期文章

新年贺礼:云和恩墨大讲堂期刊第二期

删繁就简-云和恩墨的一道面试题解析

用SQL解一道数学题:Gauss和Poincare

新年贺礼:云和恩墨大讲堂期刊发行

2015 Oracle 十大热门文章精选

Oracle 12c ASM 防火防盗新特性揭秘

DBA入门之路:学习与进阶之经验谈

DBA入门之路:关于日常工作的建议

业务架构

电子渠道(网络销售)分析系统、数据治理

IT基础架构

分布式存储解决方案 | zData一体机 | 容灾环境建设

数据架构

Oracle DB2 MySQL NoSQL

专项服务:架构/安全/容灾/优化/整合/升级/迁移

运维服务:运维服务 代维服务

人才培养:个人认证 企业内训

软件产品:SQL审核、监控、数据恢复

应用架构

应用软件和中间件:数据建模 | SQL审核和优化 | 中间件服务

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

原文发表时间:2016-02-17

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏happyJared

爬虫进阶:Scrapy抓取慕课网

  完整的爬虫流程大致是这样的:分析页面结构 -> 确定提取信息 -> 设计相应表结构 -> 编写爬虫脚本 -> 数据保存入库;入库可以选择mongo这样的文档...

3724
来自专栏沃趣科技

SQL优化案例-自定义函数索引(五)

SQL文本如下,表本身很小,走全表扫描也很快,但因业务重要性,要求尽可能缩短查询时间(为保证客户隐私,已经将注释和文字部分去掉):

1033
来自专栏java达人

SQL索引优化

序言 数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的S...

2088
来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4736
来自专栏咸鱼不闲

mysql一对多查询合并多的一方的数据。

有时候会有这样一个需求, 查询的一条记录需要包含另一个表的多条记录,并且让多条记录成为一个字段组成最终的一条记录。比较难描述,看例子吧。

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

简单对比MySQL和Oracle中的一个sql解析细节 (r5笔记第40天)

SQL的语法解析器是一个很强大的内置工具集,里面会涉及到很多的编译原理的相关知识,语法分析,词法分析。。一大堆看起来很理论的东东,不过看起来枯燥之余,它们的价值...

3548
来自专栏禁心尽力

一次浴火重生的MySQL优化(EXPLAIN命令详解)

一直对SQL优化的技能心存无限的向往,之前面试的时候有很多面试官都会来一句,你会优化吗?我说我不太会,这时可能很多人就会有点儿说法了,比如会说不要使用通配符*...

2025
来自专栏james大数据架构

你真的会玩SQL吗?你所不知道的 数据聚合

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

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

一条SQL语句的执行计划变化探究(r10笔记第3天)

最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落...

3316
来自专栏青玉伏案

OracleDBA之表管理

  下面是Oracle表管理的部分,用到的测试表是oracle数据库中scott用户下的表做的测试,有的实验也用到了hr用户的数据,以下这些东西是我的麦库上存的...

2058

扫码关注云+社区

领取腾讯云代金券