不懂业务的SQL优化方法

2022-04-07 13:17:13

看着如下长长的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内完成，基本适应频率较高的手动执行和更改也能满足领导及时检查的需要

码农公寓

相关文章