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;

   3731336

sql> 

脚本的执行计划如下：

sql> merge  into eds.tw_dp_b_tdterm_imei_day dm                   

                      dm.gsm_flux        =  temp.gsm_flux             ,          

                      dm.td_flux         =  temp.td_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                   

                        dm.city_id      = temp.city_id          and                

                      dm.gsm_flux        =  temp.gsm_flux               ,          

                      dm.imei                                        ,             

                      dm.buss_city_id                                ,             

                      dm.gsm_flux                                    ,             

                      temp.imei                                      ,             

                      temp.buss_city_id                              ,             

  ;     

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 |

   6 - access("dm"."usr_nbr"(+)="temp"."usr_nbr" and "dm"."imei"(+)="temp"."imei" and

       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")

       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

         11  sorts (memory)

sql> exit