- 掛牌上市企業
- 60秒人工響應
- 99.99%連通率
- 7*24h人工
- 故障100倍補償
SQL調優之關注filter信息 |
| 發布時間: 2012/8/24 17:32:03 |
|
開發人員發來郵件，叫我優化下面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
有人可能會說問題在于這里： 13 - filter("T2094771"."MEASR_SKID"<>2 AND "T2094771"."MEASR_SKID"<>1 AND
OK,測試一把
SQL> select BUS_UNIT_SKID,count(*) from OPT_HYPER_PLAN_FCT group by BUS_UNIT_SKID; BUS_UNIT_SKID COUNT(*)
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調優菜鳥。 本文出自：億恩科技【www.artduck.net】 |
0371-60135900
京公網安備41019702002023號
香港服務器租用
美國服務器租用
電信骨干機房
聯通骨干機房
