SELECT COUNT(1) cnt FROM( SELECT MAX(TT.ORG_NO) 服务区域,
MAX(TT.MR_SECT_NO) ,
MAX(CBDMC) ,
MAX(CBYDM) ,
MAX(CBYXM) ,
TT.CONS_NO ,
MAX(HM) ,
MAX(DZ) ,
MAX(YHFL) ,
ROUND(SUM(DF) / COUNT(*), 2) ,
MAX(DF) ,
MAX(ZWLXX) ,
MAX(DYLXX) ,
DECODE(TT.WARN_MDOE,
'01',
'催缴通知',
'02',
'预收代扣',
'88',
'免发短信') ,tt.pd_value
FROM (SELECT B.ORG_NO,
B.YM,
B.MR_SECT_NO,
(SELECT RR.NAME FROM R_SECT RR WHERE MR_SECT_NO = B.MR_SECT_NO) CBDMC,
(SELECT R.OPERATOR_NO
FROM R_OPER_ACTIVITY R
WHERE R.MR_SECT_NO = B.MR_SECT_NO
AND R.ACT_CODE = '03') CBYDM,
(SELECT USER_TITLE
FROM AMBER.INDY_USER
WHERE USER_NAME = (SELECT R.OPERATOR_NO
FROM R_OPER_ACTIVITY R
WHERE R.MR_SECT_NO = B.MR_SECT_NO
AND R.ACT_CODE = '03')) CBYXM,
B.CONS_NO,
MAX(B.CONS_NAME) HM,
MAX(B.ELEC_ADDR) DZ,
MAX(B.CONS_SORT_CODE) YHFL,
SUM(A.T_AMT) DF,
MAX((SELECT MAX(A1.MOBILE)
FROM SGPM.C_CONTACT A1, SGPM.C_CUST_CONS_RELA A2
WHERE A1.CUST_ID = A2.CUST_ID
AND A2.ORG_NO = B.ORG_NO
AND A2.CONS_ID = B.CONS_ID
AND A2.RELA_TYPE LIKE '%02%')) ZWLXX,
MAX((SELECT MAX(PHONE)
FROM SGPM.A_CONS_SUB S
WHERE S.CONS_NO = B.CONS_NO
AND S.SUB_ITEM = '01')) DYLXX,
C.WARN_MDOE,c.pd_value
FROM ARC_E_CONS_PRC_AMT A, ARC_E_CONS_SNAP B, SGPM.C_RCA_CONS C
WHERE B.YM BETWEEN '20190501' AND '20200430'
AND B.ORG_NO LIKE '3340660' || '%'
AND B.MR_SECT_NO BETWEEN '334066001212001' AND '334066001212020'
AND A.CALC_ID = B.CALC_ID
AND A.ORG_NO = B.ORG_NO
AND A.YM = B.YM
AND A.PRC_CODE NOT IN ('9999',
'9997',
'9998',
'1180',
'1110',
'1111',
'9111',
'9110')
AND B.CONS_SORT_CODE != '01'
AND C.CONS_NO = B.CONS_NO
AND B.CONS_ID = C.CONS_ID
AND C.ORG_NO LIKE '3340660' || '%'
AND C.RCA_FLAG = '1'
AND (c.WARN_MDOE= null OR null IS NULL)
GROUP BY B.ORG_NO,
B.MR_SECT_NO,
B.YM,
B.CONS_NO,
B.CONS_ID,
C.WARN_MDOE,c.pd_value
HAVING SUM(A.T_AMT) <> '0') TT
GROUP BY TT.CONS_NO, TT.WARN_MDOE,tt.pd_value);
SQL Monitoring Report
Global Information
------------------------------
Status : EXECUTING
Instance ID : 4
Session : SGPM (3062:34431)
SQL ID : 4sy2hm59ttc05
SQL Execution ID : 67109425
Execution Started : 06/22/2020 10:07:42
First Refresh Time : 06/22/2020 10:07:58
Last Refresh Time : 06/22/2020 10:08:28
Duration : 47s
Module/Action : .SelfCustQueryImplService.getLSQueryDataListPage/TH63-@dyxepm1_1:0622100635
Service : app1
Program : JDBC Thin Client
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :1 | 1 | VARCHAR2(32) | 20190501 |
| :2 | 2 | VARCHAR2(32) | 20200430 |
| :3 | 3 | VARCHAR2(128) | 3340660 |
| :4 | 4 | VARCHAR2(128) | 3xxxxxx0 |
| :5 | 5 | VARCHAR2(128) | 3xxxxxx0 |
| :6 | 6 | VARCHAR2(128) | 33xxxx0 |
| :7 | 7 | VARCHAR2(32) | |
| :8 | 8 | VARCHAR2(32) | |
| :9 | 9 | NUMBER | 2650 |
| :10 | 10 | NUMBER | 1300 |
========================================================================================================================
Global Stats
==================================================================
| Elapsed | Cpu | Concurrency | Cluster | Other | Buffer |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets |
==================================================================
| 46 | 14 | 0.00 | 0.01 | 31 | 6M |
==================================================================
SQL Plan Monitoring Details (Plan Hash Value=317986502)
=========================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | | (%) | (# samples) |
=========================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | R_SECT | 1 | 1 | | | | | | | |
| 2 | INDEX UNIQUE SCAN | R_SECT_PK | 1 | 1 | | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | R_OPER_ACTIVITY | 1 | 1 | | | | | | | |
| 4 | INDEX RANGE SCAN | R_SECT_R_OPER_ACTIVITY_FK | 11 | 1 | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | INDY_USER | 1 | 1 | | | | | | | |
| 6 | INDEX UNIQUE SCAN | UK_USER_NAME | 1 | 1 | | | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | R_OPER_ACTIVITY | 1 | 1 | | | | | | | |
| 8 | INDEX RANGE SCAN | R_SECT_R_OPER_ACTIVITY_FK | 11 | 1 | | | | | | | |
| -> 9 | SORT AGGREGATE | | 1 | | 31 | +16 | 17171 | 17171 | | | |
| -> 10 | NESTED LOOPS | | 1 | 2 | 31 | +16 | 17171 | 18019 | | | |
| -> 11 | NESTED LOOPS | | 1 | 2 | 31 | +16 | 17171 | 18019 | | | |
| -> 12 | PARTITION RANGE SINGLE | | 1 | 1 | 31 | +16 | 17171 | 18012 | | | |
| -> 13 | TABLE ACCESS BY LOCAL INDEX ROWID | C_CUST_CONS_RELA | 1 | 1 | 31 | +16 | 17171 | 18012 | | | |
| -> 14 | INDEX RANGE SCAN | IDX_C_CUST_CONS_RELA_CONS_ID | 1 | 1 | 31 | +16 | 17171 | 33069 | | | |
| -> 15 | INDEX RANGE SCAN | C_CUST_C_CONTACT_FK | 1 | 1 | 31 | +16 | 18012 | 18019 | | | |
| -> 16 | TABLE ACCESS BY GLOBAL INDEX ROWID | C_CONTACT | 1 | 1 | 31 | +16 | 18019 | 18019 | | | |
| -> 17 | SORT AGGREGATE | | 1 | | 31 | +16 | 19523 | 19523 | | | |
| 18 | PARTITION RANGE ALL | | 1 | 32 | 39 | +8 | 19523 | 15462 | | 6.52 | Cpu (3) |
| 19 | TABLE ACCESS BY LOCAL INDEX ROWID | A_CONS_SUB | 1 | 32 | 43 | +4 | 2M | 15462 | | 13.04 | Cpu (6) |
| -> 20 | INDEX RANGE SCAN | IDX_A_CONS_SUB_CONSP | 3 | 31 | 46 | +2 | 2M | 55226 | | 69.57 | Cpu (32) |
| 21 | VIEW | | 1 | 2525 | | | 1 | | | | |
| 22 | COUNT STOPKEY | | | | | | 1 | | | | |
| 23 | VIEW | | 1 | 2525 | | | 1 | | | | |
| 24 | SORT GROUP BY STOPKEY | | 1 | 2525 | | | 1 | | | | |
| 25 | VIEW | | 1 | 2525 | | | 1 | | | | |
| 26 | FILTER | | | | | | 1 | | | | |
| 27 | HASH GROUP BY | | 1 | 2525 | 31 | +16 | 1 | 0 | 61M | 2.17 | Cpu (1) |
| -> 28 | FILTER | | | | 31 | +16 | 1 | 25305 | | | |
| -> 29 | NESTED LOOPS | | 1 | 2524 | 31 | +16 | 1 | 25305 | | | |
| -> 30 | NESTED LOOPS | | 1 | 2524 | 31 | +16 | 1 | 25305 | | | |
| -> 31 | NESTED LOOPS | | 1 | 2523 | 31 | +16 | 1 | 20379 | | | |
| -> 32 | PARTITION RANGE ITERATOR | | 1 | 2522 | 31 | +16 | 1 | 22004 | | | |
| -> 33 | PARTITION LIST ITERATOR | | 1 | 2522 | 31 | +16 | 2 | 22004 | | | |
| -> 34 | TABLE ACCESS BY LOCAL INDEX ROWID | ARC_E_CONS_SNAP | 1 | 2522 | 31 | +16 | 19 | 22004 | | | |
| 35 | INDEX RANGE SCAN | LOC_ARC_E_CONS_SNAP_SECT_NO | 237 | 2520 | 31 | +16 | 19 | 22004 | | 2.17 | Cpu (1) |
| -> 36 | TABLE ACCESS BY GLOBAL INDEX ROWID | C_RCA_CONS | 1 | 1 | 31 | +16 | 22004 | 20379 | | | |
| -> 37 | INDEX UNIQUE SCAN | PK_C_RCA_CONS | 1 | 1 | 31 | +16 | 22004 | 20520 | | | |
| 38 | PARTITION RANGE AND | | 1 | 1 | 31 | +16 | 20379 | 25305 | | 4.35 | Cpu (2) |
| 39 | PARTITION LIST AND | | 1 | 1 | 46 | +1 | 20379 | 25305 | | 2.17 | Cpu (1) |
| -> 40 | INDEX RANGE SCAN | IDX_ARC_E_CONS_PRC_AMT_CALC_ID | 1 | 1 | 31 | +16 | 20379 | 25305 | | | |
| -> 41 | TABLE ACCESS BY LOCAL INDEX ROWID | ARC_E_CONS_PRC_AMT | 1 | 1 | 31 | +16 | 25305 | 25305 | | | |
=========================================================================================================================================================================================
从执行计划上很明显能看出
| -> 17 | SORT AGGREGATE | | 1 | | 31 | +16 | 19523 | 19523 | | | |
| 18 | PARTITION RANGE ALL | | 1 | 32 | 39 | +8 | 19523 | 15462 | | 6.52 | Cpu (3) |
| 19 | TABLE ACCESS BY LOCAL INDEX ROWID | A_CONS_SUB | 1 | 32 | 43 | +4 | 2M | 15462 | | 13.04 | Cpu (6) |
| -> 20 | INDEX RANGE SCAN | IDX_A_CONS_SUB_CONSP | 3 | 31 | 46 | +2 | 2M | 55226 | | 69.57 | Cpu (32) |
该步骤消耗大量资源和时间。
对应的是sql中的标量子查询部分
MAX((SELECT MAX(PHONE)
FROM SGPM.A_CONS_SUB S
WHERE S.CONS_NO = B.CONS_NO
AND S.SUB_ITEM = '01')) DYLXX,
A_CONS_SUB表是按照org_no字段做的分区表。
表数据量7千万。distinct cons_no有3千万（选择性ok）
PARTITION RANGE ALL 范围分区全扫面。
很明显是使用分区表未加分区条件。
修改后：
MAX((SELECT MAX(PHONE)
FROM SGPM.A_CONS_SUB S
WHERE S.CONS_NO = B.CONS_NO
AND S.ORG_NO = B.ORG_NO
AND S.SUB_ITEM = '01')) DYLXX,
4秒内出结果。