环境:阿里云 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 |
|*