SQL最佳化案例-從執行計劃定位SQL問題（三）

沃趣科技發表於2018-06-29
SQL

當SQL出現問題，能從執行計劃中快速的定位哪部分出現問題很重要，SQL文字如下（為保證客戶隱私，已經將註釋和文字部分去掉）：


點選(此處)摺疊或開啟

  1. SELECT /*+ index(i IDX_INVM_BEC)*/ RQ,JGM,BZ,CUSTOMER_TYPE,
  2.   B.CUSTOMER_NO,
  3.   B.CUSTOMER_NAME AS DKHM,
  4.   B.ACCT_NO DKZH,
  5.   B.STATUS,
  6.   B.LOAN_BAL,
  7.   P,
  8.   LX,
  9.   NVL((SELECT A.ACCT_NO_DESC || A.ACCT_NO_ALL2
  10.          FROM INVM_ZMQ A
  11.         WHERE A.ACCT_NO=I.ACCT_NO
  12.           AND A.ZHLB='3'),I.ACCT_NO) AS CKZH,
  13.   I.CURR_VAL,
  14.   (CASE WHEN B.TRANSFER_ACCT=I.ACCT_NO THEN '嘻嘻嘻' ELSE '' END) AS SM
  15. FROM(
  16.   SELECT B.EXTDATE AS RQ,
  17.          B.BRANCH_NO AS JGM,
  18.          B.CURRENCY AS BZ,
  19.          C.CUSTOMER_TYPE,
  20.          B.CUSTOMER_NO,
  21.          C.CUSTOMER_NAME,
  22.          B.ACCT_NO,
  23.          '啊啊' AS STATUS,
  24.          B.LOAN_BAL,
  25.          B.UNPD_PRIN_BAL AS P,
  26.          ROUND(B.CAP_UNPD_INT,2)+
  27.          (CASE WHEN B.REPAY_SCHED IN ('M','G') OR L.REPAY_SCHED IN ('M','G') THEN 0
  28.                ELSE (CASE WHEN L.PIA_CAPN_FREQ='S' AND L.PAST_DUE_CAPN_FREQ='S'
  29.                           THEN ROUND(B.THEO_UNPD_ARR_PRN,2)
  30.                                +(CASE WHEN L.FINE1_COMPD_OPT='Y' THEN ROUND(B.THEO_UNPD_ARRS_INT,2) ELSE 0 END)
  31.                                +(CASE WHEN L.FINE2_COMPD_OPT='Y' THEN ROUND(B.THEO_UNPD_INT_ARR,2) ELSE 0 END)
  32.                           ELSE 0
  33.                      END)
  34.           END) AS LX,
  35.           B.TRF_ACCT_NO AS TRANSFER_ACCT
  36.     FROM BORM PARTITION("BORM_2018-06-13") B
  37.    INNER JOIN LONP L ON L.ACCT_TYPE=B.ACCT_TYPE AND L.INT_CAT=B.INT_CAT
  38.    INNER JOIN JGDY J ON B.BRANCH_NO = J.JGM AND (J.JGM='1700' OR J.SJJGM='1700')
  39.     LEFT JOIN CB_ACCT C ON B.ACCT_NO = C.ACCT_NO AND C.SYS_ID = 'BOR'
  40.    WHERE B.BAD_DEBT_IND in ('02','52')
  41.      AND B.STAT<>'40'
  42.  ) B
  43. LEFT JOIN INVM PARTITION("INVM_2018-06-13") I
  44.     ON B.CUSTOMER_NO=I.CUSTOMER_NO
  45.    AND I.ACCT_DESC='S'
  46.    AND I.CURR_VAL<>0
  47.    AND I.BRANCH_NO IN (SELECT JGM FROM JGDY WHERE JGM='1700' OR SJJGM='1700')
  48.    AND I.EXTDATE = DATE'2018-06-13'
  49.    AND I.CURR_STATUS='00'
  50. WHERE (B.P>0 OR B.LX>0.01);

執行計劃如下：





可以一眼定位到view部分導致整個執行緩慢，那麼我們仔細分析下view部分是怎麼執行的。INVM TABLE ACCESS BY LOCAL INDEX ROWID執行11分鐘，總計13分鐘執行完。

尋找view部分執行計劃的入口，ID18和ID19做nested loop，返回結果17與21做NESTED LOOP，可以得知最先執行的是ID18，ID18走的iffs，且A-rows返回記錄6256行資料，檢視ID18謂詞資訊

18 - filter(("SJJGM"='1700' OR "JGM"='1700')) 從這部分再回到SQL文字尋找SQL程式碼是AND I.BRANCH_NO IN (SELECT JGM FROM JGDY WHERE JGM='1700' OR SJJGM='1700')也就是【SELECT JGM FROM JGDY WHERE JGM='1700' OR SJJGM='1700'】，難道這部分返回結果真的是6256行資料嗎？帶著疑問我查詢了一下。



居然只返回了34行資料，為什麼會這樣子？

