首页 > 代码库 > no_merge优化MERGE JOIN CARTESIAN跑不出结果到1S

no_merge优化MERGE JOIN CARTESIAN跑不出结果到1S

开发说下面这个sql语句跑不出结果

SELECT
     t1.order_id,

     t2.order_name,

     t1.order_flow_no,

     t1.order_type,

     t1.agent_id,

     t1.money,

     t1.order_create_time

      FROM (SELECT  re.id AS order_id,

                   re.serialnumber AS order_flow_no,

                   re.money AS money,

                   ‘1‘ AS order_type,

                   re.agent_id AS agent_id,

                   re.create_date AS order_create_time

              FROM tb_recharge re

             WHERE create_date >=

                   to_date(‘2014-07-08 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) -

                   INTERVAL ‘1‘

             DAY

               AND create_date <

                   to_date(‘2014-07-09 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)

               AND re.status != 2) t1

      LEFT JOIN (SELECT  tt1.orderform_flow_no, tt1.order_name

                   FROM (SELECT tp1.PAYMENT_FLOW_NO,

                                tp1.orderform_flow_no,

                                tp1.orderform_name AS order_name

                           FROM tb_payment tp1

                          WHERE create_time >=

                                to_date(‘2014-07-08 00:00:00‘,

                                        ‘yyyy-mm-dd hh24:mi:ss‘) - INTERVAL ‘1‘

                          DAY

                            AND create_time <

                                to_date(‘2014-07-09 00:00:00‘,

                                        ‘yyyy-mm-dd hh24:mi:ss‘)

                            AND tp1.orderform_type = 1

                            AND tp1.id NOT IN

                                (SELECT tr.payment_id FROM tb_refund tr)) tt1

                   LEFT JOIN (SELECT 
                              business_flow_no

                               FROM tb_fund_flow f

                              WHERE f.business_type = 0

                                AND create_time >= to_date(‘2014-07-08 00:00:00‘,

                                                           ‘yyyy-mm-dd hh24:mi:ss‘) -

                                    INTERVAL ‘1‘ DAY

                                AND create_time <

                                    to_date(‘2014-07-09 00:00:00‘,

                                            ‘yyyy-mm-dd hh24:mi:ss‘)) tt2

                     ON tt1.PAYMENT_FLOW_NO = tt2.business_flow_no

                  WHERE tt2.business_flow_no IS NOT NULL) t2

        ON t1.order_flow_no = t2.orderform_flow_no

     WHERE t2.orderform_flow_no IS NOT NULL

 

