从一个真实案例看性能差异问题处理方法论

墨墨导读：本文来自墨天轮用户“取名浪费我半小时”投稿，个人主页：https://www.modb.pro/u/397063。

数据技术嘉年华，十周年盛大开启，点我立即报名！大会以“自研·智能·新基建——云和数据促创新 生态融合新十年” 为主题，相邀数据英雄，总结过往十年历程与成绩，展望未来十年趋势与目标！近60场演讲，大咖云集，李飞飞、苏光牛、林晓斌、黄东旭...，快来pick你喜欢的嘉宾主题吧！

相信每一个DBA或多或少都经历过下面这些灵魂拷问：

• 我的sql在测试环境跑的好好的，怎么一上生产就跑不动了？

• 这条sql明明以前跑的好好的，怎么现在有问题了？

• 我的sql在rac 节点1跑的好好的，怎么在节点2 跑不动？

• 我的sql在x86服务器上跑都没问题，怎么迁移到一体机上反而跑不动了？

最近，遇到了其中一个问题。客户的源环境是一台普通的x86服务器，上面部署了一个Oracle 10.2.0.5，最近新上了一批一体机，安装的Oracle 19c，现在数据已经迁过来了，然后便一直在做跑批测试，但是有个批量任务在一体机中从来就没有跑完过......于是客户就问我了：这个批量任务以前在普通的x86服务器上跑都没问题，怎么现在在性能这么好的一体机上反而每次都巨慢还中断退出呢？

这个批量任务在一体机中跑的具体情况是开始后大概15分钟就会将临时表空间撑爆，最开始根据报错情况前前后后多次加了临时表空间，但临时表空间都加到300G了，问题还是没能解决。

select file_name,bytes/1024/1024/1024,status,tablespace_name from dba_temp_files;

因为源环境的临时表空间120G，跑同样的批量没问题，现在一体机中的临时表空间300G，还是不够用，所以最先想到的原因可能是一体机环境是刚搭建完的，还没有进行参数优化，所以没有设置60025事件（针对使用临时表空间而不释放的问题），以及10949事件，检查一下果然没有设置，于是设置上这两个事件重新跑批，可是结果仍然不尽人意，不到15分钟alert日志就报了无法扩展临时表空间，看来300G临时表空间又用满了，并且这里的不够用并不是因为空间不释放，而可能就是实打实的不够用。（跑批之前已经收集过统计信息，所以不存在是因为统计信息的缘故导致sql执行计划的改变。）

看来以往的经验在这里没起到什么作用，只能换个方向继续排查。

于是让业务重新调起批量，然后监控跑批过程中，临时表空间都是被什么给占用了。

SELECT vt.inst_id, vs.sid, vs.serial#, vs.username, vs.machine, vs.saddr, vs.program, vs.module, vs.logon_time, vt.tempseg_usage, vt.segtype FROM gv$session vs, (SELECT inst_id, username, session_addr, segtype, ROUND(SUM(blocks) * 8192 / 1024 / 1024 / 1024, 2) tempseg_usage FROM gv$tempseg_usage GROUP BY inst_id, username, session_addr, segtype ORDER BY 4 DESC) vt WHERE vs.inst_id = vt.inst_id AND vs.saddr = vt.session_addr order by tempseg_usage desc;

间隔一段时间，再收集一次：

可以看到，临时表空间基本都是被用来做HASH相关运算了，其中3183和3542这两个会话，占用临时表空间在不断增长。接下来我们进一步查看这两个会话在干什么：

select sid,sql_id from v$session where sid=3183; select sid,sql_id from v$session where sid=3542;

SELECT         B.SEGFILE#,        B.SEGBLK#,        B.BLOCKS,        B.BLOCKS * 8 / 1024 / 1024,        A.SID,        A.SQL_ID,        A.SERIAL#,        A.USERNAME,        C.SQL_TEXT,        B.CONTENTS   FROM V$SESSION A, V$SORT_USAGE B, V$SQL C  WHERE A.SADDR = B.SESSION_ADDR    AND A.SQL_ADDRESS = C.ADDRESS(+)  ORDER BY B.BLOCKS DESC;

可以看出，临时表空间都是被同一条sql的执行所占用的。现在就很有理由怀疑是这条sql在源环境和一体机中是两个执行计划，并且在一体机中的执行计划涉及到大量的hash操作而占用过多的临时表空间，再加之是并行执行，从而导致临时表空间爆掉。

那么现在问题又来了，统计信息都是最新的，为什么两个环境的执行计划可能不一样呢？当然执行计划不一样目前还只是猜测，接下来我们看看两个环境中的执行计划有什么区别：

脱敏后sql文本如下：

