SQL优化案例-从执行计划定位SQL问题（三）

àì夳堔傛蜴生んèń 2022-05-21 04:12 262阅读 0赞

当SQL出现问题，能从执行计划中快速的定位哪部分出现问题很重要，SQL文本如下（为保证客户隐私，已经将注释和文字部分去掉）：

SELECT /*+ index(i IDX_INVM_BEC)*/ RQ,JGM,BZ,CUSTOMER_TYPE,
      B.CUSTOMER_NO,
      B.CUSTOMER_NAME AS DKHM,
      B.ACCT_NO DKZH,
      B.STATUS,
      B.LOAN_BAL,
      P,
      LX,
      NVL((SELECT A.ACCT_NO_DESC || A.ACCT_NO_ALL2 
             FROM INVM_ZMQ A 
            WHERE A.ACCT_NO=I.ACCT_NO 
              AND A.ZHLB='3'),I.ACCT_NO) AS CKZH,
      I.CURR_VAL,
      (CASE WHEN B.TRANSFER_ACCT=I.ACCT_NO THEN '嘻嘻嘻' ELSE '' END) AS SM
    FROM(
      SELECT B.EXTDATE       AS RQ,
             B.BRANCH_NO     AS JGM,
             B.CURRENCY      AS BZ,
             C.CUSTOMER_TYPE,
             B.CUSTOMER_NO,
             C.CUSTOMER_NAME,
             B.ACCT_NO,
             '啊啊'          AS STATUS,
             B.LOAN_BAL,
             B.UNPD_PRIN_BAL AS P,
             ROUND(B.CAP_UNPD_INT,2)+
             (CASE WHEN B.REPAY_SCHED IN ('M','G') OR L.REPAY_SCHED IN ('M','G') THEN 0
                   ELSE (CASE WHEN L.PIA_CAPN_FREQ='S' AND L.PAST_DUE_CAPN_FREQ='S' 
                              THEN ROUND(B.THEO_UNPD_ARR_PRN,2)
                                   +(CASE WHEN L.FINE1_COMPD_OPT='Y' THEN ROUND(B.THEO_UNPD_ARRS_INT,2) ELSE 0 END)
                                   +(CASE WHEN L.FINE2_COMPD_OPT='Y' THEN ROUND(B.THEO_UNPD_INT_ARR,2) ELSE 0 END)
                              ELSE 0
                         END)
              END) AS LX,
              B.TRF_ACCT_NO AS TRANSFER_ACCT
        FROM BORM PARTITION("BORM_2018-06-13") B
       INNER JOIN LONP L ON L.ACCT_TYPE=B.ACCT_TYPE AND L.INT_CAT=B.INT_CAT
       INNER JOIN JGDY J ON B.BRANCH_NO = J.JGM AND (J.JGM='1700' OR J.SJJGM='1700') 
        LEFT JOIN CB_ACCT C ON B.ACCT_NO = C.ACCT_NO AND C.SYS_ID = 'BOR'
       WHERE B.BAD_DEBT_IND in ('02','52') 
         AND B.STAT<>'40'
     ) B
    LEFT JOIN INVM PARTITION("INVM_2018-06-13") I 
        ON B.CUSTOMER_NO=I.CUSTOMER_NO 
       AND I.ACCT_DESC='S' 
       AND I.CURR_VAL<>0 
       AND I.BRANCH_NO IN (SELECT JGM FROM JGDY WHERE JGM='1700' OR SJJGM='1700') 
       AND I.EXTDATE = DATE'2018-06-13' 
       AND I.CURR_STATUS='00' 
    WHERE (B.P>0 OR B.LX>0.01);

执行计划如下：

![70][]

![70 1][]

可以一眼定位到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行数据吗？带着疑问我查询了一下。

![70 2][]

居然只返回了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部分缓慢。

![70 3][]

那么就很好办了，ID4和ID15应该走hash join，查看outline data信息，还没办法使用db\_name信息引导执行计划走hash join，那么只能改写SQL。

![70 4][]

改写SQL如下：

