1、标量子查询嵌套子查询的SQL monitor
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : TBCS (20810:45485)
SQL ID : 4kvbp3sq9p28j
SQL Execution ID : 16777719
Execution Started : 04/02/2018 16:54:35
First Refresh Time : 04/02/2018 16:54:41
Last Refresh Time : 04/02/2018 17:08:55
Duration : 860s
Module/Action : UnifyFileDeal@ncrminf1 (TNS V1-V3)/-
Service : crmdb2
Program : UnifyFileDeal@ncrminf1 (TNS V1-V3)
SQL Text
------------------------------
select distinct '',
a.groupid "集团编码",
b.custname "集团名称",
a.corpscope "集团价值级别",
b.address "集团地址",
(select c.NAME
from tbcs.BUSSCUSTSUBS c
where a.custid = c.custid
and c.status = 'stcmNml'
and rownum = 1) "联系人姓名",
(select c.SERVNUMBER
from tbcs.BUSSCUSTSUBS c
where a.custid = c.custid
and c.status = 'stcmNml'
and rownum = 1) "联系人电话",
d.custmgr "客户经理编码",
e.OPERNAME "客户经理名称",
e.CONTACTPHONE "客户经理联系电话",
d.region "归属地市",
(DECODE(d.REGION,
11,
'苏州',
12,
'淮安',
13,
'宿迁',
14,
'南京',
15,
'连云港',
16,
'徐州',
17,
'常州',
18,
'镇江',
19,
'无锡',
20,
'南通',
21,
'泰州',
22,
'盐城',
23,
'扬州')) "归属地市名称",
(SELECT m.orgid
FROM tbcs.V_ESOP_ORGANIZATION_M M
WHERE M.PARENTID = 'M' || m.region
AND M.ESOP_TYPE = 4
AND ROWNUM = 1
START WITH M.ORGID = (SELECT V.PARENTID
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = e.OPERID
AND V.ESOP_TYPE = 4)
AND M.ESOP_TYPE = 4
CONNECT BY PRIOR M.PARENTID = M.ORGID
AND M.ESOP_TYPE = 4) "归属区域",
(SELECT m.orgname
FROM tbcs.V_ESOP_ORGANIZATION_M M
WHERE M.PARENTID = 'M' || m.region
AND M.ESOP_TYPE = 4
AND ROWNUM = 1
START WITH M.ORGID = (SELECT V.PARENTID
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = e.OPERID
AND V.ESOP_TYPE = 4)
AND M.ESOP_TYPE = 4
CONNECT BY PRIOR M.PARENTID = M.ORGID
AND M.ESOP_TYPE = 4) "归属区域名称",
(SELECT v.orgid
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = (SELECT V.PARENTID
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = e.OPERID
AND V.ESOP_TYPE = 4)
AND V.ESOP_TYPE = 4
AND ROWNUM = 1) "归属营业部",
(SELECT V.ORGNAME
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = (SELECT V.PARENTID
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = e.OPERID
AND V.ESOP_TYPE = 4)
AND V.ESOP_TYPE = 4
AND ROWNUM = 1) "归属营业部名称",
a.custid "CRM集团编码",
decode(trunc(b.createdate, 'hh'),
trunc(sysdate, 'hh') - 1 / 24,
'A',
'M') "操作类型",
b.status,
a.UNITKIND,
b.POSTCODE,
a.GROUPSERVICELEVEL,
b.certtype,
b.certid,
a.vocaionkind1,
(SELECT CM.EMAIL
FROM TBCS.CM_CU_RELATIONINFO CM
WHERE cm.mobileno IN
(select buss.servnumber
from tbcs.BUSSCUSTSUBS BUSS
WHERE BUSS.STATUS = 'stcmNml'
AND buss.roletype = 'MemberSort0300'
AND BUSS.CUSTID = a.custid
AND BUSS.STATUS = 'stcmNml'
AND BUSS.REGION = 12)
AND cm.email IS NOT NULL
AND ROWNUM = 1)
from tbcs.group_customer a,
tbcs.customer b,
tbcs.cm_cu_groupcustservchannel d,
tbcs.operator e,
tbcs.organization g,
tbcs.reception h
where a.custid = b.custid
and a.custid = d.custid
and d.custmgr = e.OPERID
and e.orgid = g.ORGID
and h.custid = a.custid
and h.recdefid in
('GroupCustInfoLog', 'ManagerAssign', 'BusscustSubsManage')
and h.recdate >= trunc(sysdate, 'hh') - 1 / 24
and h.recdate < trunc(sysdate, 'hh')
and h.region = 12
and d.region = 12
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
============================================================================================
| 1003 | 985 | 0.04 | 0.00 | 0.01 | 18 | 186M | 29 | 232KB |
============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1650826172)
======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
======================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1090 | +6 | 165 | 165 | | | | | | |
| 1 | COUNT STOPKEY | | | | 1090 | +6 | 165 | 165 | | | | | | |
| 2 | PARTITION LIST ALL | | 56 | 35 | 1090 | +6 | 165 | 165 | | | | | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BUSSCUSTSUBS | 56 | 35 | 1090 | +6 | 165 | 165 | 1 | 8192 | | | | |
| 4 | INDEX RANGE SCAN | IDX_GRP_BUSSCUSTSUBS_CUSTID | 56 | 11 | 1090 | +6 | 165 | 165 | 8 | 65536 | | | | |
| 5 | COUNT STOPKEY | | | | 1090 | +6 | 165 | 165 | | | | | | |
| 6 | PARTITION LIST ALL | | 56 | 35 | 1090 | +6 | 165 | 165 | | | | | | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BUSSCUSTSUBS | 56 | 35 | 1090 | +6 | 165 | 165 | | | | | | |
| 8 | INDEX RANGE SCAN | IDX_GRP_BUSSCUSTSUBS_CUSTID | 56 | 11 | 1090 | +6 | 165 | 165 | | | | | | |
| 9 | COUNT | | | | 877 | +6 | 15 | 14 | | | | | | |
| 10 | FILTER | | | | 877 | +6 | 15 | 14 | | | | | | |
| 11 | CONNECT BY WITH FILTERING | | | | 877 | +6 | 15 | 70 | | | | | | |
| 12 | FILTER | | | | 877 | +6 | 15 | 14 | | | | | | |
| 13 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 19761 | 32 | 877 | +6 | 15 | 312K | | | | | | |
| 14 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 1 | 32 | 881 | +2 | 15 | 14 | | | | 0.26 | Cpu (2) | |
| 15 | COUNT | | | | 877 | +6 | 70 | 56 | | | | | | |
| 16 | HASH JOIN | | 213 | 97 | 877 | +6 | 70 | 56 | | | | | | |
| 17 | CONNECT BY PUMP | | | | 877 | +6 | 70 | 70 | | | | | | |
| 18 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 19761 | 32 | 877 | +6 | 70 | 1M | | | | 0.13 | Cpu (1) | |
| 19 | COUNT | | | | 877 | +6 | 15 | 14 | | | | | | |
| 20 | FILTER | | | | 877 | +6 | 15 | 14 | | | | | | |
| 21 | CONNECT BY WITH FILTERING | | | | 877 | +6 | 15 | 70 | | | | | | |
| 22 | FILTER | | | | 877 | +6 | 15 | 14 | | | | | | |
| 23 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 19761 | 32 | 877 | +6 | 15 | 312K | | | | | | |
| 24 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 1 | 32 | 877 | +6 | 15 | 14 | | | | | | |
| 25 | COUNT | | | | 877 | +6 | 70 | 56 | | | | | | |
| 26 | HASH JOIN | | 213 | 97 | 877 | +6 | 70 | 56 | | | | 0.26 | Cpu (2) | |
| 27 | CONNECT BY PUMP | | | | 877 | +6 | 70 | 70 | | | | | | |
| 28 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 19761 | 32 | 877 | +6 | 70 | 1M | | | | | | |
| 29 | COUNT STOPKEY | | | | 877 | +6 | 15 | 14 | | | | | | |
| 30 | FILTER | | | | 877 | +6 | 15 | 14 | | | | | | |
| 31 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 19761 | 32 | 877 | +6 | 15 | 76076 | | | | | | |
| 32 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 1 | 32 | 877 | +6 | 15 | 14 | | | | | | |
| 33 | COUNT STOPKEY | | | | 877 | +6 | 15 | 14 | | | | | | |
| 34 | FILTER | | | | 877 | +6 | 15 | 14 | | | | | | |
| 35 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 19761 | 32 | 877 | +6 | 15 | 76076 | | | | | | |
| 36 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 1 | 32 | 877 | +6 | 15 | 14 | | | | | | |
| 37 | COUNT STOPKEY | | | | 885 | +6 | 165 | 6 | | | | | | |
| -> 38 | FILTER | | | | 1094 | +6 | 165 | 6 | | | | 1.79 | Cpu (14) | |
| -> 39 | TABLE ACCESS FULL | CM_CU_RELATIONINFO | 66265 | 1245 | 1097 | +3 | 165 | 12M | | | | 10.34 | Cpu (81) | 100% |
| 40 | PARTITION LIST SINGLE | | 1 | 6 | 885 | +6 | 12M | 6 | | | | 1.02 | Cpu (8) | |
| -> 41 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BUSSCUSTSUBS | 1 | 6 | 1094 | +6 | 12M | 6 | 2 | 16384 | | 71.78 | Cpu (562) | |
| -> 42 | INDEX RANGE SCAN | IDX_GROUPMEM_SERVNUMBER | 2 | 3 | 1094 | +6 | 12M | 255M | 1 | 8192 | | 14.30 | Cpu (112) | |
| 43 | SORT UNIQUE | | 1 | 394K | 1090 | +6 | 1 | 0 | | | | | | |
| 44 | FILTER | | | | 1090 | +6 | 1 | 171 | | | | | | |
| 45 | NESTED LOOPS | | 1 | 10721 | 1090 | +6 | 1 | 171 | | | | | | |
| 46 | NESTED LOOPS | | 1 | 10721 | 1084 | +6 | 1 | 183 | | | | | | |
| 47 | NESTED LOOPS OUTER | | 1 | 10717 | 1084 | +6 | 1 | 184 | | | | | | |
| 48 | NESTED LOOPS SEMI | | 1 | 10716 | 1084 | +6 | 1 | 185 | | | | | | |
| 49 | NESTED LOOPS | | 1 | 10716 | 1084 | +6 | 1 | 185 | | | | | | |
| 50 | NESTED LOOPS | | 1 | 10714 | 1084 | +6 | 1 | 185 | | | | | | |
| 51 | NESTED LOOPS | | 1 | 10713 | 1084 | +6 | 1 | 185 | | | | | | |
| 52 | PARTITION RANGE ITERATOR | | 1 | 10684 | 1084 | +6 | 1 | 185 | | | | | | |
| 53 | PARTITION LIST SINGLE | | 1 | 10684 | 1084 | +6 | 1 | 185 | | | | | | |
| -> 54 | TABLE ACCESS FULL | RECEPTION | 1 | 10684 | 1094 | +6 | 1 | 185 | | | | | | 94% |
| 55 | PARTITION LIST ALL | | 1 | 29 | 1084 | +6 | 185 | 185 | | | | | | |
| 56 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | GROUP_CUSTOMER | 1 | 29 | 1084 | +6 | 2578 | 185 | 1 | 8192 | | | | |
| -> 57 | INDEX RANGE SCAN | PK_CM_CU_GROUP | 1 | 28 | 1094 | +6 | 2578 | 185 | 1 | 8192 | | | | |
| 58 | PARTITION LIST SINGLE | | 1 | 1 | 1084 | +6 | 185 | 185 | | | | | | |
| -> 59 | INDEX RANGE SCAN | PK_GROUPCUSTSERV_CUSTMGRRE | 1 | 1 | 1094 | +6 | 185 | 185 | 16 | 128KB | | | | |
| 60 | TABLE ACCESS BY INDEX ROWID | T_UCP_STAFFBASICINFO | 1 | 2 | 1084 | +6 | 185 | 185 | | | | | | |
| -> 61 | INDEX UNIQUE SCAN | PK_T_UCP_STAFFBASICINFO | 1 | 1 | 1094 | +6 | 185 | 185 | | | | | | |
| -> 62 | INDEX UNIQUE SCAN | PK_T_UCP_ORGAINFO | 162K | | 1094 | +6 | 185 | 185 | | | | | | |
| 63 | INDEX RANGE SCAN | PK_UCP_STAFFMAC | 1 | 1 | | | 185 | | | | | | | |
| -> 64 | INDEX RANGE SCAN | PK_CM_CU_CUSTOMER | 1 | 3 | 1094 | +6 | 185 | 183 | 6 | 49152 | | | | |
| -> 65 | TABLE ACCESS BY GLOBAL INDEX ROWID | CUSTOMER | 1 | 4 | 1094 | +6 | 185 | 171 | 1 | 8192 | | | | |
======================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
4 - access("C"."CUSTID"=:B1 AND "C"."STATUS"='stcmNml')
5 - filter(ROWNUM=1)
8 - access("C"."CUSTID"=:B1 AND "C"."STATUS"='stcmNml')
10 - filter(("M"."PARENTID"='M'||TO_CHAR("M"."REGION") AND "M"."ESOP_TYPE"=4 AND ROWNUM=1))
11 - access("M"."ORGID"=PRIOR NULL)
12 - filter("M"."ORGID"=)
13 - filter("M"."ESOP_TYPE"=4)
14 - filter(("V"."ORGID"=:B1 AND "V"."ESOP_TYPE"=4))
16 - access("connect$_by$_pump$_007"."PRIOR M.PARENTID "="M"."ORGID")
18 - filter("M"."ESOP_TYPE"=4)
20 - filter(("M"."PARENTID"='M'||TO_CHAR("M"."REGION") AND "M"."ESOP_TYPE"=4 AND ROWNUM=1))
21 - access("M"."ORGID"=PRIOR NULL)
22 - filter("M"."ORGID"=)
23 - filter("M"."ESOP_TYPE"=4)
24 - filter(("V"."ORGID"=:B1 AND "V"."ESOP_TYPE"=4))
26 - access("connect$_by$_pump$_016"."PRIOR M.PARENTID "="M"."ORGID")
28 - filter("M"."ESOP_TYPE"=4)
29 - filter(ROWNUM=1)
30 - filter("V"."ORGID"=)
31 - filter("V"."ESOP_TYPE"=4)
32 - filter(("V"."ORGID"=:B1 AND "V"."ESOP_TYPE"=4))
33 - filter(ROWNUM=1)
34 - filter("V"."ORGID"=)
35 - filter("V"."ESOP_TYPE"=4)
36 - filter(("V"."ORGID"=:B1 AND "V"."ESOP_TYPE"=4))
37 - filter(ROWNUM=1)
38 - filter( IS NOT NULL)
39 - filter("CM"."EMAIL" IS NOT NULL)
41 - filter(("BUSS"."CUSTID"=:B1 AND "BUSS"."ROLETYPE"='MemberSort0300' AND "BUSS"."STATUS"='stcmNml'))
42 - access("BUSS"."SERVNUMBER"=:B1 AND "BUSS"."REGION"=12)
44 - filter(TRUNC(SYSDATE@!,'fmhh')>TRUNC(SYSDATE@!,'fmhh')-.0416666666666666666666666666666666666667)
54 - filter((INTERNAL_FUNCTION("H"."RECDEFID") AND "H"."RECDATE"<TRUNC(SYSDATE@!,'fmhh') AND
"H"."RECDATE">=TRUNC(SYSDATE@!,'fmhh')-.0416666666666666666666666666666666666667))
57 - access("H"."CUSTID"="A"."CUSTID")
59 - access("A"."CUSTID"="D"."CUSTID" AND "D"."REGION"=12)
61 - access("D"."CUSTMGR"="BASIC"."STAFFID")
62 - access("BASIC"."ORGAID"="ORG"."ORGAID")
63 - access("BASIC"."STAFFID"="MAC"."STAFFID")
64 - access("A"."CUSTID"="B"."CUSTID")
2、SQL Monitor分析
该SQL主要问题在于如下执行计划部分
======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
======================================================================================================================================================================================================================
| 37 | COUNT STOPKEY | | | | 885 | +6 | 165 | 6 | | | | | | |
| -> 38 | FILTER | | | | 1094 | +6 | 165 | 6 | | | | 1.79 | Cpu (14) | |
| -> 39 | TABLE ACCESS FULL | CM_CU_RELATIONINFO | 66265 | 1245 | 1097 | +3 | 165 | 12M | | | | 10.34 | Cpu (81) | 100% |
| 40 | PARTITION LIST SINGLE | | 1 | 6 | 885 | +6 | 12M | 6 | | | | 1.02 | Cpu (8) | |
| -> 41 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BUSSCUSTSUBS | 1 | 6 | 1094 | +6 | 12M | 6 | 2 | 16384 | | 71.78 | Cpu (562) | |
| -> 42 | INDEX RANGE SCAN | IDX_GROUPMEM_SERVNUMBER | 2 | 3 | 1094 | +6 | 12M | 255M | 1 | 8192 | | 14.30 | Cpu (112) | |
38 - filter( IS NOT NULL)
39 - filter("CM"."EMAIL" IS NOT NULL)
41 - filter(("BUSS"."CUSTID"=:B1 AND "BUSS"."ROLETYPE"='MemberSort0300' AND "BUSS"."STATUS"='stcmNml'))
42 - access("BUSS"."SERVNUMBER"=:B1 AND "BUSS"."REGION"=12)
44 - filter(TRUNC(SYSDATE@!,'fmhh')>TRUNC(SYSDATE@!,'fmhh')-.0416666666666666666666666666666666666667)
对应于SQL语句中的如下部分
(SELECT CM.EMAIL
FROM TBCS.CM_CU_RELATIONINFO CM
WHERE cm.mobileno IN
(select buss.servnumber
from tbcs.BUSSCUSTSUBS BUSS
WHERE BUSS.STATUS = 'stcmNml'
AND buss.roletype = 'MemberSort0300'
AND BUSS.CUSTID = a.custid
AND BUSS.STATUS = 'stcmNml'
AND BUSS.REGION = 12)
AND cm.email IS NOT NULL
AND ROWNUM = 1)
由于上述查询是标量子查询的一部分，此时标量子查询只能走类似的nested loop的filter执行计划，而这个标量子查询中又嵌套了子查询，此时就需要递归的filter，而这个递归的子查询在访问tbcs.BUSSCUSTSUBS BUSS表时走索引范围扫描，此时返回255M行数据，这255M数据回表则需要小消耗更多的IO、cpu资源。
3、标量子查询部分一般建议是等价改写
这里将上述消耗性能的标量子查询部分进行了改写，如下：
select distinct '',
a.groupid "集团编码",
b.custname "集团名称",
a.corpscope "集团价值级别",
b.address "集团地址",
(select c.NAME
from tbcs.BUSSCUSTSUBS c
where a.custid = c.custid
and c.status = 'stcmNml'
and rownum = 1) "联系人姓名",
(select c.SERVNUMBER
from tbcs.BUSSCUSTSUBS c
where a.custid = c.custid
and c.status = 'stcmNml'
and rownum = 1) "联系人电话",
d.custmgr "客户经理编码",
e.OPERNAME "客户经理名称",
e.CONTACTPHONE "客户经理联系电话",
d.region "归属地市",
(DECODE(d.REGION,
11,
'苏州',
12,
'淮安',
13,
'宿迁',
14,
'南京',
15,
'连云港',
16,
'徐州',
17,
'常州',
18,
'镇江',
19,
'无锡',
20,
'南通',
21,
'泰州',
22,
'盐城',
23,
'扬州')) "归属地市名称",
(SELECT m.orgid
FROM tbcs.V_ESOP_ORGANIZATION_M M
WHERE M.PARENTID = 'M' || m.region
AND M.ESOP_TYPE = 4
AND ROWNUM = 1
START WITH M.ORGID = (SELECT V.PARENTID
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = e.OPERID
AND V.ESOP_TYPE = 4)
AND M.ESOP_TYPE = 4
CONNECT BY PRIOR M.PARENTID = M.ORGID
AND M.ESOP_TYPE = 4) "归属区域",
(SELECT m.orgname
FROM tbcs.V_ESOP_ORGANIZATION_M M
WHERE M.PARENTID = 'M' || m.region
AND M.ESOP_TYPE = 4
AND ROWNUM = 1
START WITH M.ORGID = (SELECT V.PARENTID
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = e.OPERID
AND V.ESOP_TYPE = 4)
AND M.ESOP_TYPE = 4
CONNECT BY PRIOR M.PARENTID = M.ORGID
AND M.ESOP_TYPE = 4) "归属区域名称",
(SELECT v.orgid
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = (SELECT V.PARENTID
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = e.OPERID
AND V.ESOP_TYPE = 4)
AND V.ESOP_TYPE = 4
AND ROWNUM = 1) "归属营业部",
(SELECT V.ORGNAME
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = (SELECT V.PARENTID
FROM tbcs.V_ESOP_ORGANIZATION_M V
WHERE V.ORGID = e.OPERID
AND V.ESOP_TYPE = 4)
AND V.ESOP_TYPE = 4
AND ROWNUM = 1) "归属营业部名称",
a.custid "CRM集团编码",
decode(trunc(b.createdate, 'hh'),
trunc(sysdate, 'hh') - 1 / 24,
'A',
'M') "操作类型",
b.status,
a.UNITKIND,
b.POSTCODE,
a.GROUPSERVICELEVEL,
b.certtype,
b.certid,
a.vocaionkind1,
j.EMAIL
from tbcs.group_customer a,
(select max(cm.email) email, BUSS.CUSTID
from TBCS.CM_CU_RELATIONINFO CM, tbcs.BUSSCUSTSUBS BUSS
where BUSS.STATUS = 'stcmNml'
AND buss.roletype = 'MemberSort0300'
and BUSS.STATUS = 'stcmNml'
AND BUSS.REGION = 12
and cm.mobileno = buss.servnumber
and cm.email IS NOT NULL
group by BUSS.CUSTID) j,
tbcs.customer b,
tbcs.cm_cu_groupcustservchannel d,
tbcs.operator e,
tbcs.organization g,
tbcs.reception h
where j.CUSTID(+) = a.custid
and a.custid = b.custid
and a.custid = d.custid
and d.custmgr = e.OPERID
and e.orgid = g.ORGID
and h.custid = a.custid
and h.recdefid in
('GroupCustInfoLog', 'ManagerAssign', 'BusscustSubsManage')
and h.recdate >= trunc(sysdate, 'hh') - 1 / 24
and h.recdate < trunc(sysdate, 'hh')
and h.region = 12
and d.region = 12
Execution Plan
----------------------------------------------------------
Plan hash value: 3368935094
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 321 | 13092 (5)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION LIST ALL | | 56 | 1400 | 35 (0)| 00:00:01 | 1 | 5 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BUSSCUSTSUBS | 56 | 1400 | 35 (0)| 00:00:01 | 1 | 5 |
|* 4 | INDEX RANGE SCAN | IDX_GRP_BUSSCUSTSUBS_CUSTID | 56 | | 11 (0)| 00:00:01 | 1 | 5 |
|* 5 | COUNT STOPKEY | | | | | | | |
| 6 | PARTITION LIST ALL | | 56 | 1680 | 35 (0)| 00:00:01 | 1 | 5 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BUSSCUSTSUBS | 56 | 1680 | 35 (0)| 00:00:01 | 1 | 5 |
|* 8 | INDEX RANGE SCAN | IDX_GRP_BUSSCUSTSUBS_CUSTID | 56 | | 11 (0)| 00:00:01 | 1 | 5 |
| 9 | COUNT | | | | | | | |
|* 10 | FILTER | | | | | | | |
|* 11 | CONNECT BY WITH FILTERING | | | | | | | |
|* 12 | FILTER | | | | | | | |
|* 13 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 21241 | 497K| 32 (10)| 00:00:01 | | |
|* 14 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 1 | 21 | 32 (10)| 00:00:01 | | |
| 15 | COUNT | | | | | | | |
|* 16 | HASH JOIN | | 228 | 9576 | 98 (12)| 00:00:01 | | |
| 17 | CONNECT BY PUMP | | | | | | | |
|* 18 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 21241 | 497K| 32 (10)| 00:00:01 | | |
| 19 | COUNT | | | | | | | |
|* 20 | FILTER | | | | | | | |
|* 21 | CONNECT BY WITH FILTERING | | | | | | | |
|* 22 | FILTER | | | | | | | |
|* 23 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 21241 | 684K| 32 (10)| 00:00:01 | | |
|* 24 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 1 | 21 | 32 (10)| 00:00:01 | | |
| 25 | COUNT | | | | | | | |
|* 26 | HASH JOIN | | 228 | 11628 | 98 (12)| 00:00:01 | | |
| 27 | CONNECT BY PUMP | | | | | | | |
|* 28 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 21241 | 684K| 32 (10)| 00:00:01 | | |
|* 29 | COUNT STOPKEY | | | | | | | |
|* 30 | FILTER | | | | | | | |
|* 31 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 21241 | 248K| 32 (10)| 00:00:01 | | |
|* 32 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 1 | 21 | 32 (10)| 00:00:01 | | |
|* 33 | COUNT STOPKEY | | | | | | | |
|* 34 | FILTER | | | | | | | |
|* 35 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 21241 | 435K| 32 (10)| 00:00:01 | | |
|* 36 | MAT_VIEW ACCESS FULL | V_ESOP_ORGANIZATION_M | 1 | 21 | 32 (10)| 00:00:01 | | |
| 37 | SORT UNIQUE | | 1 | 321 | 13092 (5)| 00:00:01 | | |
|* 38 | FILTER | | | | | | | |
| 39 | NESTED LOOPS | | 1 | 321 | 12566 (5)| 00:00:01 | | |
| 40 | NESTED LOOPS | | 1 | 321 | 12566 (5)| 00:00:01 | | |
| 41 | NESTED LOOPS SEMI | | 1 | 240 | 12562 (5)| 00:00:01 | | |
| 42 | NESTED LOOPS OUTER | | 1 | 230 | 12562 (5)| 00:00:01 | | |
| 43 | NESTED LOOPS | | 1 | 221 | 12561 (5)| 00:00:01 | | |
| 44 | NESTED LOOPS | | 1 | 182 | 12559 (5)| 00:00:01 | | |
|* 45 | HASH JOIN OUTER | | 1 | 160 | 12558 (5)| 00:00:01 | | |
| 46 | JOIN FILTER CREATE | :BF0000 | 1 | 98 | 11002 (3)| 00:00:01 | | |
| 47 | NESTED LOOPS | | 1 | 98 | 11002 (3)| 00:00:01 | | |
| 48 | NESTED LOOPS | | 1 | 98 | 11002 (3)| 00:00:01 | | |
| 49 | PARTITION RANGE ITERATOR | | 1 | 53 | 10973 (3)| 00:00:01 | KEY | KEY |
| 50 | PARTITION LIST SINGLE | | 1 | 53 | 10973 (3)| 00:00:01 | 1 | 1 |
|* 51 | TABLE ACCESS FULL | RECEPTION | 1 | 53 | 10973 (3)| 00:00:01 | KEY | KEY |
| 52 | PARTITION LIST ALL | | 1 | | 28 (0)| 00:00:01 | 1 | 14 |
|* 53 | INDEX RANGE SCAN | PK_CM_CU_GROUP | 1 | | 28 (0)| 00:00:01 | 1 | 14 |
| 54 | TABLE ACCESS BY LOCAL INDEX ROWID| GROUP_CUSTOMER | 1 | 45 | 29 (0)| 00:00:01 | 1 | 1 |
| 55 | VIEW | | 5709 | 345K| 1556 (16)| 00:00:01 | | |
| 56 | SORT GROUP BY | | 5709 | 351K| 1556 (16)| 00:00:01 | | |
| 57 | JOIN FILTER USE | :BF0000 | 26572 | 1634K| 1553 (15)| 00:00:01 | | |
|* 58 | HASH JOIN | | 26572 | 1634K| 1553 (15)| 00:00:01 | | |
| 59 | PARTITION LIST SINGLE | | 26532 | 1243K| 307 (9)| 00:00:01 | 1 | 1 |
|* 60 | TABLE ACCESS FULL | BUSSCUSTSUBS | 26532 | 1243K| 307 (9)| 00:00:01 | 1 | 1 |
|* 61 | TABLE ACCESS FULL | CM_CU_RELATIONINFO | 66265 | 970K| 1245 (17)| 00:00:01 | | |
| 62 | PARTITION LIST SINGLE | | 1 | 22 | 1 (0)| 00:00:01 | 1 | 1 |
|* 63 | INDEX RANGE SCAN | PK_GROUPCUSTSERV_CUSTMGRRE | 1 | 22 | 1 (0)| 00:00:01 | 1 | 1 |
| 64 | TABLE ACCESS BY INDEX ROWID | T_UCP_STAFFBASICINFO | 1 | 39 | 2 (0)| 00:00:01 | | |
|* 65 | INDEX UNIQUE SCAN | PK_T_UCP_STAFFBASICINFO | 1 | | 1 (0)| 00:00:01 | | |
|* 66 | INDEX RANGE SCAN | PK_UCP_STAFFMAC | 1 | 9 | 1 (0)| 00:00:01 | | |
|* 67 | INDEX UNIQUE SCAN | PK_T_UCP_ORGAINFO | 161K| 1581K| 0 (0)| 00:00:01 | | |
|* 68 | INDEX RANGE SCAN | PK_CM_CU_CUSTOMER | 1 | | 3 (0)| 00:00:01 | | |
| 69 | TABLE ACCESS BY GLOBAL INDEX ROWID | CUSTOMER | 1 | 81 | 4 (0)| 00:00:01 | ROWID | ROWID |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
4 - access("C"."CUSTID"=:B1 AND "C"."STATUS"='stcmNml')
5 - filter(ROWNUM=1)
8 - access("C"."CUSTID"=:B1 AND "C"."STATUS"='stcmNml')
10 - filter("M"."PARENTID"='M'||TO_CHAR("M"."REGION") AND "M"."ESOP_TYPE"=4 AND ROWNUM=1)
11 - access("M"."ORGID"=PRIOR "M"."PARENTID")
filter("M"."ESOP_TYPE"=4)
12 - filter("M"."ORGID"= (SELECT "V"."PARENTID" FROM "TBCS"."V_ESOP_ORGANIZATION_M" "V" WHERE "V"."ORGID"=:B1 AND
"V"."ESOP_TYPE"=4))
13 - filter("M"."ESOP_TYPE"=4)
14 - filter("V"."ORGID"=:B1 AND "V"."ESOP_TYPE"=4)
16 - access("connect$_by$_pump$_007"."PRIOR M.PARENTID "="M"."ORGID")
18 - filter("M"."ESOP_TYPE"=4)
20 - filter("M"."PARENTID"='M'||TO_CHAR("M"."REGION") AND "M"."ESOP_TYPE"=4 AND ROWNUM=1)
21 - access("M"."ORGID"=PRIOR "M"."PARENTID")
filter("M"."ESOP_TYPE"=4)
22 - filter("M"."ORGID"= (SELECT "V"."PARENTID" FROM "TBCS"."V_ESOP_ORGANIZATION_M" "V" WHERE "V"."ORGID"=:B1 AND
"V"."ESOP_TYPE"=4))
23 - filter("M"."ESOP_TYPE"=4)
24 - filter("V"."ORGID"=:B1 AND "V"."ESOP_TYPE"=4)
26 - access("connect$_by$_pump$_016"."PRIOR M.PARENTID "="M"."ORGID")
28 - filter("M"."ESOP_TYPE"=4)
29 - filter(ROWNUM=1)
30 - filter("V"."ORGID"= (SELECT "V"."PARENTID" FROM "TBCS"."V_ESOP_ORGANIZATION_M" "V" WHERE "V"."ORGID"=:B1 AND
"V"."ESOP_TYPE"=4))
31 - filter("V"."ESOP_TYPE"=4)
32 - filter("V"."ORGID"=:B1 AND "V"."ESOP_TYPE"=4)
33 - filter(ROWNUM=1)
34 - filter("V"."ORGID"= (SELECT "V"."PARENTID" FROM "TBCS"."V_ESOP_ORGANIZATION_M" "V" WHERE "V"."ORGID"=:B1 AND
"V"."ESOP_TYPE"=4))
35 - filter("V"."ESOP_TYPE"=4)
36 - filter("V"."ORGID"=:B1 AND "V"."ESOP_TYPE"=4)
38 - filter(TRUNC(SYSDATE@!,'fmhh')>TRUNC(SYSDATE@!,'fmhh')-.0416666666666666666666666666666666666667)
45 - access("J"."CUSTID"(+)="A"."CUSTID")
51 - filter(("H"."RECDEFID"='BusscustSubsManage' OR "H"."RECDEFID"='GroupCustInfoLog' OR "H"."RECDEFID"='ManagerAssign') AND
"H"."RECDATE"<TRUNC(SYSDATE@!,'fmhh') AND "H"."RECDATE">=TRUNC(SYSDATE@!,'fmhh')-.0416666666666666666666666666666666666667)
53 - access("H"."CUSTID"="A"."CUSTID")
58 - access("CM"."MOBILENO"="BUSS"."SERVNUMBER")
60 - filter("BUSS"."ROLETYPE"='MemberSort0300' AND "BUSS"."STATUS"='stcmNml')
61 - filter("CM"."EMAIL" IS NOT NULL)
63 - access("A"."CUSTID"="D"."CUSTID" AND "D"."REGION"=12)
65 - access("D"."CUSTMGR"="BASIC"."STAFFID")
66 - access("BASIC"."STAFFID"="MAC"."STAFFID"(+))
67 - access("BASIC"."ORGAID"="ORG"."ORGAID")
68 - access("A"."CUSTID"="B"."CUSTID")
Statistics
----------------------------------------------------------
64 recursive calls
0 db block gets
133932 consistent gets
4122 physical reads
648 redo size
3594 bytes sent via SQL*Net to client
553 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
44 sorts (memory)
0 sorts (disk)
8 rows processed
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