SELECT A.col1,        A.col2,        A.col3,        A.col4,        A.col5,        A.col6,        A.col7,        A.col8,        A.col9,        A.XXXNO,        A.XXXCODE,        A.col10,        A.col11,        A.col12,        A.col13,        A.col14,        CASE          WHEN A.XXXCODE = '000001' THEN           (SELECT MAX(K.AMNT) FROM TAB_C K WHERE K.XXXNO = A.XXXNO)          WHEN A.XXXCODE = '000002' THEN           (SELECT K.AMNT              FROM TAB_C K             WHERE K.XXXNO = A.XXXNO               AND K.YYYCODE = '000003')          WHEN A.XXXCODE IN ('000004', '000005') THEN           (SELECT C.AMNT              FROM TAB_C C, ZZZCODE D             WHERE C.YYYCODE = D.YYYCODE               AND A.XXXNO = C.XXXNO)          WHEN A.XXXCODE = '000006' THEN           (SELECT C.AMNT              FROM TAB_C C, ZZZCODE D             WHERE C.YYYCODE = D.YYYCODE               AND C.YYYCODE IN ('000007', '000008')               AND A.XXXNO = C.XXXNO)          WHEN A.XXXCODE = '000009' THEN           (SELECT C.AMNT              FROM TAB_C C, ZZZCODE D             WHERE C.YYYCODE = D.YYYCODE               AND C.YYYCODE IN ('000010', '000011')               AND A.XXXNO = C.XXXNO)          ELSE           (SELECT C.AMNT              FROM TAB_C C, ZZZCODE D             WHERE C.YYYCODE = D.YYYCODE               AND A.XXXNO = C.XXXNO               AND D.CHOFLAG = 'M')        END,        CASE          WHEN A.XXXCODE = '000012' THEN           (SELECT NVL(SUM(PREM), 0)              FROM TAB_G             WHERE XXXNO = A.XXXNO               AND PAYPLANCODE IN ('000013', '000014'))          ELSE           (SELECT SUM(PREM)              FROM TAB_G             WHERE XXXNO = A.XXXNO               AND YYYCODE IN (SELECT YYYCODE FROM TAB_U))        END,        A.col15,        A.col16,        (SELECT SUM(MONEY) M FROM LCINSUREACCTRACE C WHERE C.XXXNO = A.XXXNO),        (SELECT MAX(F.FEEVALUE)           FROM TAB_F F, TAB_G G, TAB_H H          WHERE F.FEECODE = G.FEECODE            AND F.INSUACCNO = G.INSUACCNO            AND F.PAYPLANCODE = G.PAYPLANCODE            AND G.FEETAKEPLACE = '01'            AND H.INSUACCNO = G.INSUACCNO            AND H.PAYPLANCODE = G.PAYPLANCODE            AND H.XXXNO = A.XXXNO            AND F.GRPXXXNO = A.GRPXXXNO),        CASE          WHEN (A.XXXCODE = '000015') THEN           (CASE             WHEN (SELECT SUM(YYYCODE)                     FROM TAB_C                    WHERE XXXNO = A.XXXNO                      AND YYYCODE IN ('000016', '000017', '000018')) IS NOT NULL THEN              TO_NUMBER('1')             WHEN (SELECT SUM(YYYCODE)                     FROM TAB_C                    WHERE XXXNO = A.XXXNO                      AND YYYCODE IN ('000019', '000020', '000021')) IS NOT NULL THEN              TO_NUMBER('0')             ELSE              TO_NUMBER('2')           END)          ELSE           (CASE             WHEN A.XXXCODE = '000022' THEN              (SELECT FLOATRATE                 FROM TAB_C                WHERE XXXNO = A.XXXNO                  AND YYYCODE = '000023')             WHEN A.XXXCODE IN (SELECT XXXCODE FROM TAB_O) THEN              (SELECT MAX(ACCRATE)                 FROM TAB_S                WHERE INSUACCNO IN (SELECT INSUACCNO                                      FROM TAB_O                                     WHERE XXXCODE = A.XXXCODE))             ELSE              (SELECT MAX(RATE) FROM TAB_R WHERE XXXCODE = A.XXXCODE)           END)        END,        A.col17,        A.col18,        (SELECT G.SUPPRISKSCORE           FROM TAB_G G          WHERE G.PAYPLANTYPE = '01'            AND G.XXXNO = A.XXXNO),        (SELECT G.PREM           FROM TAB_G G          WHERE G.PAYPLANTYPE IN ('01', '02', '03', '04')            AND G.XXXNO = A.XXXNO),        A.col19,        A.col20,        A.INSUREDNO,        A.col21,        (SELECT I.IDTYPE FROM TAB_I I WHERE I.CUSTOMERNO = A.INSUREDNO),        (SELECT I.IDNO FROM TAB_I I WHERE I.CUSTOMERNO = A.INSUREDNO),        (SELECTMAX(I.GETLIMIT)           FROM TAB_I I          WHERE I.XXXNO = A.XXXNO            AND TRIM(I.YYYCODE) = SUBSTR(I.YYYCODE, 0, 6)),        (SELECT MAX(I.GETRATE)           FROM TAB_I I          WHERE I.XXXNO = A.XXXNO            AND TRIM(I.YYYCODE) = SUBSTR(I.YYYCODE, 0, 6)),        GETAMNTORPREM(A.XXXNO, 1),        GETAMNTORPREM(A.XXXNO, 2),        GETAMNTORPREM(A.XXXNO, 3),        GETAMNTORPREM(A.XXXNO, 4),        GETAMNTORPREM(A.XXXNO, 5),        GETAMNTORPREM(A.XXXNO, 6),        CASE          WHEN A.XXXCODE IN               ('000024', '000025', '000026', '000027', '000028') THEN           A.STANDBYFLAG1          ELSE           ''        END,        CASE          WHEN A.XXXCODE IN               ('000024', '000025', '000026', '000027', '000028') THEN           A.STANDBYFLAG2          ELSE           ''        END,        A.col22,        A.col23,        GETTERMINATETYPE(A.XXXNO, A.xxxNO, A.INSUREDNO),        (SELECT STARTDATE           FROM LCCONTSTATE          WHERE xxxNO = A.xxxNO            AND XXXNO = A.XXXNO            AND TRIM(STATETYPE) = 'Terminate'            AND ROWNUM = 1),        A.col24,        (SELECT CONTPLANCODE           FROM TAB_D          WHERE xxxNO = A.xxxNO            AND INSUREDNO = A.INSUREDNO),        (SELECT OPERSOURCETYPE FROM TAB_P P WHERE P.xxxNO = A.xxxNO),        (SELECT B.INSUACCTYPE FROM TAB_B B WHERE B.GRPXXXNO = A.GRPXXXNO),        (SELECT NVL(TAXRATE, 0)           FROM VATTAXRATEAPP          WHERE XXXCODE = A.XXXCODE            AND ROWNUM = 1)   FROM TAB_A A  WHERE A.SALECHNL = '2'    AND A.APPFLAG NOT IN ('0', '2', '9')    AND A.MANAGECOM LIKE :B1 || '%'    AND ((A.XXXCODE IN (SELECT XXXCODE                           FROM TAB_X                          WHERE A.XXXCODE = XXXCODE                            AND RISKPERIOD = 'L')) OR        (A.XXXCODE NOT IN (SELECT XXXCODE                               FROM TAB_X                              WHERE A.XXXCODE = XXXCODE                                AND RISKPERIOD = 'L') AND        ((A.APPFLAG <> '4') OR        (TO_CHAR(A.CVALIDATE) >        (SELECT (TO_CHAR(SYSDATE, 'yyyy') - 2) || '/12/31' FROM DUAL)) OR        (TO_CHAR(A.E NDDATE, 'yyyy/mm/dd') >        (SELECT TO_CHAR((SYSDATE - 30), 'yyyy/mm/dd') FROM DUAL)))));

