SQL调优之关注filter信息

开发人员发来邮件，叫我优化下面SQL

Hi Robinson,
-
 

Kindly need your help to check the below SQL for performance issue with Hyper plan detail report. Thanks in advance.

select avg(T2094771.MEASR_AMT) as c1,

     sum(T2094771.MEASR_YA_AMT) as c2,

     sum(T2094771.MEASR_CURR_AMT) as c3,

     sum(T2094771.MEASR_AMT) as c4,

     T2095164.ACCT_LONG_NAME as c5,

     T2095164.NAME as c6,

     T2095002.PROD_DESC as c7,

     T2093992.MTH_NAME as c8,

     T2093992.MTH_NUM as c9,

     T2094784.MEASR_NAME as c10,

     T2094784.MEASR_ORDR_SKID as c11,

     upper(T2095164.FUND_FRCST_MODEL_DESC) as c12

from

     ADWU_OPTIMA_WE11.OPT_MEASR_DIM T2094784,

     ADWU_OPTIMA_WE11.OPT_ACCT_FDIM T2095164 /* OPT_ACCT_PRMTN_FDIM */ ,

     ADWU_OPTIMA_WE11.OPT_PROD_BRAND_ASDN_DIM T2095002,

     ADWU_OPTIMA_WE11.OPT_BUS_UNIT_FDIM T2093691,

     ADWU_OPTIMA_WE11.OPT_CAL_MASTR_DIM T2093992 /* OPT_CAL_MASTR_DIM01 */ ,

     ADWU_OPTIMA_WE11.OPT_HYPER_PLAN_FCT T2094771

where  ( T2094771.MEASR_SKID = T2094784.MEASR_SKID

and T2094771.PROD_SKID = T2095002.BRAND_SKID

and T2094771.BUS_UNIT_SKID = T2095002.BUS_UNIT_SKID

and T2093691.BUS_UNIT_SKID = T2094771.BUS_UNIT_SKID

and T2093992.CAL_MASTR_SKID = T2094771.DATE_SKID

and T2094771.ACCT_SKID = T2095164.ACCT_SKID

and T2094771.BUS_UNIT_SKID = T2095164.BUS_UNIT_SKID

and T2093691.BUS_UNIT_NAME = 'Austria Retail'

and T2093992.FISC_YR_ABBR_NAME = 'FY10/11'

and T2094771.FY_DATE_SKID = T2095002.FY_DATE_SKID

and T2095002.PROD_LVL_DESC = 'Category'

and T2095164.ACCT_LONG_NAME = 'SPAR AT - 2000122510'

and T2094771.MEASR_SKID <> 1 and T2094771.MEASR_SKID <> 2

and T2094784.MEASR_SKID <> 1 and T2094784.MEASR_SKID <> 2

and (T2095002.PROD_DESC in ('Baby Wipes - 1000045671', 'Childrens Personal Care - 1101158952', 'Diapers - 1000043704', 'Dummy Category for Unknown Category - 0'))

and T2094784.MEASR_NAME <> 'D-NOS Index YA' )

group by T2093992.MTH_NAME, T2093992.MTH_NUM, T2094784.MEASR_ORDR_SKID, T2094784.MEASR_NAME, T2095002.PROD_DESC,

T2095164.NAME, T2095164.ACCT_LONG_NAME, upper(T2095164.FUND_FRCST_MODEL_DESC)

order by c8, c7, c5, c11, c10

------Run SQL on UAT-----UAT要跑2分44秒

508 rows selected.

Elapsed: 00:02:24.22 

On UAT, SQL can be finished in about 2minutes and 25 seconds.

-----Run SQL on Prod-----Prod要跑6分44秒

482 rows selected.

Elapsed: 00:06:44.27

上面的SQL是OBIEE报表使用的，我们无法更改代码，客户使用的肯定是PROD环境，这个SQL要跑6分44秒肯定是不能接受的，一般客户最多能等3分钟，也就是说要让SQL再3分钟以内出结果。SQL调优做多了，这种SQL真的没什么挑战性，也就看看执行计划，1分钟就能搞定的事情

我们来看一下执行计划：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1422339931

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

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

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

|   0 | SELECT STATEMENT                         |                         |     1 |   250 |   847  (58)| 00:00:04 |       |       |

|   1 |  SORT GROUP BY                           |                         |     1 |   250 |   847  (58)| 00:00:04 |       |       |

|   2 |   NESTED LOOPS                           |                         |       |       |         |     |       |       |

|   3 |    NESTED LOOPS                          |                         |     1 |   250 |   846  (58)| 00:00:04 |       |       |

|   4 |     NESTED LOOPS                         |                         |     1 |   220 |   845  (58)| 00:00:04 |       |       |

|   5 |      NESTED LOOPS                        |                         |     1 |   161 |   841  (58)| 00:00:04 |       |       |

|*  6 |       HASH JOIN                          |                         |    14 |  1778 |   827  (59)| 00:00:04 |       |       |

|   7 |        PARTITION LIST ALL                |                         |     4 |   280 |    19   (0)| 00:00:01 |     1 |    14 |

|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM           |     4 |   280 |    19   (0)| 00:00:01 |     1 |    14 |

|*  9 |          INDEX RANGE SCAN                | OPT_ACCT_FDIM_NX2       |     4 |       |    15   (0)| 00:00:01 |     1 |    14 |

|  10 |        NESTED LOOPS                      |                         |  2417K|   131M|   698  (54)| 00:00:03 |       |       |