plan如下

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |   |     1 |   440 |   158K (75)| 00:31:40 |  |  |
|*  1 |  HASH JOIN ANTI          |   |     1 |   440 |   158K (75)| 00:31:40 |  |  |
|   2 |   NESTED LOOPS          |   |     1 |   434 |   156K (75)| 00:31:23 |  |  |
|   3 |    NESTED LOOPS          |   |     1 |   434 |   156K (75)| 00:31:23 |  |  |
|   4 |     MERGE JOIN CARTESIAN        |   |     1 |   125 |   156K (75)| 00:31:23 |  |  |
|   5 |      PARTITION RANGE SINGLE        |   |     1 |    83 |     0   (0)| 00:00:01 |    12 |    12 |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| TB_RECHARGE  |     1 |    83 |     0   (0)| 00:00:01 |    12 |    12 |
|*  7 |        INDEX RANGE SCAN         | TB_RECHARGE_I5  |     1 |  |     0   (0)| 00:00:01 |    12 |    12 |
|   8 |      BUFFER SORT         |   |   673K|    26M|   156K (75)| 00:31:23 |  |  |
|   9 |       PARTITION RANGE SINGLE        |   |   673K|    26M|   156K (75)| 00:31:23 |     8 |     8 |
|* 10 |        TABLE ACCESS FULL        | TB_FUND_FLOW  |   673K|    26M|   156K (75)| 00:31:23 |     8 |     8 |
|* 11 |     INDEX UNIQUE SCAN         | UNIQ_FLOW_TYPE1 |     1 |  |     2   (0)| 00:00:01 |  |  |
|* 12 |    TABLE ACCESS BY GLOBAL INDEX ROWID  | TB_PAYMENT  |     1 |   309 |     2   (0)| 00:00:01 |     8 |     8 |
|  13 |   TABLE ACCESS FULL         | TB_REFUND  |   150K|   884K|  1402   (1)| 00:00:17 |  |  |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("TP1"."ID"="TR"."PAYMENT_ID")
   6 - filter("RE"."STATUS"<>2)
   7 - access("CREATE_DATE">=TO_DATE(‘ 2014-07-07 00:00:00‘, ‘syyyy-mm-dd hh24:mi:ss‘) AND
       "CREATE_DATE"<TO_DATE(‘ 2014-07-09 00:00:00‘, ‘syyyy-mm-dd hh24:mi:ss‘))
  10 - filter("F"."BUSINESS_TYPE"=0 AND "CREATE_TIME">=TO_DATE(‘ 2014-07-07 00:00:00‘, ‘syyyy-mm-dd hh24:mi:ss‘)
       AND "CREATE_TIME"<TO_DATE(‘ 2014-07-09 00:00:00‘, ‘syyyy-mm-dd hh24:mi:ss‘) AND "BUSINESS_FLOW_NO" IS NOT NULL)
  11 - access("RE"."SERIALNUMBER"="TP1"."ORDERFORM_FLOW_NO" AND "TP1"."ORDERFORM_TYPE"=1)
       filter("TP1"."ORDERFORM_FLOW_NO" IS NOT NULL)
  12 - filter("CREATE_TIME">=TO_DATE(‘ 2014-07-07 00:00:00‘, ‘syyyy-mm-dd hh24:mi:ss‘) AND
       "CREATE_TIME"<TO_DATE(‘ 2014-07-09 00:00:00‘, ‘syyyy-mm-dd hh24:mi:ss‘) AND
       "TP1"."PAYMENT_FLOW_NO"="BUSINESS_FLOW_NO")

 

 

相关表大小说明:

tb_recharge: 6G

tb_fund_flow: 9G

tb_payment: 4G

都为分区表

 

这个执行计划一眼就可以看出很多表统计信息有问题,先不管统计信息是否正确,定位到id为4 中tb_recharge 和tb_fund_folw  两个大表做MERGE JOIN CARTESIAN   笛卡尔,并且tb_found_flow  TABLE FULL SCAN, 发现问题的时候这个sql已经跑了3个小时了。

注意上面sql语句的写法,都是将大表先过滤在相互去join,说明一点这个sql最终join结果只有2条数据。但是最终CBO查询改写了,导致内敛视图合并,出现笛卡尔。看到这里优化就非常简单了,于是我加了一个hint no_merge不允许视图合并。

下面是最后的结果,1s数据就出来了

SELECT

    /*+ no_merge(t1) no_merge(t2)*/

     t1.order_id,

     t2.order_name,

     t1.order_flow_no,

     t1.order_type,

     t1.agent_id,

     t1.money,

     t1.order_create_time

      FROM (SELECT re.id AS order_id,

                   re.serialnumber AS order_flow_no,

                   re.money AS money,

                   ‘1‘ AS order_type,

                   re.agent_id AS agent_id,

                   re.create_date AS order_create_time

              FROM tb_recharge re

             WHERE create_date >=

                   to_date(‘2014-07-08 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) -

                   INTERVAL ‘1‘

             DAY

               AND create_date <

                   to_date(‘2014-07-09 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)

               AND re.status != 2) t1

      LEFT JOIN (SELECT  tt1.orderform_flow_no, tt1.order_name

                   FROM (SELECT tp1.PAYMENT_FLOW_NO,

                                tp1.orderform_flow_no,

                                tp1.orderform_name AS order_name

                           FROM tb_payment tp1

                          WHERE create_time >=

                                to_date(‘2014-07-08 00:00:00‘,

                                        ‘yyyy-mm-dd hh24:mi:ss‘) - INTERVAL ‘1‘

                          DAY

                            AND create_time <

                                to_date(‘2014-07-09 00:00:00‘,

                                        ‘yyyy-mm-dd hh24:mi:ss‘)

                            AND tp1.orderform_type = 1

                            AND tp1.id NOT IN

                                (SELECT tr.payment_id FROM tb_refund tr)) tt1

                   LEFT JOIN (SELECT /*+ index(f IDX_FUNDFLOW_CRTTIME) */

                              business_flow_no

                               FROM tb_fund_flow f

                              WHERE f.business_type = 0

                                AND create_time >= to_date(‘2014-07-08 00:00:00‘,

                                                           ‘yyyy-mm-dd hh24:mi:ss‘) -

                                    INTERVAL ‘1‘ DAY

                                AND create_time <

                                    to_date(‘2014-07-09 00:00:00‘,

                                            ‘yyyy-mm-dd hh24:mi:ss‘)) tt2

                     ON tt1.PAYMENT_FLOW_NO = tt2.business_flow_no

                  WHERE tt2.business_flow_no IS NOT NULL) t2

        ON t1.order_flow_no = t2.orderform_flow_no

     WHERE t2.orderform_flow_no IS NOT NULL

 