源环境中的执行计划：

一体机中的执行计划：

可以看出源环境和一体机中的执行计划差别还是很大的，结合sql文本（标红部分），和一体机中的执行计划（红框标注部分），我们可以看出，sql文本中很多的标量子查询都被自动改写成了hash外连接，这也是两个环境中两个执行计划的最大差别。

为什么在迁移至一体机之后，在统计信息准确的情况下，执行计划就变化了呢，也就是为什么原来的环境标量子查询是没有被拆开的，而现在标量子查询部分被自动的转换成了hash外连接的形式。

分析到现在我们来回顾一下，源环境oracle 10.2.0.5中的一条包含很多标量子查询的sql语句运行正常，但业务迁移到一体机 oracle 19c之后，该条sql每次执行都要消耗大量临时表空间，并且并行一上来就导致临时表空间爆掉。在比较两个环境中的执行计划之后，发现在一体机环境中很多标量子查询都被自动的改写成了hash外连接。

现在很有理由怀疑是oracle高版本中的某个新特性导致执行计划改变的，通过和朋友一番求助终于找到了一个参数：_optimizer_unnest_scalar_sq。

在Oracle 12c之前，对于标量子查询，由于无法展开，所以外层查询返回多少条记录，标量子查询部分就要被执行多少次，也正因为此，标量子查询很多时候性能都不佳，而在Oracle 12c中新增了一个新特性即标量子查询自动转换，受隐含参数_optimizer_unnest_scalar_sq控制，这一特性默认是开启的，即该参数默认为true。他会将标量子查询展开，转换成一个group by视图，然后与外层表做连接即执行计划中的hash join outer。

