SQL调优之关注filter信息-CSDN博客

开发人员发来邮件，叫我优化下面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

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

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

| 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秒跑完。

看到这里明白怎么做SQL优化了没？我只要是看见一个人用工具看执行计划，我就知道他是个SQL调优菜鸟。