ITPub博客

首页 > 数据库 > Oracle > SQL优化案例-从执行计划定位SQL问题(三)

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

原创 Oracle 作者:沃趣科技 时间:2018-06-29 09:51:15 0 删除 编辑

当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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
杭州沃趣科技股份有限公司创建于2012年(股票代码:839849),是一家专注为企业用户提供基于高性能、高可用、可扩展的开放数据库云平台解决方案的国产厂商。公司创始团队为原阿里巴巴数据库技术团队核心骨干,凭借丰富的研发及运维经验,为行业客户提供数据库云产品及软硬件一体化解决方案。

注册时间:2016-07-18

  • 博文量
    224
  • 访问量
    680718