不懂業務的SQL優化方法
看著如下長長的SQL，對於其要實現的功能一無所知，開發人員需要根據領導的需要調整該SQL並匯出產生的資料，但每次執行都要5分鐘左右，對對於開發，除錯和領導確認都不能接受，開發人員直接將此SQL轉給我優化，由於此SQL並不寫入應用且手工執行頻率很高，於是最直接的方法就是先並行加快sql的執行速度
WITH T_MAST AS(
SELECT
T3.BASE_YW
,T2.USER_ID
,T2.USER_NM
,T2.BRNC_ID
,(SELECT SORG_NM FROM MCS_HQ.MA_SORG T WHERE T.SORG_ID = T2.BRNC_ID) BRNC_NM
,T2.OFFC_ID
,(SELECT SORG_NM FROM MCS_HQ.MA_SORG T WHERE T.SORG_ID = T2.OFFC_ID) OFFC_NM
,(SELECT CODE_NM FROM MCS_HQ.CD_CODE_LIST T WHERE T.CODE_DIV = 'USER_TP' AND T.LANG_CD = 'CN' AND T.CODE_CD = T2.USER_TP) USER_TP_NM
,COUNT(*) LONG_CNT
from MCS_HQ.HI_USER_MENU_ACCS_LOG T1
,MCS_HQ.MA_USER T2
,MCS_HQ.MA_BASE_YMD T3
WHERE T1.MENU_ID IN ('M001164','M001126','M001125','M001124','M001123','M001163')
AND T1.USER_ID = T2.USER_ID
AND T1.ACCS_DT >= DATE '2014-12-29'
AND TO_CHAR(T1.ACCS_DT,'yyyymmDD') = T3.BASE_YMD
AND T2.USER_TP IN ('OFFC','SLMN','BRNC') --指定使用者
AND T2.USER_USE_ST = 'ACTV'
AND T2.USER_ID NOT IN ('chokyu','kimbk01') --('CHOKU','KIMBK01');
AND EXISTS(SELECT * FROM MCS_HQ.MA_USER_PROD A WHERE A.USER_PROD_REL_TP = 'MPRD' AND A.USE_YN = 'Y'
AND A.REL_TO_YMD = '99991231' AND A.PROD_CD IN ('GSM','LTE','CDMA','TABLET') AND A.USER_ID = T1.USER_ID)
AND EXISTS(
SELECT *
FROM MCS_HQ.MA_USER_ROLE A
,MCS_HQ.MA_ROLE_MENU B
WHERE A.USER_ID = T1.USER_ID
AND A.USE_YN = 'Y'
AND B.ROLE_ID = A.ROLE_ID
AND B.USE_YN = 'Y'
AND B.MENU_ID IN ('M001164','M001126','M001125','M001124','M001123','M001163')
)
AND T2.SUBS_ID = 'SCIC'
GROUP BY T3.BASE_YW
,T2.BRNC_ID
,T2.OFFC_ID
,T2.USER_ID
,T2.USER_NM
,T2.USER_TP
)
SELECT MAX(BRNC_NM) BRNC_NM
,MAX(OFFC_NM) OFFC_NM
,USER_ID USER_ID
,MAX(USER_NM) USER_NM
,USER_TP_NM
,sum(DECODE(BASE_YW,'201501',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201502',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201503',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201504',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201505',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201506',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201507',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201508',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201509',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201510',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201511',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201512',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201513',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201514',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201515',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201516',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201517',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201518',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201519',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201520',LONG_CNT)) W20_LOGN_CNT
,sum(DECODE(BASE_YW,'201521',LONG_CNT)) W21_LOGN_CNT
,sum(DECODE(BASE_YW,'201522',LONG_CNT)) W22_LOGN_CNT
,sum(DECODE(BASE_YW,'201523',LONG_CNT)) W23_LOGN_CNT
,sum(DECODE(BASE_YW,'201524',LONG_CNT)) W24_LOGN_CNT
,sum(DECODE(BASE_YW,'201525',LONG_CNT)) W25_LOGN_CNT
FROM T_MAST
GROUP BY BRNC_ID
,OFFC_ID
,USER_ID
,USER_TP_NM
ORDER BY
BRNC_NM
,OFFC_NM
,USER_ID
,USER_TP_NM
要想盡快確認哪些表需要並行提高執行速度，先檢視執行計劃
SELECT STATEMENT, GOAL = FIRST_ROWS 48563 1 248
INDEX RANGE SCAN MCS_HQ UX_MA_SORG_3 1 1 16
INDEX RANGE SCAN MCS_HQ UX_MA_SORG_3 1 1 16
INDEX RANGE SCAN MCS_HQ UX_CD_CODE_LIST_1 2 1 34
SORT ORDER BY 48563 1 248
HASH GROUP BY 48563 1 248
VIEW MCS_HQ_READ 48561 1 248
HASH GROUP BY 48561 1 135
NESTED LOOPS SEMI 48560 1 135
NESTED LOOPS 48554 1 103
NESTED LOOPS 48552 1 56
HASH JOIN RIGHT SEMI 48551 1 40
VIEW SYS VW_SQ_1 481 37509 412599
HASH JOIN 481 37509 1912959
TABLE ACCESS FULL MCS_HQ MA_ROLE_MENU 58 145 3480 ("B"."MENU_ID"='M001123' OR "B"."MENU_ID"='M001124' OR "B"."MENU_ID"='M001125' OR "B"."MENU_ID"='M001126' OR "B"."MENU_ID"='M001163' OR "B"."MENU_ID"='M001164') AND "B"."USE_YN"='Y'
TABLE ACCESS FULL MCS_HQ MA_USER_ROLE 422 158708 4285116 "A"."USE_YN"='Y' AND "A"."USER_ID"<>'chokyu' AND "A"."USER_ID"<>'kimbk01'
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID MCS_HQ HI_USER_MENU_ACCS_LOG 48069 56598 1641342 "T1"."USER_ID"<>'chokyu' AND "T1"."USER_ID"<>'kimbk01'
INDEX RANGE SCAN MCS_HQ X_HI_USER_MENU_ACCS_LOG_1 658 57249
INDEX RANGE SCAN MCS_HQ UX_MA_BASE_YMD_1 1 1 16
TABLE ACCESS BY INDEX ROWID MCS_HQ MA_USER 2 1 47 "T2"."USER_USE_ST"='ACTV' AND ("T2"."USER_TP"='BRNC' OR "T2"."USER_TP"='OFFC' OR "T2"."USER_TP"='SLMN') AND "T2"."SUBS_ID"='SCIC'
INDEX UNIQUE SCAN MCS_HQ PK_MA_USER 1 1 "T2"."USER_ID"<>'chokyu' AND "T2"."USER_ID"<>'kimbk01'
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID MCS_HQ MA_USER_PROD 6 384534 12305088 "A"."USE_YN"='Y' AND "A"."REL_TO_YMD"='99991231'
INDEX UNIQUE SCAN MCS_HQ PK_MA_USER_PROD 5 1 "A"."USER_ID"<>'chokyu' AND "A"."USER_ID"<>'kimbk01'
從計劃中不難看出MA_ROLE_MENU，MA_USER_ROLE先做了全表掃描，然後又和HI_USER_MENU_ACCS_LOG做了 HASH JOIN RIGHT SEMI 連線，且做連線時消耗的成本極高，於是設法
在這兩部採用並行，提高執行效率，增加並行後的sql如下
WITH T_MAST AS(
SELECT /*+PARALLEL(T1,8)*/
T3.BASE_YW
,T2.USER_ID
,T2.USER_NM
,T2.BRNC_ID
,(SELECT SORG_NM FROM MCS_HQ.MA_SORG T WHERE T.SORG_ID = T2.BRNC_ID) BRNC_NM
,T2.OFFC_ID
,(SELECT SORG_NM FROM MCS_HQ.MA_SORG T WHERE T.SORG_ID = T2.OFFC_ID) OFFC_NM
,(SELECT CODE_NM FROM MCS_HQ.CD_CODE_LIST T WHERE T.CODE_DIV = 'USER_TP' AND T.LANG_CD = 'CN' AND T.CODE_CD = T2.USER_TP) USER_TP_NM
,COUNT(*) LONG_CNT
from MCS_HQ.HI_USER_MENU_ACCS_LOG T1
,MCS_HQ.MA_USER T2
,MCS_HQ.MA_BASE_YMD T3
WHERE T1.MENU_ID IN ('M001164','M001126','M001125','M001124','M001123','M001163')
AND T1.USER_ID = T2.USER_ID
AND T1.ACCS_DT >= DATE '2014-12-29'
AND TO_CHAR(T1.ACCS_DT,'yyyymmDD') = T3.BASE_YMD
AND T2.USER_TP IN ('OFFC','SLMN','BRNC') --三星使用者
AND T2.USER_USE_ST = 'ACTV'
AND T2.USER_ID NOT IN ('chokyu','kimbk01') --('CHOKU','KIMBK01');
AND EXISTS(SELECT * FROM MCS_HQ.MA_USER_PROD A WHERE A.USER_PROD_REL_TP = 'MPRD' AND A.USE_YN = 'Y'
AND A.REL_TO_YMD = '99991231' AND A.PROD_CD IN ('GSM','LTE','CDMA','TABLET') AND A.USER_ID = T1.USER_ID)
AND EXISTS(
SELECT /*+PARALLEL(A,8) PARALLEL(B,8)*/*
FROM MCS_HQ.MA_USER_ROLE A
,MCS_HQ.MA_ROLE_MENU B
WHERE A.USER_ID = T1.USER_ID
AND A.USE_YN = 'Y'
AND B.ROLE_ID = A.ROLE_ID
AND B.USE_YN = 'Y'
AND B.MENU_ID IN ('M001164','M001126','M001125','M001124','M001123','M001163')
)
AND T2.SUBS_ID = 'SCIC'
GROUP BY T3.BASE_YW
,T2.BRNC_ID
,T2.OFFC_ID
,T2.USER_ID
,T2.USER_NM
,T2.USER_TP
)
SELECT MAX(BRNC_NM) BRNC_NM
,MAX(OFFC_NM) OFFC_NM
,USER_ID USER_ID
,MAX(USER_NM) USER_NM
,USER_TP_NM
,sum(DECODE(BASE_YW,'201501',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201502',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201503',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201504',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201505',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201506',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201507',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201508',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201509',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201510',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201511',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201512',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201513',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201514',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201515',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201516',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201517',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201518',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201519',LONG_CNT)) W20_USER_CNT
,sum(DECODE(BASE_YW,'201520',LONG_CNT)) W20_LOGN_CNT
,sum(DECODE(BASE_YW,'201521',LONG_CNT)) W21_LOGN_CNT
,sum(DECODE(BASE_YW,'201522',LONG_CNT)) W22_LOGN_CNT
,sum(DECODE(BASE_YW,'201523',LONG_CNT)) W23_LOGN_CNT
,sum(DECODE(BASE_YW,'201524',LONG_CNT)) W24_LOGN_CNT
,sum(DECODE(BASE_YW,'201525',LONG_CNT)) W25_LOGN_CNT
FROM T_MAST
GROUP BY BRNC_ID
,OFFC_ID
,USER_ID
,USER_TP_NM
ORDER BY
BRNC_NM
,OFFC_NM
,USER_ID
,USER_TP_NM
這樣增加hint後的sql執行速度在10s內完成，基本適應頻率較高的手動執行和更改也能滿足領導及時檢查的需要
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-1704185/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Sql優化方法SQL優化
- SQL優化的方法論SQL優化
- SQL查詢優化的方法SQL優化
- SQL 語句的優化方法SQL優化
- SQL優化常用方法11SQL優化
- SQL優化常用方法10SQL優化
- SQL優化常用方法16SQL優化
- SQL優化常用方法2SQL優化
- SQL優化常用方法5SQL優化
- SQL優化常用方法8SQL優化
- SQL優化常用方法3SQL優化
- SQL優化常用方法19SQL優化
- SQL優化常用方法20SQL優化
- SQL優化常用方法18SQL優化
- SQL優化常用方法12SQL優化
- SQL優化常用方法46SQL優化
- SQL優化常用方法52SQL優化
- SQL優化常用方法33SQL優化
- SQL優化常用方法29SQL優化
- SQL優化常用方法26SQL優化
- SQL優化常用方法25SQL優化
- SQL優化常用方法39SQL優化
- SQL優化常用方法27SQL優化
- SQL優化常用方法22SQL優化
- SQL優化常用方法30SQL優化
- 你還不懂 Tomcat 的優化嗎？Tomcat優化
- 達夢SQL優化方法statSQL優化
- 50種方法優化SQL Server優化SQLServer
- Oracle SQL效能優化常用方法OracleSQL優化
- SQL語句優化方法用hint的30種方法SQL優化
- SQL查詢優化常見方法SQL優化
- SQL語句優化方法30例SQL優化
- MS SQL Server查詢優化方法SQLServer優化
- MS SQL Server查詢優化方法SQLServer優化
- 【SQL優化】SQL優化的10點注意事項SQL優化
- SQL語句優化的原則與方法QOSQL優化
- SQL語句優化方法30例(轉)SQL優化
- 【SQL優化】SQL優化工具SQL優化