一次300多行SQL从7分15s到24s的SQL历程

最新推荐文章于 2023-03-08 15:18:49 发布

Silencecxq

最新推荐文章于 2023-03-08 15:18:49 发布

阅读量976

点赞数

分类专栏： Oracle 文章标签： oracle sql select 优化

本文链接：https://blog.csdn.net/chen1511XIQIANG/article/details/62891305

版权

环境：阿里云 ECS 11.2.0.1 ADG Oracle

环境分析

前几天优化了一条SQL搞定以后，以为晚上日终批量报表没有问题了，然后昨天想着检查一下，看awr报告，截取晚上1点到2点的时间段，赫然映入眼帘。SQL换了，四条完全类似的SQL语句，有300多行，着实吓了一条，和开发了解到，这四条SQL其实差不多算是一条SQL，只是最后取模的的值分为1,2,3,4，所以运行了4次，可是每一次都将近400s，

然后就手动的执行了其中的一条语句,取得是模为2的为例进行执行如下：

   
   
   
    select *
      from (select a.*, rownum rn
              from (select t7.processid,
                           t7.taskname,
                           t7.operatorcode,
                           t7.orgcode,
                           t7.orgcate,
                           t7.protype,
                           t7.procode,
                           nvl(t1.prodaytotal, 0) prodaytotal,
                           nvl(t2.prodaytreat, 0) prodaytreat,
                           nvl(t3.prodayuntreat, 0) prodayuntreat,
                           nvl(t4.prodaytotalnew, 0) prodaytotalnew,
                           nvl(t5.prodaytreatnew, 0) prodaytreatnew,
                           nvl(t6.prodayuntreatnew, 0) prodayuntreatnew,
                           (select count(b.activationtime) daytasktotal
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmsysdate         c
                             where a.processid = t7.processid
                               and a.id = b.PROCESSINFOID
                               and b.operatorcode = t7.operatorcode
                               and a.processid = b.processid
                               and b.taskname = t7.taskname
                               and trunc(b.activationtime, 'dd') = c.BUSINESSDATE) daytasktotal,
                           (select count(b.activationtime) daytasktreat
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmsysdate         c
                             where a.processid = t7.processid
                               and a.id = b.PROCESSINFOID
                               and b.operatorcode = t7.operatorcode
                               and a.processid = b.processid
                               and b.taskname = t7.taskname
                               and trunc(b.completetime, 'dd') = c.BUSINESSDATE) daytasktreat,
                           (select count(b.activationtime) daytaskuntreat
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmsysdate         c
                             where a.processid = t7.processid 
    						   and a.id = b.PROCESSINFOID
                               and b.operatorcode = t7.operatorcode
                               and a.processid = b.processid
                               and b.taskname = t7.taskname
                               and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                               and trunc(b.completetime, 'dd') is null) daytaskuntreat,
                           (select count(b.activationtime) daytasktotalnew
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmsysdate         c
                             where a.processid = t7.processid
                               and b.operatorcode = t7.operatorcode
                               and a.processid = b.processid
                               and a.id = b.PROCESSINFOID
                               and b.taskname = t7.taskname
                               and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                               and b.tasktypeflag = '00') daytasktotalnew,
                           (select count(b.activationtime) daytasktreatnew
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmsysdate         c
                             where a.processid = t7.processid
                               and b.operatorcode = t7.operatorcode
                               and a.processid = b.processid
                               and a.id = b.PROCESSINFOID
                               and b.taskname = t7.taskname
                               and trunc(b.completetime, 'dd') = c.BUSINESSDATE
                               and b.tasktypeflag = '00') daytasktreatnew,
                           (select count(b.activationtime) daytaskuntreatnew
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmsysdate         c
                             where a.processid = t7.processid
                               and b.operatorcode = t7.operatorcode
                               and a.processid = b.processid
                               and a.id = b.PROCESSINFOID
                               and b.taskname = t7.taskname
                               and trunc(b.activationtime, 'dd') = c.BUSINESSDATE
                               and trunc(b.completetime, 'dd') is null
                               and b.tasktypeflag = '00') daytaskuntreatnew,
                           (select count(b.activationtime) beforetasktotal
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmsysdate         c
                             where a.processid = t7.processid
                               and b.operatorcode = t7.operatorcode
                               and a.processid = b.processid
                               and a.id = b.PROCESSINFOID
                               and b.taskname = t7.taskname
                               and trunc(b.activationtime, 'dd') <= c.BUSINESSDATE) beforetasktotal
                      from (select a.processid,
                                   b.taskname,
                                   b.operatorcode,
                                   b.orgcode,
                                   c.orgcate,
                                   a.PROTYPE,
                                   a.productcode procode,
                                   COUNT(B.ACTIVATIONTIME) prodaytotal
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmorg             c,
                                   f_smproduct          d,
                                   f_vsmsysdate         e
                             where a.processid = b.processid
                               and a.id = b.PROCESSINFOID
                               and b.orgcode = c.orgcode
                               and a.productcode = d.procode
                               and trunc(b.activationtime, 'dd') = e.BUSINESSDATE
                               and a.processid in ('financeApp',
                                                   'aheadRedemption',
                                                   'financeInno',
                                                   'financeCusMod',
                                                   'financeAccMod')
                             GROUP BY a.processid,
                                      b.taskname,
                                      b.operatorcode,
                                      b.orgcode,
                                      c.orgcate,
                                      a.protype,
                                      a.productcode) t1,
                           (select a.processid,
                                   b.taskname,
                                   b.operatorcode,
                                   b.orgcode,
                                   c.orgcate,
                                   a.protype,
                                   a.productcode procode,
                                   COUNT(B.ACTIVATIONTIME) prodaytreat
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   f_vsmorg             c,
                                   f_smproduct          d,
                                   f_vsmsysdate         e
                             where a.processid = b.processid
                               and a.id = b.PROCESSINFOID
                               and b.orgcode = c.orgcode
                               and a.productcode = d.procode
                               and trunc(b.completetime, 'dd') = e.BUSINESSDATE
                               and a.processid in ('financeApp',
                                                   'aheadRedemption',
                                                   'financeInno',
                                                   'financeCusMod',
                                                   'financeAccMod')
                             GROUP BY a.processid,
                                      b.taskname,
                                      b.operatorcode,
                                      b.orgcode,
                                      c.orgcate,
                                      a.protype,
                                      a.productcode) t2,
                           (select a.processid,
                                   b.taskname,
                                   b.operatorcode,
                                   b.orgcode,
                                   c.orgcate,
                                   a.protype,
                                   a.productcode procode,
                                   COUNT(B.ACTIVATIONTIME) prodayuntreat
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK  b,
                                   f_vsmorg     c,
                                   f_smproduct  d,
                                   f_vsmsysdate e
                             where a.processid = b.processid
                               and b.orgcode = c.orgcode
                               and a.productcode = d.procode
                               and a.id = b.PROCESSINFOID
                               and trunc(b.activationtime, 'dd') = e.BUSINESSDATE
                               and a.processid in ('financeApp',
                                                   'aheadRedemption',
                                                   'financeInno',
                                                   'financeCusMod',
                                                   'financeAccMod')
                               and b.completetime is null
                             GROUP BY a.processid,
                                      b.taskname,
                                      b.operatorcode,
                                      b.orgcode,
                                      c.orgcate,
                                      a.protype,
                                      a.productcode) t3,
                           (select a.processid,
                                   b.taskname,
                                   b.operatorcode,
                                   b.orgcode,
                                   c.orgcate,
                                   a.protype,
                                   a.productcode procode,
                                   COUNT(B.ACTIVATIONTIME) prodaytotalnew
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   credit.smorg         c,
                                   f_smproduct          d,
                                   f_vsmsysdate         e
                             where a.processid = b.processid
                               and b.orgcode = c.orgcode
                               and a.id = b.PROCESSINFOID
                               and a.productcode = d.procode
                               and trunc(b.activationtime, 'dd') = e.BUSINESSDATE
                               and a.processid in ('finan ceApp',
                                                   'aheadRedemption',
                                                   'financeInno',
                                                   'financeCusMod',
                                                   'financeAccMod')
                               and b.tasktypeflag = '00'
                             GROUP BY a.processid,
                                      b.taskname,
                                      b.operatorcode,
                                      b.orgcode,
                                      c.orgcate,
                                      a.protype,
                                      a.productcode) t4,
                           (select a.processid,
                                   b.taskname,
                                   b.operatorcode,
                                   b.orgcode,
                                   c.orgcate,
                                   a.protype,
                                   a.productcode procode,
                                   COUNT(B.ACTIVATIONTIME) prodaytreatnew
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   credit.smorg         c,
                                   f_smproduct          d,
                                   f_vsmsysdate         e
                             where a.processid = b.processid
                               and b.orgcode = c.orgcode
                               and a.productcode = d.procode
                               and a.id = b.PROCESSINFOID
                               and trunc(b.completetime, 'dd') = e.BUSINESSDATE
                               and a.processid in ('financeApp',
                                                   'aheadRedemption',
                                                   'financeInno',
                                                   'financeCusMod',
                                                   'financeAccMod')
                               and b.tasktypeflag = '00'
                             GROUP BY a.processid,
                                      b.taskname,
                                      b.operatorcode,
                                      b.orgcode,
                                      c.orgcate,
                                      a.protype,
                                      a.productcode) t5,
                           (select a.processid,
                                   b.taskname,
                                   b.operatorcode,
                                   b.orgcode,
                                   c.orgcate,
                                   a.protype,
                                   a.productcode procode,
                                   COUNT(B.ACTIVATIONTIME) prodayuntreatnew
                              from bpm.jbpm_processinfo a,
                                   VBPMEXTTASK          b,
                                   credit.smorg         c,
                                   f_smproduct          d,
                                   f_vsmsysdate         e
                             where a.processid = b.processid
                               and b.orgcode = c.orgcode
                               and a.productcode = d.procode
                               and a.id = b.PROCESSINFOID
                               and trunc(b.activationtime, 'dd') = e.BUSINESSDATE
                               and trunc(b.completetime, 'dd') is null
                               and a.processid in ('financeApp',
                                                   'aheadRedemption',
                                                   'financeInno',
                                                   'financeCusMod',
                                                   'financeAccMod')
                               and b.tasktypeflag = '00'
                             GROUP BY a.processid,
                                      b.taskname,
                                      b.operatorcode,
                                      b.orgcode,
                                      c.orgcate,
                                      a.protype,
                                      a.productcode) t6,
                           (select br.name     processid,
                                   br.taskname,
                                   s.usercode  operatorcode,
                                   v2.PROCODE,
                                   v2.PROTYPE,
                                   fv.orgcode,
                                   fv.orgcate
                              from bpmsteprole       br,
                                   f_vsmuser         s,
                                   f_vsmuserflowrole sr,
                                   f_vsmflowrole     r,
                                   f_vsmproduct      v2,
                                   f_smuserpro       v3,
                                   f_vsmorg          fv
                             where s.id = sr.flowuserid
                               and sr.flowroleid = r.id
                               and r.flowrolecode = br.steprole
                               and s.validflag in ('0', '1')
                               and s.id = v3.userid
                               and v2.id = v3.proid
                               and s.ORGID = fv.ID
                               AND sr.USEFLAG = '1') t7
                     where t7.processid = t1.processid(+)
                       and t7.processid = t2.processid(+)
                       and t7.processid = t3.processid(+)
                       and t7.processid = t4.processid(+)
                       and t7.processid = t5.processid(+)
                       and t7.processid = t6.processid(+)
                       and t7.taskname = t1.taskname(+)
                       and t7.taskname = t2.taskname(+)
                       and t7.taskname = t3.taskname(+)
                       and t7.taskname = t4.taskname(+)
                       and t7.taskname = t5.taskname(+)
                       and t7.taskname = t6.taskname(+)
                       and t7.operatorcode = t1.operatorcode(+)
                       and t7.operatorcode = t2.operatorcode(+)
                       and t7.operatorcode = t3.operatorcode(+)
                       and t7.operatorcode = t4.operatorcode(+)
                       and t7.operatorcode = t5.operatorcode(+)
                       and t7.operatorcode = t6.operatorcode(+)
                       and t7.orgcode = t1.orgcode(+)
                       and t7.orgcode = t2.orgcode(+)
                       and t7.orgcode = t3.orgcode(+)
                       and t7.orgcode = t4.orgcode(+)
                       and t7.orgcode = t5.orgcode(+)
                       and t7.orgcode = t6.orgcode(+)
                       and t7.orgcate = t1.orgcate(+)
                       and t7.orgcate = t2.orgcate(+)
                       and t7.orgcate = t3.orgcate(+)
                       and t7.orgcate = t4.orgcate(+)
                       and t7.orgcate = t5.orgcate(+)
                       and t7.protype = t6.protype(+)
                       and t7.protype = t1.protype(+)
                       and t7.protype = t2.protype(+)
                       and t7.protype = t3.protype(+)
                       and t7.protype = t4.protype(+)
                       and t7.protype = t5.protype(+)
                       and t7.protype = t6.protype(+)
                       and t7.procode = t1.procode(+)
                       and t7.procode = t2.procode(+)
                       and t7.procode = t3.procode(+)
                       and t7.procode = t4.procode(+)
                       and t7.procode = t5.procode(+)
                       and t7.procode = t6.procode(+)) a)
     where mod(rn, 4) = 2

