MERGE INTO 性能问题疑问

简介:

今天同事碰到一个SQL的性能问题,主要是MERGE INTO的性能问题,执行脚本的时候,居然耗时50多分钟,汗!简直让人抓狂,脚本如下:


MERGE  INTO EDS.TW_DP_B_TDTERM_IMEI_DAY DM                   
        USING T_IMEI_DAY_1111 TEMP                                                 
                    ON(                                                            
                        DM.DATE_CD      =  TEMP.DATE_CD          AND               
                        DM.CITY_ID      = TEMP.CITY_ID           AND                
                        DM.IMEI          =  TEMP.IMEI AND                          
                        DM.USR_NBR      =  TEMP.USR_NBR                            
                      )                                                            
  WHEN MATCHED THEN  UPDATE SET                                                    
                      DM.GSM_FLUX        =  TEMP.GSM_FLUX           ,          
                      DM.TD_FLUX         =  TEMP.TD_FLUX            ,            
                      DM.GPRS_FLUX       =  TEMP.GPRS_FLUX                         
  WHEN NOT MATCHED THEN INSERT(                                                    
                      DM.DATE_CD                                    ,              
                      DM.CITY_ID                                    ,              
                      DM.IMEI                                       ,             
                      DM.BUSS_CITY_ID                               ,             
                      DM.TYPE_ID                                    ,              
                      DM.USR_NBR                                    ,              
                      DM.GSM_FLUX                                   ,             
                      DM.TD_FLUX                                    ,              
                      DM.GPRS_FLUX                                                 
                      )                                                            
                VALUES(                                                            
                      TEMP.DATE_CD                                  ,              
                      TEMP.CITY_ID                                  ,              
                      TEMP.IMEI                                     ,             
                      TEMP.BUSS_CITY_ID                             ,             
                      TEMP.TYPE_ID                                  ,              
                      TEMP.USR_NBR                                  ,              
                      TEMP.GSM_FLUX                                 ,              
                      TEMP.TD_FLUX                                  ,              
                      TEMP.GPRS_FLUX                                               
                      );  


这 两个表的数据量大致如下: T_IMEI_DAY_1111  三十多万,  EDS.TW_DP_B_TDTERM_IMEI_DAY  三百多万数据,跑数前都做过表的相关信息 收集。 EDS.TW_DP_B_TDTERM_IMEI_DAY表的索引有效,碎片很少:


SQL> select count(1) from T_IMEI_DAY_1111;


  COUNT(1)
----------
    333575


SQL> select count(1) from EDS.TW_DP_B_TDTERM_IMEI_DAY;


  COUNT(1)
----------
   3731336


SQL> 


脚本的执行计划如下:
SQL> MERGE  INTO EDS.TW_DP_B_TDTERM_IMEI_DAY DM                   
        USING T_IMEI_DAY_1111 TEMP                                                 
                    ON(                                                            
                        DM.DATE_CD      =  TEMP.DATE_CD          AND               
                        DM.CITY_ID      = TEMP.CITY_ID           AND                
                        DM.IMEI          =  TEMP.IMEI AND                          
                        DM.USR_NBR      =  TEMP.USR_NBR                            
                      )                                                            
  WHEN MATCHED THEN  UPDATE SET                                                    
                      DM.GSM_FLUX        =  TEMP.GSM_FLUX             ,          
                      DM.TD_FLUX         =  TEMP.TD_FLUX              ,            
                      DM.GPRS_FLUX       =  TEMP.GPRS_FLUX                         
  WHEN NOT MATCHED THEN INSERT(                                                    
                      DM.DATE_CD                                    ,              
                      DM.CITY_ID                                    ,              
                      DM.IMEI                                       ,             
                      DM.BUSS_CITY_ID                               ,             
                      DM.TYPE_ID                                    ,              
                      DM.USR_NBR                                    ,              
                      DM.GSM_FLUX                                   ,             
                      DM.TD_FLUX                                    ,              
                      DM.GPRS_FLUX                                                 
                      )                                                            
                VALUES(                                                            
                      TEMP.DATE_CD                                  ,              
                      TEMP.CITY_ID                                  ,              
                      TEMP.IMEI                                     ,             
                      TEMP.BUSS_CITY_ID                             ,             
                      TEMP.TYPE_ID                                  ,              
                      TEMP.USR_NBR                                  ,              
                      TEMP.GSM_FLUX                                 ,              
                      TEMP.TD_FLUX                                  ,              
                      TEMP.GPRS_FLUX                                               
                      );    