|* 11 |         INDEX RANGE SCAN                 | OPT_BUS_UNIT_FDIM_UX2   |     1 |    19 |     1   (0)| 00:00:01 |       |       |

|  12 |         PARTITION LIST ITERATOR          |                         |  2417K|    87M|   697  (54)| 00:00:03 |   KEY |   KEY |

|* 13 |          TABLE ACCESS FULL               | OPT_HYPER_PLAN_FCT      |  2417K|    87M|   697  (54)| 00:00:03 |   KEY |   KEY |

|* 14 |       TABLE ACCESS BY INDEX ROWID        | OPT_MEASR_DIM           |     1 |    34 |     1   (0)| 00:00:01 |       |       |

|* 15 |        INDEX UNIQUE SCAN                 | OPT_MEASR_DIM_PK        |     1 |       |     0   (0)| 00:00:01 |       |       |

|  16 |      PARTITION LIST ITERATOR             |                         |     1 |    59 |     3  (34)| 00:00:01 |   KEY |   KEY |

|* 17 |       TABLE ACCESS FULL                  | OPT_PROD_BRAND_ASDN_DIM |     1 |    59 |     3  (34)| 00:00:01 |   KEY |   KEY |

|* 18 |     INDEX UNIQUE SCAN                    | OPT_CAL_MASTR_DIM_PK    |     1 |       |     0   (0)| 00:00:01 |       |       |

|* 19 |    TABLE ACCESS BY INDEX ROWID           | OPT_CAL_MASTR_DIM       |     1 |    30 |     1   (0)| 00:00:01 |       |       |

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

Predicate Information (identified by operation id):

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

   6 - access("T2094771"."BUS_UNIT_SKID"="T2095164"."BUS_UNIT_SKID" AND "T2094771"."ACCT_SKID"="T2095164"."ACCT_SKID")

   9 - access("T2095164"."ACCT_LONG_NAME"='SPAR AT - 2000122510')

  11 - access("T2093691"."BUS_UNIT_NAME"='Austria Retail')

  13 - filter("T2094771"."MEASR_SKID"<>2 AND "T2094771"."MEASR_SKID"<>1 AND

              "T2093691"."BUS_UNIT_SKID"="T2094771"."BUS_UNIT_SKID")

  14 - filter("T2094784"."MEASR_NAME"<>'D-NOS Index YA')

  15 - access("T2094771"."MEASR_SKID"="T2094784"."MEASR_SKID")

       filter("T2094784"."MEASR_SKID"<>1 AND "T2094784"."MEASR_SKID"<>2)

  17 - filter("T2095002"."PROD_LVL_DESC"='Category' AND ("T2095002"."PROD_DESC"='Baby Wipes - 1000045671' OR

              "T2095002"."PROD_DESC"='Childrens Personal Care - 1101158952' OR "T2095002"."PROD_DESC"='Diapers - 1000043704' OR

              "T2095002"."PROD_DESC"='Dummy Category for Unknown Category - 0') AND "T2094771"."PROD_SKID"="T2095002"."BRAND_SKID" AND

              "T2094771"."BUS_UNIT_SKID"="T2095002"."BUS_UNIT_SKID" AND "T2094771"."FY_DATE_SKID"="T2095002"."FY_DATE_SKID")

  18 - access("T2093992"."CAL_MASTR_SKID"="T2094771"."DATE_SKID")

  19 - filter("T2093992"."FISC_YR_ABBR_NAME"='FY10/11')

44 rows selected.

看出来了吗，问题在于

17 - filter("T2095002"."PROD_LVL_DESC"='Category' AND ("T2095002"."PROD_DESC"='Baby Wipes - 1000045671' OR
            "T2095002"."PROD_DESC"='Childrens Personal Care - 1101158952' OR "T2095002"."PROD_DESC"='Diapers - 1000043704' OR
            "T2095002"."PROD_DESC"='Dummy Category for Unknown Category - 0') AND "T2094771"."PROD_SKID"="T2095002"."BRAND_SKID" AND
            "T2094771"."BUS_UNIT_SKID"="T2095002"."BUS_UNIT_SKID" AND "T2094771"."FY_DATE_SKID"="T2095002"."FY_DATE_SKID")

有人可能会说问题在于这里：

13 - filter("T2094771"."MEASR_SKID"<>2 AND "T2094771"."MEASR_SKID"<>1 AND
            "T2093691"."BUS_UNIT_SKID"="T2094771"."BUS_UNIT_SKID")

OK,测试一把

SQL> select BUS_UNIT_SKID,count(*) from OPT_HYPER_PLAN_FCT group by BUS_UNIT_SKID;

BUS_UNIT_SKID   COUNT(*)
------------- ----------
        14346     629624
        14347    9283369
        14348    4405691
        14349    3811875
        14361     105556
        14362    2128613
        14363    1284530
        14364    6633483
        14365       4344
        14366      93072
        14369        324

11 rows selected.

所以不合适。

所以创建如下索引：

SQL> create index OPT_PROD_BRAND_ASDN_DIM_NX1 ON OPT_PROD_BRAND_ASDN_DIM(PROD_DESC,BRAND_SKID,FY_DATE_SKID,PROD_LVL_DESC,BUS_UNIT_SKID);

Index created.

Elapsed: 00:00:01.79

------RUN SQL ON PROD------

482 rows selected.

Elapsed: 00:00:24.50 -------- SQL能在24秒跑完。

服务器租用/服务器托管中国五强！虚拟主机域名注册顶级提供商！15年品质保障！--亿恩科技[ENKJ.COM]