执行计划如下：

    
    
    
     36679 rows selected.
     
     Elapsed: 00:07:15.54
     
     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 2469110542
     
     -------------------------------------------------------------------------------------------------------------------------------
     | Id  | Operation			     | Name			      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
     -------------------------------------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT		     |				      |   183K|    44M|       |   268K	(1)| 00:53:39 |
     |   1 |  SORT AGGREGATE 		     |				      |     1 |    84 |       | 	   |	      |
     |   2 |   NESTED LOOPS			     |				      |       |       |       | 	   |	      |
     |   3 |    NESTED LOOPS 		     |				      |     1 |    84 |       |   401	(0)| 00:00:05 |
     |   4 |     NESTED LOOPS		     |				      |     1 |    68 |       |   399	(0)| 00:00:05 |
     |   5 |      TABLE ACCESS FULL		     | SMSYSDATE		      |     1 |     8 |       |     3	(0)| 00:00:01 |
     |*  6 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK		      |     1 |    60 |       |   396	(0)| 00:00:05 |
     |*  7 |       INDEX RANGE SCAN		     | EXTTASK_OPTCODE		      |   574 |       |       |     4	(0)| 00:00:01 |
     |*  8 |     INDEX UNIQUE SCAN		     | SYS_C0011919		      |     1 |       |       |     1	(0)| 00:00:01 |
     |*  9 |    TABLE ACCESS BY INDEX ROWID	     | JBPM_PROCESSINFO 	      |     1 |    16 |       |     2	(0)| 00:00:01 |
     |  10 |  SORT AGGREGATE 		     |				      |     1 |    95 |       | 	   |	      |
     |  11 |   NESTED LOOPS			     |				      |       |       |       | 	   |	      |
     |  12 |    NESTED LOOPS 		     |				      |     1 |    95 |       |   401	(0)| 00:00:05 |
     |  13 |     NESTED LOOPS		     |				      |     1 |    79 |       |   399	(0)| 00:00:05 |
     |  14 |      TABLE ACCESS FULL		     | SMSYSDATE		      |     1 |     8 |       |     3	(0)| 00:00:01 |
     |* 15 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK		      |     1 |    71 |       |   396	(0)| 00:00:05 |
     |* 16 |       INDEX RANGE SCAN		     | EXTTASK_OPTCODE		      |   574 |       |       |     4	(0)| 00:00:01 |
     |* 17 |     INDEX UNIQUE SCAN		     | SYS_C0011919		      |     1 |       |       |     1	(0)| 00:00:01 |
     |* 18 |    TABLE ACCESS BY INDEX ROWID	     | JBPM_PROCESSINFO 	      |     1 |    16 |       |     2	(0)| 00:00:01 |
     |  19 |  SORT AGGREGATE 		     |				      |     1 |    95 |       | 	   |	      |
     |  20 |   NESTED LOOPS			     |				      |       |       |       | 	   |	      |
     |  21 |    NESTED LOOPS 		     |				      |     1 |    95 |       |   401	(0)| 00:00:05 |
     |  22 |     NESTED LOOPS		     |				      |     1 |    79 |       |   399	(0)| 00:00:05 |
     |  23 |      TABLE ACCESS FULL		     | SMSYSDATE		      |     1 |     8 |       |     3	(0)| 00:00:01 |
     |* 24 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK		      |     1 |    71 |       |   396	(0)| 00:00:05 |
     |* 25 |       INDEX RANGE SCAN		     | EXTTASK_OPTCODE		      |   574 |       |       |     4	(0)| 00:00:01 |
     |* 26 |     INDEX UNIQUE SCAN		     | SYS_C0011919		      |     1 |       |       |     1	(0)| 00:00:01 |
     |* 27 |    TABLE ACCESS BY INDEX ROWID	     | JBPM_PROCESSINFO 	      |     1 |    16 |       |     2	(0)| 00:00:01 |
     |  28 |  SORT AGGREGATE 		     |				      |     1 |    87 |       | 	   |	      |
     |  29 |   NESTED LOOPS			     |				      |       |       |       | 	   |	      |
     |  30 |    NESTED LOOPS 		     |				      |     1 |    87 |       |   401	(0)| 00:00:05 |
     |  31 |     NESTED LOOPS		     |				      |     1 |    71 |       |   399	(0)| 00:00:05 |
     |  32 |      TABLE ACCESS FULL		     | SMSYSDATE		      |     1 |     8 |       |     3	(0)| 00:00:01 |
     |* 33 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK		      |     1 |    63 |       |   396	(0)| 00:00:05 |
     |* 34 |       INDEX RANGE SCAN		     | EXTTASK_OPTCODE		      |   574 |       |       |     4	(0)| 00:00:01 |
     |* 35 |     INDEX UNIQUE SCAN		     | SYS_C0011919		      |     1 |       |       |     1	(0)| 00:00:01 |
     |* 36 |    TABLE ACCESS BY INDEX ROWID	     | JBPM_PROCESSINFO 	      |     1 |    16 |       |     2	(0)| 00:00:01 |
     |  37 |  SORT AGGREGATE 		     |				      |     1 |    98 |       | 	   |	      |
     |  38 |   NESTED LOOPS			     |				      |       |       |       | 	   |	      |
     |  39 |    NESTED LOOPS 		     |				      |     1 |    98 |       |   401	(0)| 00:00:05 |
     |  40 |     NESTED LOOPS		     |				      |     1 |    82 |       |   399	(0)| 00:00:05 |
     |  41 |      TABLE ACCESS FULL		     | SMSYSDATE		      |     1 |     8 |       |     3	(0)| 00:00:01 |
     |* 42 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK		      |     1 |    74 |       |   396	(0)| 00:00:05 |
     |* 43 |       INDEX RANGE SCAN		     | EXTTASK_OPTCODE		      |   574 |       |       |     4	(0)| 00:00:01 |
     |* 44 |     INDEX UNIQUE SCAN		     | SYS_C0011919		      |     1 |       |       |     1	(0)| 00:00:01 |
     |* 45 |    TABLE ACCESS BY INDEX ROWID	     | JBPM_PROCESSINFO 	      |     1 |    16 |       |     2	(0)| 00:00:01 |
     |  46 |  SORT AGGREGATE 		     |				      |     1 |    98 |       | 	   |	      |
     |  47 |   NESTED LOOPS			     |				      |       |       |       | 	   |	      |
     |  48 |    NESTED LOOPS 		     |				      |     1 |    98 |       |   401	(0)| 00:00:05 |
     |  49 |     NESTED LOOPS		     |				      |     1 |    82 |       |   399	(0)| 00:00:05 |
     |  50 |      TABLE ACCESS FULL		     | SMSYSDATE		      |     1 |     8 |       |     3	(0)| 00:00:01 |
     |* 51 |      TABLE ACCESS BY INDEX ROWID     | JBPM_EXT_TASK		      |     1 |    74 |       |   396	(0)| 00:00:05 |
     |*

最低0.47元/天解锁文章

Silencecxq

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
一次300多行SQL从7分15s到24s的SQL历程

环境：阿里云 ECS 11.2.0.1 ADG Oracle环境分析前几天优化了一条SQL搞定以后，以为晚上日终批量报表没有问题了，然后昨天想着检查一下，看awr报告，截取晚上1点到2点的时间段，赫然映入眼帘。SQL换了，四条完全类似的SQL语句，有300多行，着实吓了一条，和开发了解到，这四条SQL其实差不多算是一条SQL，只是最后取模的的值分为1,2,3,4，所以运行了4次
复制链接

扫一扫

专栏目录