333575 rows merged.




Execution Plan
----------------------------------------------------------
Plan hash value: 3661285687


--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |                         |   264K|    54M|       |  3576   (2)| 00:01:20 |       |       |
|   1 |  MERGE                 | TW_DP_B_TDTERM_IMEI_DAY |       |       |       |            |          |       |       |
|   2 |   VIEW                 |                         |       |       |       |            |          |       |       |
|*  3 |    HASH JOIN OUTER     |                         |   264K|    44M|    31M|  3576   (2)| 00:01:20 |       |       |
|   4 |     TABLE ACCESS FULL  | T_IMEI_DAY_1111         |   264K|    28M|       |   100   (2)| 00:00:03 |       |       |
|   5 |     PARTITION RANGE ALL|                         |  2128K|   125M|       |   765   (7)| 00:00:17 |     1 |   365 |
|   6 |      TABLE ACCESS FULL | TW_DP_B_TDTERM_IMEI_DAY |  2128K|   125M|       |   765   (7)| 00:00:17 |     1 |   365 |
--------------------------------------------------------------------------------------------------------------------------


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


   3 - access("DM"."USR_NBR"(+)="TEMP"."USR_NBR" AND "DM"."IMEI"(+)="TEMP"."IMEI" AND
              "DM"."CITY_ID"(+)="TEMP"."CITY_ID" AND "DM"."DATE_CD"(+)="TEMP"."DATE_CD")


Note
-----
   - dynamic sampling used for this statement




Statistics
----------------------------------------------------------
       9975  recursive calls
    1212324  db block gets
     111135  consistent gets
       2447  physical reads
  228686840  redo size
        822  bytes sent via SQL*Net to client
       2571  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     333575  rows processed


SQL>


在优化脚本期间,我添加了USE_MERGE提示,结果执行脚本的时间一下子缩短到几十秒。但是让我很纳闷的是, 无论怎么看,添加USE_MERGE提示的脚本的执行计划怎么比较都比上面没添加提示的脚本的执行计划查(执行计划如下所示)
SQL> MERGE /*+ USE_MERGE(DM TEMP) */ INTO EDS.TW_DP_B_TDTERM_IMEI_DAY DM                   
        USING T_IMEI_DAY_1111 TEMP                                                 
                    ON(                                                            
                        DM.DATE_CD      =  TEMP.DATE_CD          AND               
                        DM.CITY_ID      = TEMP.CITY_ID          AND                
                        DM.IMEI          =  TEMP.IMEI AND                          
                        DM.USR_NBR      =  TEMP.USR_NBR                            
                      )                                                            
  WHEN MATCHED THEN  UPDATE SET                                                    
                      DM.GSM_FLUX        =  TEMP.GSM_FLUX               ,          
                      DM.TD_FLUX         =  TEMP.TD_FLUX              ,            
                      DM.GPRS_FLUX       =  TEMP.GPRS_FLUX                         
  WHEN NOT MATCHED THEN INSERT(                                                    
                      DM.DATE_CD                                    ,              
                      DM.CITY_ID                                    ,              
                      DM.IMEI                                        ,             
                      DM.BUSS_CITY_ID                                ,             
                      DM.TYPE_ID                                    ,              
                      DM.USR_NBR                                    ,              
                      DM.GSM_FLUX                                    ,             
                      DM.TD_FLUX                                    ,              
                      DM.GPRS_FLUX                                                 
                      )                                                            
                VALUES(                                                            
                      TEMP.DATE_CD                                  ,              
                      TEMP.CITY_ID                                  ,              
                      TEMP.IMEI                                      ,             
                      TEMP.BUSS_CITY_ID                              ,             
                      TEMP.TYPE_ID                                  ,              
                      TEMP.USR_NBR                                  ,              
                      TEMP.GSM_FLUX                                 ,              
                      TEMP.TD_FLUX                                  ,              
                      TEMP.GPRS_FLUX                                               
                      )                                                            
  ;     


333575 rows merged.




Execution Plan
----------------------------------------------------------
Plan hash value: 2993229602


