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

当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);

执行计划如下:

可以一眼定位到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如下:

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); 

改写完之后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

|  作者简介

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

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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Linyb极客之路

Spring事务机制详解

Spring事务机制主要包括 声明式事务和编程式事务,此处侧重讲解声明式事务,编程式事务在实际开发中得不到广泛使用,仅供学习参考。

612
来自专栏北京马哥教育

十分钟带你了解 Python3 多线程核心知识

每个独立的线程有一个程序运行的入口、顺序执行序列和程序的出口。但是线程不能够独立执行,必须依存在应用程序中,由应用程序提供多个线程执行控制。 每个线程都有他自...

2475
来自专栏乐沙弥的世界

PL/SQL --> DBMS_DDL包的使用

为了便于建立性能良好的PL/SQL程序,Oracle提供了大量的系统包供使用。Oracle提供的这些包扩展并增强了数据库的一些功能,以及突

834
来自专栏乐沙弥的世界

SQL*Loader使用方法

SQL*Loader由一个输入控制文件来控制整个装载的相关描述信息,一个或多个数据文件作为原始数据,其详细组成结构包括

582
来自专栏菩提树下的杨过

如何在多线程中调用winform窗体控件

由于 Windows 窗体控件本质上不是线程安全的。因此如果有两个或多个线程适度操作某一控件的状态(set value),则可能会迫使该控件进入一种不一致的状态...

18410
来自专栏杨建荣的学习笔记

探究AWR 第二篇(r3笔记第93天)

在探究awr第一篇中介绍了awr的一些基本操作 http://blog.itpub.net/23718752/viewspace-1123134/ 在这一篇中,...

2917
来自专栏Vamei实验室

Linux进程基础

计算机实际上可以做的事情实质上非常简单,比如计算两个数的和,再比如在内存中寻找到某个地址等等。这些最基础的计算机动作被称为指令 (instruction)。所谓...

1855
来自专栏更流畅、简洁的软件开发方式

我的数据访问函数库的源代码(一)—— 共用部分

/* 2008 4 25 更新 */ 我的数据访问函数库的源码。整个类有1400多行,原先就是分开来写的,现在更新后还是分开来发一下吧。 第一部分:内部成员...

1739
来自专栏Golang语言社区

Go语言社区 APP --问答模块数据存储流程及代码

注:这个是我们社区APP的问答社区的 数据库保存及数据读取的流程; 流程如下: 保存流程====接到客户端数据保存到数据库--...

3489
来自专栏乐沙弥的世界

PL/SQL 下邮件发送程序

      对DBA而言,尽管在os级别下发送邮件是轻而易举的事情,然而很多时候我们也需要在PL/SQL中来发送邮件,比如监控job的执行状况等。本文根据网友(...

692

扫码关注云+社区