Oracle标量子查询执行计划,性能为王：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审核和优化 | 中间件服务

本文分享自微信公众号 -