用MERGE改写UPDATE的优化

最新推荐文章于 2021-04-05 03:16:02 发布

24号信仰

最新推荐文章于 2021-04-05 03:16:02 发布

阅读量3.3k

点赞数 2

分类专栏：【SQL TUNING】 24号信仰の优化专栏文章标签： MERGE UPDATE MERGE改写 UPDATE优化 MERGE优化

本文链接：https://blog.csdn.net/Skybig1988/article/details/71080901

版权

【SQL TUNING】同时被 2 个专栏收录

37 篇文章 3 订阅

订阅专栏

24号信仰の优化专栏

37 篇文章 4 订阅

订阅专栏

某系统负责人反应一个新上线的存储过程跑了 10个小时没出结果，中间卡在了一个update语句上面：

ETL日志看到这个流程2017-04-11 23:59:43开始跑，到第二天10点还没有跑完

开发告诉我他们存储过程里面新上线了一条update语句，在测试环境测试可以跑出结果：sql如下

 UPDATE UUU_UIU_ITTTTTTTTT S
   SET S.ASSET_LOSS_PRVS =       
       (SELECT LOSS_BAL
          FROM ZZZ_Z_ZZZZ_ZZ SW
         WHERE SW.ZZZZ_NNN = S.MA_ZZZZ_NN
           AND ETL_DATE = :B1) *
       (SELECT F.CONVERSION_RATE
          FROM VW_DDIDD_DDDDD F
         WHERE CONVERSION_DATE = :B1
           AND F.CURRENCY_CD = S.CURRENCY_CD)           
 WHERE S.GL_ACCT_ID IS NOT NULL;

通过V$SESSION视图同样看到的是这个update语句active状态持续了很长时间，可以断定整个存储过程的主要问题是这个update语句

开始优化

1.查看这条SQL的执行计划和这个SQL相关表的统计信息如下：

Plan hash value: 1873025285
 
----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                               |    35M|  1670M|   385G  (1)|999:59:59 |
|   1 |  UPDATE            | UUU_UIU_ITTTTTTTTT            |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| UUU_UIU_ITTTTTTTTT            |    35M|  1670M|   559K  (1)| 01:51:56 |
|*  3 |   TABLE ACCESS FULL| ZZZ_Z_ZZZZ_ZZ                 |     2 |    56 | 10783   (1)| 00:02:10 |
|*  4 |   TABLE ACCESS FULL| ZZZ_ZIZ_ZZZZ_ZZZZ_INFO_ZZZZZZ |     1 |    35 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

表的相关信息特别重要,所以我定制脚本去获取执行计划中所涉及的所有表相关的信息

问题非常明显：
1.要UPDATE的表UUU_UIU_ITTTTTTTTT表有3.5kw条数据也就意味着ZZZ_Z_ZZZZ_ZZ SW 和 VW_DDIDD_DDDDD F 会被通过TABLE ACCESS FULL分别访问3.5kw次
而且VW_DDIDD_DDDDD F 是一个视图，视图里面所有涉及到的表（庆幸这个视图里面就一张表）同样会被访问 3.5kw次。所以这个SQL执行会执行很长时间。而且不会报错。
2.DPDATE的这种用另一个表的列去更新主表的写法，SQL只能走嵌套循环，而且只能选择UUU_UIU_ITTTTTTTTT S（3.5kw条数据）作为驱动表
所以要解决这个问题。只能用改写语句

解决：改写语句需要通过开发了解业务
开发反馈：通过关联ZZZ_Z_ZZZZ_ZZ SW表 WHERE SW.ZZZZ_NNN = S.MA_ZZZZ_NN 为了补录 UUU_UIU_ITTTTTTTTT 表里面缺失的数据，

也就是【匹配到数据才更新】
而VW_DDIDD_DDDDD F 这个表相当于参数表，取出汇率相乘作运算
到这里。我才发现
这个SQL不仅在执行效率上非常坑爹，根本就是一条完全业务上完全错误的SQL
她这个SQL的结果是UUU_UIU_ITTTTTTTTT S表通过ZZZ_Z_ZZZZ_ZZ SW去更新：【匹配到更新，匹配不到会更新成空值】。DPDATE经常容易出现这种错误
庆幸这个SQL么有跑出结果，否则被更新的字段将会出现很多NULL值。当然还有一个坑爹之处我到后面才发现，往下看