肯定是此處的JGDY_IDX3有什麼問題，那麼會有什麼問題呢？往上看ID為8的JGDY_IDX3正確的返回了34行資料，又仔細看了下ID為18的JGDY_IDX3，starts184次，正好6256/184=34，那麼原因找到了，正是因為ID4和ID15做NESTED LOOP，導致檢視裡面所有的部分都要多執行184次。按照上面的分析思路看ID4裡面的執行計劃都很正確，但是返回結果184行記錄且ID4和ID15做nested loop，導致整個view部分緩慢。




那麼就很好辦了，ID4和ID15應該走hash join，檢視outline data資訊，還沒辦法使用db_name資訊引導執行計劃走hash join，那麼只能改寫SQL。



改寫SQL如下：


點選(此處)摺疊或開啟

  1. SELECT RQ,JGM,BZ,CUSTOMER_TYPE,
  2.   B.CUSTOMER_NO,
  3.   B.CUSTOMER_NAME AS DKHM,
  4.   B.ACCT_NO DKZH,
  5.   B.STATUS,
  6.   B.LOAN_BAL,
  7.   P,
  8.   LX,
  9.   NVL((SELECT A.ACCT_NO_DESC || A.ACCT_NO_ALL2
  10.          FROM INVM_ZMQ A
  11.         WHERE A.ACCT_NO=C.ACCT_NO
  12.           AND A.ZHLB='3'),C.ACCT_NO) AS CKZH,
  13.   C.CURR_VAL,
  14.   (CASE WHEN B.TRANSFER_ACCT=C.ACCT_NO THEN '嘻嘻嘻' ELSE '' END) AS SM
  15. FROM(
  16.   SELECT B.EXTDATE AS RQ,
  17.          B.BRANCH_NO AS JGM,
  18.          B.CURRENCY AS BZ,
  19.          C.CUSTOMER_TYPE,
  20.          B.CUSTOMER_NO,
  21.          C.CUSTOMER_NAME,
  22.          B.ACCT_NO,
  23.          '啊啊' AS STATUS,
  24.          B.LOAN_BAL,
  25.          B.UNPD_PRIN_BAL AS P,
  26.          ROUND(B.CAP_UNPD_INT,2)+
  27.          (CASE WHEN B.REPAY_SCHED IN ('M','G') OR L.REPAY_SCHED IN ('M','G') THEN 0
  28.                ELSE (CASE WHEN L.PIA_CAPN_FREQ='S' AND L.PAST_DUE_CAPN_FREQ='S'
  29.                           THEN ROUND(B.THEO_UNPD_ARR_PRN,2)
  30.                                +(CASE WHEN L.FINE1_COMPD_OPT='Y' THEN ROUND(B.THEO_UNPD_ARRS_INT,2) ELSE 0 END)
  31.                                +(CASE WHEN L.FINE2_COMPD_OPT='Y' THEN ROUND(B.THEO_UNPD_INT_ARR,2) ELSE 0 END)
  32.                           ELSE 0
  33.                      END)
  34.           END) AS LX,
  35.           B.TRF_ACCT_NO AS TRANSFER_ACCT
  36.     FROM BORM PARTITION("BORM_2018-06-13") B
  37.    INNER JOIN LONP L ON L.ACCT_TYPE=B.ACCT_TYPE AND L.INT_CAT=B.INT_CAT
  38.    INNER JOIN JGDY J ON B.BRANCH_NO = J.JGM AND (J.JGM='1700' OR J.SJJGM='1700')
  39.     LEFT JOIN CB_ACCT C ON B.ACCT_NO = C.ACCT_NO AND C.SYS_ID = 'BOR'
  40.    WHERE B.BAD_DEBT_IND in ('02','52')
  41.      AND B.STAT<>'40'
  42.  ) B
  43. LEFT JOIN (SELECT /*+ index(I IDX_INVM_BEC) */ CUSTOMER_NO,ACCT_NO,CURR_VAL FROM
  44.        INVM PARTITION("INVM_2018-06-13") I
  45. INNER JOIN JGDY ON JGDY.JGM=I.BRANCH_NO AND (JGM='1700' OR SJJGM='1700')
  46.    AND I.ACCT_DESC='S'
  47.    AND I.CURR_VAL<>0
  48.    AND I.EXTDATE = DATE'2018-06-13'
  49.    AND I.CURR_STATUS='00') C ON B.CUSTOMER_NO=C.CUSTOMER_NO
  50. WHERE (B.P>0 OR B.LX>0.01);



改寫完之後SQL由13分鐘變為5秒鐘執行完，看ID19還要執行999k次，查詢ID18謂詞資訊對應SQL如下，確實是要返回999k行資料。


點選(此處)摺疊或開啟

  1. 10:59:23 report.QData>SELECT COUNT(*) FROM INVM PARTITION("INVM_2018-06-13") I WHERE I.BRANCH_NO IN (SELECT JGM FROM JGDY WHERE JGM='1700' or SJJGM='1700') AND I.EXTDATE=TO_DATE(' 2018-06-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND I.CURR_STATUS='00';
  2. COUNT(*)
  3. ----------
  4. 999424





|  作者簡介

姚崇·沃趣科技高階資料庫技術專家

熟悉Oracle資料庫內部機制，豐富的資料庫及RAC叢集層故障診斷、效能調優、OWI、資料庫備份恢復及遷移經驗。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2157025/，如需轉載，請註明出處，否則將追究法律責任。

相關文章