利用Merge into 改写Update SQL 一例

前言

客户说，生产系统最近CPU使用率经常达到100%，请DBA帮忙调查一下。

根据客户提供的情况描述及对应时间段，我导出AWR，发现如下问题：

11v41vaj06pjd ：每次执行消耗2,378,874.14 buffer 约等于18g 内存
bsfrz471nh9s4：每次执行消耗1,545,875.18 buffer 约等于12g 内存
非常大的内存消耗，而且执行频率高。
所以就断定这两条sql就是cpu使用率高的祸源，只要优化这两条sql，cpu必然而然的降下来。

优化前

这两条sql的结构是一样的，只是表连接有所不同，所以优化方法都是一致的。

update mm_writeoutstatus_to s
   set s.status = '00'
 where s.status = '0Z'
   and s.id in (select distinct t.id
                  from mm_writeout_to t, mm_paymentin_events_td p
                 where exists (select 1
                          from mm_paymentin_events_td m,
                               mm_paymentin_events_td m1
                         where m.newno = 1420467997
                           and m.fatherno = m1.listno
                           and m1.sonno = p.listno)
                   and t.businessno = p.newno);


Execution Plan
----------------------------------------------------------
Plan hash value: 393324829

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |                           |     1 |    21 |  4437   (1)| 00:00:54 |
|   1 |  UPDATE                          | MM_WRITEOUTSTATUS_TO      |       |       |            |          |
|*  2 |   FILTER                         |                           |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID   | MM_WRITEOUTSTATUS_TO      |   789 | 16569 |    96   (0)| 00:00:02 |
|*  4 |     INDEX RANGE SCAN             | IDX_WRITEOUTSTATUS_TEST   |   789 |       |     6   (0)| 00:00:01 |
|   5 |    NESTED LOOPS                  |                           |     1 |    52 |    11   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                 |                           |     1 |    38 |     9   (0)| 00:00:01 |
|   7 |      NESTED LOOPS                |                           |     1 |    27 |     7   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID| MM_WRITEOUT_TO            |     1 |    18 |     3   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | PK_MM_WRITEOUT_TO         |     1 |       |     2   (0)| 00:00:01 |
|* 10 |       TABLE ACCESS BY INDEX ROWID| MM_PAYMENTIN_EVENTS_TD    |     1 |     9 |     4   (0)| 00:00:01 |
|* 11 |        INDEX RANGE SCAN          | IDX_PAYMENTINE_08         |     4 |       |     2   (0)| 00:00:01 |
|* 12 |      TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD    |     1 |    11 |     2   (0)| 00:00:01 |
|* 13 |       INDEX UNIQUE SCAN          | PK_MM_PAYMENTIN_EVENTS_TD |     1 |       |     1   (0)| 00:00:01 |
|* 14 |     TABLE ACCESS BY INDEX ROWID  | MM_PAYMENTIN_EVENTS_TD    |     1 |    14 |     2   (0)| 00:00:01 |
|* 15 |      INDEX UNIQUE SCAN           | PK_MM_PAYMENTIN_EVENTS_TD |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( EXISTS (SELECT 0 FROM "MM_PAYMENTIN_EVENTS_TD" "P","MM_WRITEOUT_TO"
              "T","MM_PAYMENTIN_EVENTS_TD" "M1","MM_PAYMENTIN_EVENTS_TD" "M" WHERE "M"."NEWNO"=1420467997 AND
              "M"."FATHERNO" IS NOT NULL AND "M"."FATHERNO"="M1"."LISTNO" AND "M1"."SONNO" IS NOT NULL AND
              "T"."ID"=:B1 AND "M1"."SONNO"="P"."LISTNO" AND "P"."NEWNO"=TO_NUMBER("T"."BUSINESSNO")))
   4 - access("S"."STATUS"='0Z')
   9 - access("T"."ID"=:B1)
  10 - filter("M"."FATHERNO" IS NOT NULL)
  11 - access("M"."NEWNO"=1420467997)
  12 - filter("M1"."SONNO" IS NOT NULL)
  13 - access("M"."FATHERNO"="M1"."LISTNO")
  14 - filter("P"."NEWNO"=TO_NUMBER("T"."BUSINESSNO"))
  15 - access("M1"."SONNO"="P"."LISTNO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1830312  consistent gets
        154  physical reads
          0  redo size
        830  bytes sent via SQL*Net to client
       1240  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

分析

执行计划中有走filter关键字，且有两个子级，我们都知道，走这种连接方式是非常耗费性能的，主表返回多少行，被驱动表就得被扫描多少次。
利用merge into 可以等价改写update语句。

优化后

merge into  mm_writeoutstatus_to s
using (select distinct t.id
                  from mm_writeout_to t, mm_paymentin_events_td p
                 where exists (select 1
                          from mm_paymentin_events_td m,
                               mm_paymentin_events_td m1
                         where m.newno = 1420467997
                           and m.fatherno = m1.listno
                           and m1.sonno = p.listno)
                   and t.businessno = p.newno)b
on (s.id = b.id)
when matched then
   update set s.status = '00' where s.status = '0Z'                


Execution Plan
----------------------------------------------------------
Plan hash value: 1386952490

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                      |                           |     1 |    59 |  9822   (1)| 00:01:58 |
|   1 |  MERGE                               | MM_WRITEOUTSTATUS_TO      |       |       |            |          |
|   2 |   VIEW                               |                           |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID       | MM_WRITEOUTSTATUS_TO      |     1 |    53 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |                           |     1 |    66 |  9822   (1)| 00:01:58 |
|   5 |      VIEW                            |                           |     1 |    13 |  9819   (1)| 00:01:58 |
|   6 |       SORT UNIQUE                    |                           |     1 |    52 |  9819   (1)| 00:01:58 |
|*  7 |        HASH JOIN                     |                           |     1 |    52 |  9818   (1)| 00:01:58 |
|   8 |         NESTED LOOPS                 |                           |     1 |    34 |     9   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                |                           |     1 |    20 |     7   (0)| 00:00:01 |
|* 10 |           TABLE ACCESS BY INDEX ROWID| MM_PAYMENTIN_EVENTS_TD    |     1 |     9 |     5   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN          | IDX_PAYMENTINE_08         |     4 |       |     3   (0)| 00:00:01 |
|* 12 |           TABLE ACCESS BY INDEX ROWID| MM_PAYMENTIN_EVENTS_TD    |     1 |    11 |     2   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN         | PK_MM_PAYMENTIN_EVENTS_TD |     1 |       |     1   (0)| 00:00:01 |
|  14 |          TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD    |     1 |    14 |     2   (0)| 00:00:01 |
|* 15 |           INDEX UNIQUE SCAN          | PK_MM_PAYMENTIN_EVENTS_TD |     1 |       |     1   (0)| 00:00:01 |
|  16 |         TABLE ACCESS FULL            | MM_WRITEOUT_TO            |  1124K|    19M|  9797   (1)| 00:01:58 |
|* 17 |      INDEX RANGE SCAN                | IDX_WRITEOUTSTATUS_1      |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("P"."NEWNO"=TO_NUMBER("T"."BUSINESSNO"))
  10 - filter("M"."FATHERNO" IS NOT NULL)
  11 - access("M"."NEWNO"=1420467997)
  12 - filter("M1"."SONNO" IS NOT NULL)
  13 - access("M"."FATHERNO"="M1"."LISTNO")
  15 - access("M1"."SONNO"="P"."LISTNO")
  17 - access("S"."ID"="B"."ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      54083  consistent gets
          0  physical reads
          0  redo size
        832  bytes sent via SQL*Net to client
       1281  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed   

------------------------------------------------------------------------------------------------------------------------------

优化前每次执行需要1830312 次逻辑读，优化后每次执行需要54083 次逻辑读，性能提升33.8倍