用MERGE来改写UPDATE，最终的结果就是希望SQL走HASH连接,来解决因为驱动表返回的行数太多而引发的性能问题

我尝试了一下，没搞定【老司机可以尝试一下】
1.ZZZ_Z_ZZZZ_ZZ SW和VW_DDIDD_DDDDD F视图没法关联
2.MERGE和UPDATE一样没法更新不固化的视图
所以思考了一下根据业务拆分成两条SQL

MERGE INTO UUU_UIU_ITTTTTTTTT S
USING (SELECT SW.ZZZZ_NNN,LOSS_BAL FROM ZZZ_Z_ZZZZ_ZZ SW where ETL_DATE = :B1) SW
on (SW.ZZZZ_NNN = S.MA_ZZZZ_NN)
when matched then
  UPDATE SET S.ASSET_LOSS_PRVS = LOSS_BAL
   WHERE S.GL_ACCT_ID IS NOT NULL;

COMMIT;

MERGE INTO UUU_UIU_ITTTTTTTTT S
USING (SELECT F.CONVERSION_RATE,CURRENCY_CD
         FROM VW_DDIDD_DDDDD F
        WHERE CONVERSION_DATE = :B1) F
ON (F.CURRENCY_CD = S.CURRENCY_CD)
when matched then
  UPDATE 
     SET S.ASSET_LOSS_PRVS = S.ASSET_LOSS_PRVS * F.CONVERSION_RATE
      WHERE S.GL_ACCT_ID IS NOT NULL;
COMMIT;

执行计划如下
Plan hash value: 3526885530
 
---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |                    |  2783K|  1242M|       |  1261K  (1)| 04:12:24 |
|   1 |  MERGE               | UUU_UIU_ITTTTTTTTT |       |       |       |            |          |
|   2 |   VIEW               |                    |       |       |       |            |          |
|*  3 |    HASH JOIN         |                    |  2783K|  1117M|   106M|  1261K  (1)| 04:12:24 |
|*  4 |     TABLE ACCESS FULL| ZZZ_Z_ZZZZ_ZZ      |  2783K|    74M|       | 10818   (1)| 00:02:10 |
|   5 |     TABLE ACCESS FULL| UUU_UIU_ITTTTTTTTT |    35M|    13G|       |   561K  (1)| 01:52:15 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("SW"."ZZZZ_NNN"="S"."MA_ZZZZ_NN")
   4 - filter("ETL_DATE"=:B1)


Plan hash value: 2040901539
 
------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |                               |    19M|  8811M|   561K  (1)| 01:52:17 |
|   1 |  MERGE               | UUU_UIU_ITTTTTTTTT            |       |       |            |          |
|   2 |   VIEW               |                               |       |       |            |          |
|*  3 |    HASH JOIN         |                               |    19M|  7840M|   561K  (1)| 01:52:17 |
|*  4 |     TABLE ACCESS FULL| ZZZ_ZIZ_ZZZZ_ZZZZ_INFO_ZZZZZZ |     6 |   210 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| UUU_UIU_ITTTTTTTTT            |    35M|    13G|   561K  (1)| 01:52:15 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."SRC_CCY_CD"="S"."CURRENCY_CD")
   4 - filter("A"."TARGET_CCY_CD"='T01' AND "A"."EXCH_RATE_TYP_CD"='GLS0_Corporate' AND 
              "A"."ETL_DATE"=:B1)

通过和开发确认，修改后的语句符合业务逻辑。可以更新存储过程

在将这个语句放入存储过程的时候，我发现了另一个问题：UPDATE之后没有使用COMMIT语句【在存储过程最后是有一个COMMIT】
但是这样的写法会导致很大问题。【每个事务之后都应该COMMIT，否则小事务就会慢慢变成大事务。开发规范明确要求避免大事务】

修改后性能大幅度提升，重新调度存储过程前后仅用了不到10分钟搞定