但是需要注意的是，只有包含聚合函数（count函数除外）的标量子查询才会被自动转换，例如该sql文本中蓝色标注的max（），sum（）。

接下来就是验证了：

alter system set "_optimizer_unnest_scalar_sq"=false sid='*' scope=spfile;

让人绝望的是，不到10分钟临时表空间又爆掉了......，再一次陷入沉思，前面根据对执行计划的比对，基本可以十分肯定就是这个参数导致的问题啊，怎么还不起作用？难道参数没生效，不可能，特意检查了啊....想了一会原来是因为我之前给这条sql绑定过一个执行计划，现在即使这个参数生效了，使用的还是之前绑定的那个执行计划，还是得细心啊。

begin DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_fygmw8mt23uum_3203434082'); end; /

删除sql_profile之后，再次调起批量任务，监控临时表空间的使用率，300G的临时表空间使用不到10G，最终批量任务也成功跑完。当然在sql文本中加no_unnest的hint或者加OPTIMIZER_FEATURES_ENABLE('10.2.0.5')的hint应该也是可以解决这一问题的，这里就没再做测试了 。

性能差异问题仿佛是dba始终摆脱不掉的一个问题，我们先来看看都有哪些性能差异的场景：

• 测试环境和生产环境性能存在差异（不同的硬件）

• 数据库迁移前后存在性能差异（不同的数据库版本及硬件）

• 相同的数据库版本相同的硬件，以前和现在性能存在差异

• rac 不同节点性能存在差异

首先在不考虑硬件和数据库版本存在差异的情况下，对于性能差异问题，我一般都还是先依照最常规的优化手段：v$session或者ASH查看等待事件的差异，sql_monitor查看真实执行计划的差异，10046事件查看解析阶段和执行阶段的差异，10053事件查看生成某个执行计划的具体原因，oradebug  short_stack查看进程堆栈的差异，perf top查看应用级函数调用的差异。一般如果不是特别棘手的问题，这一套组合拳下来估计也就差不多了。

接下来我们再看硬件存在差异的情况：硬件不同，一般指的是内存大小不同，这也直接影响到sga pga的大小分配；cpu核数；以及cpu性能，需要特别关注的一个指标就是MHz（cpu主频），可以通过以下命令查看：cat /proc/cpuinfo | grep "MHz"，并且很多时候，不是说只有两个环境才可能存在不同的cpu性能，很可能同一个数据库的两个rac节点的cpu性能存在差异，从而导致rac不同节点性能存在差异，另外就是需要注意cpu节能模式或者说cpu折叠是否开启；对于存储的差异这个就不多说了，ASH中的信息就能很方便的排查读写性能。

再来看看数据库版本存在差异的情况：其实这种很多都是低版本到高版本迁移之后存在的问题，很容易想到的就是高版本的参数，以及高版本的新特性可能并不适用于所有情况，例如最常见的cardinality feedback以及statistics feedback特性。所以的参数以及新特性我们不可能都一一记住，所以还是需要通过一些列的常规分析最终才能定位到是不是某个参数或新特性导致的性能差异。

最后我们来看看终极灵魂拷问，过去和现在性能存在差异的情况：我觉的这也是最棘手的一种情况，同样的数据库版本，同样的硬件环境，过去性能好，现在性能差，我目前能想到的有统计信息的及时性；业务上新上来一些特殊数据，打破原有表中的数据分布，从而生成了性能更差的执行计划；特定时段内，系统自动任务的异常影响了业务的正常运行......

墨天轮原文链接：https://www.modb.pro/db/40290（复制到浏览器中打开或者点击“阅读原文”）

推荐阅读：144页！分享珍藏已久的数据库技术年刊

数据技术嘉年华，汇聚业内多种数据库最佳实践和顶级技术专家，只为总结 2020 ，与您尽享技术前沿，领先一步卓立变革潮头！

2020 数据技术嘉年华，现在加入，尽享超低票价优惠：

视频号，新的分享时代，关注我们，看看有什么新发现？

数据和云

ID：OraNews

如有收获，请划至底部，点击“在看”，谢谢！

点击下图查看更多 ↓

云和恩墨大讲堂 | 一个分享交流的地方

长按，识别二维码，加入万人交流社群

请备注：云和恩墨大讲堂

  点个“在看”

你的喜欢会被看到❤