---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |                         |  1688K|   346M|       | 14060   (1)| 00:05:13 |       |       |
|   1 |  MERGE                  | TW_DP_B_TDTERM_IMEI_DAY |       |       |       |            |          |       |       |
|   2 |   VIEW                  |                         |       |       |       |            |          |       |       |
|   3 |    MERGE JOIN OUTER     |                         |  1688K|   283M|       | 14060   (1)| 00:05:13 |       |       |
|   4 |     SORT JOIN           |                         |   264K|    28M|    68M|  2424   (1)| 00:00:54 |       |       |
|   5 |      TABLE ACCESS FULL  | T_IMEI_DAY_1111         |   264K|    28M|       |   100   (2)| 00:00:03 |       |       |
|*  6 |     SORT JOIN           |                         |  2128K|   125M|   586M| 11636   (2)| 00:04:19 |       |       |
|   7 |      PARTITION RANGE ALL|                         |  2128K|   125M|       |   765   (7)| 00:00:17 |     1 |   365 |
|   8 |       TABLE ACCESS FULL | TW_DP_B_TDTERM_IMEI_DAY |  2128K|   125M|       |   765   (7)| 00:00:17 |     1 |   365 |
---------------------------------------------------------------------------------------------------------------------------


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


   6 - access("DM"."USR_NBR"(+)="TEMP"."USR_NBR" AND "DM"."IMEI"(+)="TEMP"."IMEI" AND
              "DM"."CITY_ID"(+)="TEMP"."CITY_ID" AND "DM"."DATE_CD"(+)="TEMP"."DATE_CD")
       filter("DM"."DATE_CD"(+)="TEMP"."DATE_CD" AND "DM"."CITY_ID"(+)="TEMP"."CITY_ID" AND
              "DM"."IMEI"(+)="TEMP"."IMEI" AND "DM"."USR_NBR"(+)="TEMP"."USR_NBR")


Note
-----
   - dynamic sampling used for this statement




Statistics
----------------------------------------------------------
       1884  recursive calls
    1217983  db block gets
      90427  consistent gets
        654  physical reads
  259129380  redo size
        814  bytes sent via SQL*Net to client
       2667  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
     333575  rows processed


SQL> exit

相关文章
|
9天前
|
安全 Java 5G
从源码角度剖析 Elasticserach 段合并调优策略
从源码角度剖析 Elasticserach 段合并调优策略
15 0
|
9天前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
7月前
|
存储 SQL 关系型数据库
MySQL 优化 index merge(索引合并)引起的死锁分析(强烈推荐)
生产环境出现死锁流水,通过查看死锁日志,看到造成死锁的是两条一样的update语句(只有where条件中的值不同),如下:
|
12月前
|
自然语言处理 前端开发 UED
🌈 关于我给dumi2.0提pr的完整记录
博主最近一年时间在工作业余都在写开源组件库 concis ,其中文档站点生成框架采取了 dumi,前几天不久dumi2.0正式发布,博主也是顺势而为直接把项目升级(dumi1 -> dumi2)
111 1
🌈 关于我给dumi2.0提pr的完整记录
|
SQL 存储 缓存
源码解读:semi join如何避免拉取大表数据?(一)
Hash join是解决复杂join的一个重要手段,但其无法避免拉取左右两端的数据到计算层进行计算,导致某些场景下执行效率不高。作为一种补充,bka join则可以利用OLTP数据库中的索引,通过join构造inner表的predicate命中表索引,在某些场景下有比较好的join效率。PolarDB-X是面向HTAP设计的分布式数据库,在复杂查询时也会重点考虑利用数据库的索引信息来提升join的查询效率,因此有了本文的semi bka join。
源码解读:semi join如何避免拉取大表数据?(一)
|
数据库 开发者 索引
merge 操作|学习笔记
快速学习 merge 操作
176 0
merge 操作|学习笔记
|
算法 关系型数据库 MySQL
索引合并机制详解
索引合并机制详解
629 0
索引合并机制详解
|
存储 监控 搜索推荐
我感受到的排序机制参考
假期重新把之前在新浪博客里面的文字梳理了下,搬到这里。这
134 0
|
SQL 监控 关系型数据库
index_merge导致死锁案例分析
一、死锁现象描述 1.1 基本环境信息 1、数据库版本以及隔离级别 mysql>select version(); +---------------------+ | version() | +---------------------+ | 5.
1069 0