SELECT  RQ,JGM,BZ,CUSTOMER_TYPE,
      B.CUSTOMER_NO,
      B.CUSTOMER_NAME AS DKHM,
      B.ACCT_NO DKZH,
      B.STATUS,
      B.LOAN_BAL,
      P,
      LX,
      NVL((SELECT A.ACCT_NO_DESC || A.ACCT_NO_ALL2 
             FROM INVM_ZMQ A 
            WHERE A.ACCT_NO=C.ACCT_NO 
              AND A.ZHLB='3'),C.ACCT_NO) AS CKZH,
      C.CURR_VAL,
      (CASE WHEN B.TRANSFER_ACCT=C.ACCT_NO THEN '嘻嘻嘻' ELSE '' END) AS SM
    FROM(
      SELECT B.EXTDATE       AS RQ,
             B.BRANCH_NO     AS JGM,
             B.CURRENCY      AS BZ,
             C.CUSTOMER_TYPE,
             B.CUSTOMER_NO,
             C.CUSTOMER_NAME,
             B.ACCT_NO,
             '啊啊'          AS STATUS,
             B.LOAN_BAL,
             B.UNPD_PRIN_BAL AS P,
             ROUND(B.CAP_UNPD_INT,2)+
             (CASE WHEN B.REPAY_SCHED IN ('M','G') OR L.REPAY_SCHED IN ('M','G') THEN 0
                   ELSE (CASE WHEN L.PIA_CAPN_FREQ='S' AND L.PAST_DUE_CAPN_FREQ='S' 
                              THEN ROUND(B.THEO_UNPD_ARR_PRN,2)
                                   +(CASE WHEN L.FINE1_COMPD_OPT='Y' THEN ROUND(B.THEO_UNPD_ARRS_INT,2) ELSE 0 END)
                                   +(CASE WHEN L.FINE2_COMPD_OPT='Y' THEN ROUND(B.THEO_UNPD_INT_ARR,2) ELSE 0 END)
                              ELSE 0
                         END)
              END) AS LX,
              B.TRF_ACCT_NO AS TRANSFER_ACCT
        FROM BORM PARTITION("BORM_2018-06-13") B
       INNER JOIN LONP L ON L.ACCT_TYPE=B.ACCT_TYPE AND L.INT_CAT=B.INT_CAT
       INNER JOIN JGDY J ON B.BRANCH_NO = J.JGM AND (J.JGM='1700' OR J.SJJGM='1700') 
        LEFT JOIN CB_ACCT C ON B.ACCT_NO = C.ACCT_NO AND C.SYS_ID = 'BOR'
       WHERE B.BAD_DEBT_IND in ('02','52') 
         AND B.STAT<>'40'
     ) B
    LEFT JOIN (SELECT /*+ index(I IDX_INVM_BEC) */ CUSTOMER_NO,ACCT_NO,CURR_VAL FROM 
           INVM PARTITION("INVM_2018-06-13") I 
    INNER JOIN JGDY ON JGDY.JGM=I.BRANCH_NO AND (JGM='1700' OR SJJGM='1700')
       AND I.ACCT_DESC='S' 
       AND I.CURR_VAL<>0 
       AND I.EXTDATE = DATE'2018-06-13' 
       AND I.CURR_STATUS='00') C  ON B.CUSTOMER_NO=C.CUSTOMER_NO 
    WHERE (B.P>0 OR B.LX>0.01);

![70 5][]

改写完之后SQL由13分钟变为5秒钟执行完，看ID19还要执行999k次，查询ID18谓词信息对应SQL如下，确实是要返回999k行数据。

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';
    COUNT(*)
    ----------
    999424

![70 6][]

**|  作者简介**

### 姚崇·沃趣科技高级数据库技术专家 ###

### 熟悉Oracle数据库内部机制，丰富的数据库及RAC集群层故障诊断、性能调优、OWI、数据库备份恢复及迁移经验。 ###

[70]: /images/20220521/d12e687fed0c4d80a6eaccbac101e3c1.png
[70 1]: /images/20220521/3950453da1604ebd802a3aa396eec575.png
[70 2]: /images/20220521/27c74bcf22f44ca291e24c5dcfefce2b.png
[70 3]: /images/20220521/24cf33f88f0245c5922eeaa651d6aa8b.png
[70 4]: /images/20220521/4e1d8eac8a094360af9b6dda056e2c94.png
[70 5]: /images/20220521/8aa4ff969cf34ffe932939586f8748ce.png
[70 6]: /images/20220521/d61d4e4cdc6a4b57b3aab9175cc57242.png