注意由于tb_fund_flow 是按月分区表,这里查询一天的数据,必然走index比较快,可见统计信息错误,由于系统所有sql语句都在1秒内出结果,我没有去收集统计信息,于是加了这个hint  /*+ index(f IDX_FUNDFLOW_CRTTIME) */ ,下面是最终的执行计划

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

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT         |        |     1 |   327 |       |   415K (1)| 01:23:07 |       |       |

|*  1 |  HASH JOIN          |        |     1 |   327 |       |   415K (1)| 01:23:07 |       |       |

|   2 |   PARTITION RANGE SINGLE        |        |     1 |    73 |       |     0 (0)| 00:00:01 |    12 |    12 |

|   3 |    VIEW           |        |     1 |    73 |       |     0 (0)| 00:00:01 |       |       |

|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID  | TB_RECHARGE       |     1 |    83 |       |     0 (0)| 00:00:01 |    12 |    12 |

|*  5 |      INDEX RANGE SCAN         | TB_RECHARGE_I5       |     1 |       |       |     0 (0)| 00:00:01 |    12 |    12 |

|   6 |   VIEW           |        |  6740 |  1671K|       |   415K (1)| 01:23:07 |       |       |

|*  7 |    HASH JOIN          |        |  6740 |  2349K|       |   415K (1)| 01:23:07 |       |       |

|*  8 |     HASH JOIN RIGHT ANTI        |        |   990 |   304K|  2656K| 41160 (1)| 00:08:14 |       |       |

|   9 |      TABLE ACCESS FULL         | TB_REFUND       |   150K|   884K|       |  1402 (1)| 00:00:17 |       |       |

|  10 |      PARTITION RANGE SINGLE        |        | 98977 |    29M|       | 38125 (1)| 00:07:38 |     8 |     8 |

|* 11 |       TABLE ACCESS BY LOCAL INDEX ROWID| TB_PAYMENT       | 98977 |    29M|       | 38125 (1)| 00:07:38 |     8 |     8 |

|* 12 |        INDEX RANGE SCAN         | IDX_PAYMENT_CRTTIME  | 98977 |       |       |  1550 (1)| 00:00:19 |     8 |     8 |

|  13 |     PARTITION RANGE SINGLE        |        |   673K|    26M|       |   374K (1)| 01:14:53 |     8 |     8 |

|* 14 |      TABLE ACCESS BY LOCAL INDEX ROWID | TB_FUND_FLOW       |   673K|    26M|       |   374K (1)| 01:14:53 |     8 |     8 |

|* 15 |       INDEX RANGE SCAN         | IDX_FUNDFLOW_CRTTIME |   697K|       |       | 12504 (1)| 00:02:31 |     8 |     8 |

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

 添加了no_merge Hint后可以看到id等于3和6出现了view关键字,达到了最后sql写法的目的,先过滤大数据在join,最后这个sql 1s出了结果。开发傻眼了i_f30.gif

本文出自 “专注于Oracle性能调优” 博客,谢绝转载!