1.SQL执行9分钟
CTPROD@coredb1> SELECT DISTINCT m.policyNo,
2 m.endorSeqNo,
3 ap.insuredType applicantType,
4 ap.insuredCode applicantCode,
5 ap.insuredName applicantName,
6 ap.identifyType applicantIdentifyType,
7 ap.identifyNumber applicantIdentifyNumber,
8 ap.sex applicantSex,
9 ap.birthDate applicantBirthDate,
10 r.planCode,
11 r.riskCode,
12 r.subPolicyNo,
13 r.startDate,
14 r.endDate,
15 r.compensationType,
16 r.retroactiveStartDate,
17 r.discoverEndDate,
18 a.itemNo,
19 '' projectCode,
20 '' itemProvinceCode,
21 '' itemCityCode,
22 '' itemDistrictCode,
23 '' village,
24 '' district,
25 '' situation,
26 (select to_char(WMSYS.WM_CONCAT(t.projectname))
27 from gupolicycopyitemacci t
28 where a.policyno = t.policyno
29 and a.endorseqno = t.endorseqno
30 and a.itemno = t.itemno) as itemNameDisplay,
31 k.riskCName as riskName,
32 m.companyCode,
33 c.companyCname,
34 m.channeltip,
35 n.codecname,
36 m.salesmanCode,
37 u.usercname,
38 rd.nominativeInd
39 FROM GuPolicyCopyMain m,
40 GuPolicyCopyRelatedParty ap,
41 GuPolicyCopyRiskDynamic rd,
42 GuPolicyCopyItemAcciList i,
43 GuPolicyCopyRisk r,
44 GuPolicyCopyItemMain a,
45 GuPolicyCopyEndorHead h,
46 GgRisk k,
47 ggCompany c,
48 ggUser u,
49 GgCode n
50 WHERE m.policyNo = r.policyNo
51 AND m.companyCode = c.companyCode
52 AND r.policyNo = a.policyNo
53 AND r.endorSeqNo = a.endorSeqNo
54 AND r.riskCode = a.riskCode
55 AND r.plancode = a.plancode
56 AND m.policyNo = ap.policyNo
57 AND m.endorSeqNo = r.endorSeqNo
58 AND r.policyno = rd.policyno
59 AND r.riskcode = rd.riskcode
60 AND r.endorseqno = rd.endorseqno
61 AND m.policyNo = h.policyNo
62 AND m.endorSeqNo = h.endorSeqNo
63 AND r.riskCode = k.riskCode
64 AND a.policyNo = i.policyNo(+)
65 AND a.endorSeqNo = i.endorSeqNo(+)
66 AND a.riskCode = i.riskCode(+)
67 AND a.plancode = i.plancode(+)
68 AND a.itemno = i.itemno(+)
69 AND n.codecode = m.channeltip
70 AND n.codetype = 'UnderWriteChannelTip'
71 AND u.usercode = m.salesmanCode
72 AND k.opencoverind <> '1'
73 AND k.riskClass in ('11', '10')
74 AND (TIMESTAMP'2023-12-12 12:11:13.000' between r.startDate and
75 r.endDate)
76 AND h.endorSeqNo =
77 (SELECT MAX(endorSeqNo)
78 FROM GuPolicyCopyEndorHead t
79 WHERE t.policyNo = h.policyNo
80 AND t.validDate <= TIMESTAMP'2023-12-12 12:11:13.000')
81 and ((m.cancelind <> '1' or m.cancelind is null) and
82 (m.surrenderind <> '1' or m.surrenderind is null))
83 AND (r.riskCode = '1137')
84 and (r.policyNo = '6050400113720230000119' or
85 r.subPolicyNo = '6050400113720230000119')
86 AND (k.riskClass = '11')
87 order by r.startDate Desc;
执行:9分钟。
2.查看执行计划
select * from table(dbms_xplan.display_cursor('2gs6gb92zcb51',null,null));
--逻辑读高：536959137*8=4T
--cost小:00:00:01
Execution Plan
----------------------------------------------------------
Plan hash value: 1977746091
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 538 | 68 (3)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 46 | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYITEMACCI | 1 | 46 | 4 (0)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | ID_PC_ITEMACCI_REF_ITEMMAIN | 1 | | 3 (0)| 00:00:01 | | |
| 4 | SORT ORDER BY | | 1 | 538 | 68 (3)| 00:00:01 | | |
| 5 | HASH UNIQUE | | 1 | 538 | 67 (2)| 00:00:01 | | |
| 6 | CONCATENATION | | | | | | | |
| 7 | NESTED LOOPS | | 1 | 538 | 33 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 1 | 535 | 28 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 450 | 24 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS | | 1 | 396 | 23 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS | | 1 | 377 | 22 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 350 | 20 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1 | 300 | 17 (0)| 00:00:01 | | |
| 14 | NESTED LOOPS | | 1 | 240 | 14 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS OUTER | | 1 | 206 | 11 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 165 | 8 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS | | 1 | 124 | 5 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | GGRISK | 1 | 42 | 1 (0)| 00:00:01 | | |
|* 19 | INDEX UNIQUE SCAN | PK_GGRISK | 1 | | 0 (0)| 00:00:01 | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISK | 1 | 82 | 4 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | IDX_GUPOLICYCOPYRISK_SPNO | 1 | | 3 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN | PK_GUPOLICYCOP6 | 1 | 41 | 3 (0)| 00:00:01 | | |
|* 23 | INDEX RANGE SCAN | PK_GUPOLICYCO17 | 1 | 41 | 3 (0)| 00:00:01 | | |
| 24 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISKDYNAMIC | 1 | 34 | 3 (0)| 00:00:01 | | |
|* 25 | INDEX RANGE SCAN | PK_GUPOLICYCOPYRISKDYNAMIC | 1 | | 2 (0)| 00:00:01 | | |
|* 26 | TABLE ACCESS BY GLOBAL INDEX ROWID| GUPOLICYCOPYMAIN | 1 | 60 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 27 | INDEX UNIQUE SCAN | PK_GUPOLICYCO21 | 1 | | 2 (0)| 00:00:01 | | |
| 28 | TABLE ACCESS BY INDEX ROWID | GGCODE | 1 | 50 | 3 (0)| 00:00:01 | | |
|* 29 | INDEX RANGE SCAN | PK_GGCODE | 1 | | 2 (0)| 00:00:01 | | |
|* 30 | INDEX UNIQUE SCAN | PK_PHEAD2 | 1 | 27 | 2 (0)| 00:00:01 | | |
| 31 | TABLE ACCESS BY INDEX ROWID | GGUSER | 1 | 19 | 1 (0)| 00:00:01 | | |
|* 32 | INDEX UNIQUE SCAN | PK_GGUSER | 1 | | 0 (0)| 00:00:01 | | |
| 33 | TABLE ACCESS BY INDEX ROWID | GGCOMPANY | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 34 | INDEX UNIQUE SCAN | PK_GGCOMPANY | 1 | | 0 (0)| 00:00:01 | | |
| 35 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRELATEDPARTY | 1 | 85 | 4 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN | PK_CINSURED4 | 1 | | 3 (0)| 00:00:01 | | |
|* 37 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 3 | 5 (0)| 00:00:01 | | |
|* 38 | FILTER | | | | | | | |
| 39 | SORT AGGREGATE | | 1 | 35 | | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYENDORHEAD | 1 | 35 | 5 (0)| 00:00:01 | | |
|* 41 | INDEX RANGE SCAN | PK_PHEAD2 | 1 | | 4 (0)| 00:00:01 | | |
| 42 | NESTED LOOPS | | 1 | 538 | 33 (0)| 00:00:01 | | |
| 43 | NESTED LOOPS | | 1 | 535 | 28 (0)| 00:00:01 | | |
| 44 | NESTED LOOPS | | 1 | 450 | 24 (0)| 00:00:01 | | |
| 45 | NESTED LOOPS | | 1 | 396 | 23 (0)| 00:00:01 | | |
| 46 | NESTED LOOPS | | 1 | 377 | 22 (0)| 00:00:01 | | |
| 47 | NESTED LOOPS | | 1 | 350 | 20 (0)| 00:00:01 | | |
| 48 | NESTED LOOPS | | 1 | 300 | 17 (0)| 00:00:01 | | |
| 49 | NESTED LOOPS | | 1 | 240 | 14 (0)| 00:00:01 | | |
| 50 | NESTED LOOPS OUTER | | 1 | 206 | 11 (0)| 00:00:01 | | |
| 51 | NESTED LOOPS | | 1 | 165 | 8 (0)| 00:00:01 | | |
| 52 | NESTED LOOPS | | 1 | 124 | 5 (0)| 00:00:01 | | |
|* 53 | TABLE ACCESS BY INDEX ROWID | GGRISK | 1 | 42 | 1 (0)| 00:00:01 | | |
|* 54 | INDEX UNIQUE SCAN | PK_GGRISK | 1 | | 0 (0)| 00:00:01 | | |
|* 55 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISK | 1 | 82 | 4 (0)| 00:00:01 | | |
|* 56 | INDEX RANGE SCAN | PK_GUPOLICYCOPY | 1 | | 3 (0)| 00:00:01 | | |
|* 57 | INDEX RANGE SCAN | PK_GUPOLICYCOP6 | 1 | 41 | 3 (0)| 00:00:01 | | |
|* 58 | INDEX RANGE SCAN | PK_GUPOLICYCO17 | 1 | 41 | 3 (0)| 00:00:01 | | |
| 59 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISKDYNAMIC | 1 | 34 | 3 (0)| 00:00:01 | | |
|* 60 | INDEX RANGE SCAN | PK_GUPOLICYCOPYRISKDYNAMIC | 1 | | 2 (0)| 00:00:01 | | |
|* 61 | TABLE ACCESS BY GLOBAL INDEX ROWID| GUPOLICYCOPYMAIN | 1 | 60 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 62 | INDEX UNIQUE SCAN | PK_GUPOLICYCO21 | 1 | | 2 (0)| 00:00:01 | | |
| 63 | TABLE ACCESS BY INDEX ROWID | GGCODE | 1 | 50 | 3 (0)| 00:00:01 | | |
|* 64 | INDEX RANGE SCAN | PK_GGCODE | 1 | | 2 (0)| 00:00:01 | | |
|* 65 | INDEX UNIQUE SCAN | PK_PHEAD2 | 1 | 27 | 2 (0)| 00:00:01 | | |
| 66 | TABLE ACCESS BY INDEX ROWID | GGUSER | 1 | 19 | 1 (0)| 00:00:01 | | |
|* 67 | INDEX UNIQUE SCAN | PK_GGUSER | 1 | | 0 (0)| 00:00:01 | | |
| 68 | TABLE ACCESS BY INDEX ROWID | GGCOMPANY | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 69 | INDEX UNIQUE SCAN | PK_GGCOMPANY | 1 | | 0 (0)| 00:00:01 | | |
| 70 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRELATEDPARTY | 1 | 85 | 4 (0)| 00:00:01 | | |
|* 71 | INDEX RANGE SCAN | PK_CINSURED4 | 1 | | 3 (0)| 00:00:01 | | |
|* 72 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 3 | 5 (0)| 00:00:01 | | |
|* 73 | FILTER | | | | | | | |
| 74 | SORT AGGREGATE | | 1 | 35 | | | | |
|* 75 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYENDORHEAD | 1 | 35 | 5 (0)| 00:00:01 | | |
|* 76 | INDEX RANGE SCAN | PK_PHEAD2 | 1 | | 4 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
8 recursive calls
34 db block gets
536959137 consistent gets --逻辑读较高。
0 physical reads
0 redo size
3595 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
发现执行计划中连接谓词推入。其他看不出啥。
3.查看表的统计信息时间
发现表没有收集统计信息。
--收集相关表的统计信息。
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GuPolicyCopyMain');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GuPolicyCopyRelatedParty');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GuPolicyCopyRiskDynamic');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GuPolicyCopyItemAcciList');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GuPolicyCopyRisk');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GuPolicyCopyItemMain');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GuPolicyCopyEndorHead');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GgRisk');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'ggCompany');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'ggUser');
EXEC DBMS_STATS.gather_table_stats('ctprod', 'GgCode');
4.重新执行
SELECT DISTINCT m.policyNo,
m.endorSeqNo,
ap.insuredType applicantType,
ap.insuredCode applicantCode,
ap.insuredName applicantName,
ap.identifyType applicantIdentifyType,
ap.identifyNumber applicantIdentifyNumber,
ap.sex applicantSex,
ap.birthDate applicantBirthDate,
r.planCode,
r.riskCode,
r.subPolicyNo,
r.startDate,
r.endDate,
r.compensationType,
r.retroactiveStartDate,
r.discoverEndDate,
a.itemNo,
'' projectCode,
'' itemProvinceCode,
'' itemCityCode,
'' itemDistrictCode,
'' village,
'' district,
'' situation,
(select to_char(WMSYS.WM_CONCAT(t.projectname))
from gupolicycopyitemacci t
where a.policyno = t.policyno
and a.endorseqno = t.endorseqno
and a.itemno = t.itemno) as itemNameDisplay,
k.riskCName as riskName,
m.companyCode,
c.companyCname,
m.channeltip,
n.codecname,
m.salesmanCode,
u.usercname,
rd.nominativeInd
FROM GuPolicyCopyMain m,
GuPolicyCopyRelatedParty ap,
GuPolicyCopyRiskDynamic rd,
GuPolicyCopyItemAcciList i,
GuPolicyCopyRisk r,
GuPolicyCopyItemMain a,
GuPolicyCopyEndorHead h,
GgRisk k,
ggCompany c,
ggUser u,
GgCode n
WHERE m.policyNo = r.policyNo
AND m.companyCode = c.companyCode
AND r.policyNo = a.policyNo
AND r.endorSeqNo = a.endorSeqNo
AND r.riskCode = a.riskCode
AND r.plancode = a.plancode
AND m.policyNo = ap.policyNo
AND m.endorSeqNo = r.endorSeqNo
AND r.policyno = rd.policyno
AND r.riskcode = rd.riskcode
AND r.endorseqno = rd.endorseqno
AND m.policyNo = h.policyNo
AND m.endorSeqNo = h.endorSeqNo
AND r.riskCode = k.riskCode
AND a.policyNo = i.policyNo(+)
AND a.endorSeqNo = i.endorSeqNo(+)
AND a.riskCode = i.riskCode(+)
AND a.plancode = i.plancode(+)
AND a.itemno = i.itemno(+)
AND n.codecode = m.channeltip
AND n.codetype = 'UnderWriteChannelTip'
AND u.usercode = m.salesmanCode
AND k.opencoverind <> '1'
AND k.riskClass in ('11', '10')
AND (TIMESTAMP'2023-12-12 12:11:13.000' between r.startDate and
r.endDate)
AND h.endorSeqNo =
(SELECT MAX(endorSeqNo)
FROM GuPolicyCopyEndorHead t
WHERE t.policyNo = h.policyNo
AND t.validDate <= TIMESTAMP'2023-12-12 12:11:13.000')
and ((m.cancelind <> '1' or m.cancelind is null) and
(m.surrenderind <> '1' or m.surrenderind is null))
AND (r.riskCode = '1137')
and (r.policyNo = '6050400113720230000119' or
r.subPolicyNo = '6050400113720230000119')
AND (k.riskClass = '11')
order by r.startDate Desc;
--执行时间4分13s;
Elapsed: 00:04:13.05
Inst: 1 Child: 0 Plan hash value: 1977746091
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68 (100)| | | | | | |
| 1 | SORT AGGREGATE | | 1 | 46 | | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYITEMACCI | 1 | 46 | 4 (0)| 00:00:01 | | | | | |
|* 3 | INDEX RANGE SCAN | ID_PC_ITEMACCI_REF_ITEMMAIN | 1 | | 3 (0)| 00:00:01 | | | | | |
| 4 | SORT ORDER BY | | 1 | 538 | 68 (3)| 00:00:01 | | | 2048 | 2048 | 2048 (0)|
| 5 | HASH UNIQUE | | 1 | 538 | 67 (2)| 00:00:01 | | | 47M| 3903K| 609K (0)|
| 6 | CONCATENATION | | | | | | | | | | |
| 7 | NESTED LOOPS | | 1 | 538 | 33 (0)| 00:00:01 | | | | | |
| 8 | NESTED LOOPS | | 1 | 535 | 28 (0)| 00:00:01 | | | | | |
| 9 | NESTED LOOPS | | 1 | 450 | 24 (0)| 00:00:01 | | | | | |
| 10 | NESTED LOOPS | | 1 | 396 | 23 (0)| 00:00:01 | | | | | |
| 11 | NESTED LOOPS | | 1 | 377 | 22 (0)| 00:00:01 | | | | | |
| 12 | NESTED LOOPS | | 1 | 350 | 20 (0)| 00:00:01 | | | | | |
| 13 | NESTED LOOPS | | 1 | 300 | 17 (0)| 00:00:01 | | | | | |
| 14 | NESTED LOOPS | | 1 | 240 | 14 (0)| 00:00:01 | | | | | |
| 15 | NESTED LOOPS OUTER | | 1 | 206 | 11 (0)| 00:00:01 | | | | | |
| 16 | NESTED LOOPS | | 1 | 165 | 8 (0)| 00:00:01 | | | | | |
| 17 | NESTED LOOPS | | 1 | 124 | 5 (0)| 00:00:01 | | | | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | GGRISK | 1 | 42 | 1 (0)| 00:00:01 | | | | | |
|* 19 | INDEX UNIQUE SCAN | PK_GGRISK | 1 | | 0 (0)| | | | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISK | 1 | 82 | 4 (0)| 00:00:01 | | | | | |
|* 21 | INDEX RANGE SCAN | IDX_GUPOLICYCOPYRISK_SPNO | 1 | | 3 (0)| 00:00:01 | | | | | |
|* 22 | INDEX RANGE SCAN | PK_GUPOLICYCOP6 | 1 | 41 | 3 (0)| 00:00:01 | | | | | |
|* 23 | INDEX RANGE SCAN | PK_GUPOLICYCO17 | 1 | 41 | 3 (0)| 00:00:01 | | | | | |
| 24 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISKDYNAMIC | 1 | 34 | 3 (0)| 00:00:01 | | | | | |
|* 25 | INDEX RANGE SCAN | PK_GUPOLICYCOPYRISKDYNAMIC | 1 | | 2 (0)| 00:00:01 | | | | | |
|* 26 | TABLE ACCESS BY GLOBAL INDEX ROWID| GUPOLICYCOPYMAIN | 1 | 60 | 3 (0)| 00:00:01 | ROWID | ROWID | | | |
|* 27 | INDEX UNIQUE SCAN | PK_GUPOLICYCO21 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | GGCODE | 1 | 50 | 3 (0)| 00:00:01 | | | | | |
|* 29 | INDEX RANGE SCAN | PK_GGCODE | 1 | | 2 (0)| 00:00:01 | | | | | |
|* 30 | INDEX UNIQUE SCAN | PK_PHEAD2 | 1 | 27 | 2 (0)| 00:00:01 | | | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | GGUSER | 1 | 19 | 1 (0)| 00:00:01 | | | | | |
|* 32 | INDEX UNIQUE SCAN | PK_GGUSER | 1 | | 0 (0)| | | | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | GGCOMPANY | 1 | 54 | 1 (0)| 00:00:01 | | | | | |
|* 34 | INDEX UNIQUE SCAN | PK_GGCOMPANY | 1 | | 0 (0)| | | | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRELATEDPARTY | 1 | 85 | 4 (0)| 00:00:01 | | | | | |
|* 36 | INDEX RANGE SCAN | PK_CINSURED4 | 1 | | 3 (0)| 00:00:01 | | | | | |
|* 37 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 3 | 5 (0)| 00:00:01 | | | | | |
|* 38 | FILTER | | | | | | | | | | |
| 39 | SORT AGGREGATE | | 1 | 35 | | | | | | | |
|* 40 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYENDORHEAD | 1 | 35 | 5 (0)| 00:00:01 | | | | | |
|* 41 | INDEX RANGE SCAN | PK_PHEAD2 | 1 | | 4 (0)| 00:00:01 | | | | | |
| 42 | NESTED LOOPS | | 1 | 538 | 33 (0)| 00:00:01 | | | | | |
| 43 | NESTED LOOPS | | 1 | 535 | 28 (0)| 00:00:01 | | | | | |
| 44 | NESTED LOOPS | | 1 | 450 | 24 (0)| 00:00:01 | | | | | |
| 45 | NESTED LOOPS | | 1 | 396 | 23 (0)| 00:00:01 | | | | | |
| 46 | NESTED LOOPS | | 1 | 377 | 22 (0)| 00:00:01 | | | | | |
| 47 | NESTED LOOPS | | 1 | 350 | 20 (0)| 00:00:01 | | | | | |
| 48 | NESTED LOOPS | | 1 | 300 | 17 (0)| 00:00:01 | | | | | |
| 49 | NESTED LOOPS | | 1 | 240 | 14 (0)| 00:00:01 | | | | | |
| 50 | NESTED LOOPS OUTER | | 1 | 206 | 11 (0)| 00:00:01 | | | | | |
| 51 | NESTED LOOPS | | 1 | 165 | 8 (0)| 00:00:01 | | | | | |
| 52 | NESTED LOOPS | | 1 | 124 | 5 (0)| 00:00:01 | | | | | |
|* 53 | TABLE ACCESS BY INDEX ROWID | GGRISK | 1 | 42 | 1 (0)| 00:00:01 | | | | | |
|* 54 | INDEX UNIQUE SCAN | PK_GGRISK | 1 | | 0 (0)| | | | | | |
|* 55 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISK | 1 | 82 | 4 (0)| 00:00:01 | | | | | |
|* 56 | INDEX RANGE SCAN | PK_GUPOLICYCOPY | 1 | | 3 (0)| 00:00:01 | | | | | |
|* 57 | INDEX RANGE SCAN | PK_GUPOLICYCOP6 | 1 | 41 | 3 (0)| 00:00:01 | | | | | |
|* 58 | INDEX RANGE SCAN | PK_GUPOLICYCO17 | 1 | 41 | 3 (0)| 00:00:01 | | | | | |
| 59 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISKDYNAMIC | 1 | 34 | 3 (0)| 00:00:01 | | | | | |
|* 60 | INDEX RANGE SCAN | PK_GUPOLICYCOPYRISKDYNAMIC | 1 | | 2 (0)| 00:00:01 | | | | | |
|* 61 | TABLE ACCESS BY GLOBAL INDEX ROWID| GUPOLICYCOPYMAIN | 1 | 60 | 3 (0)| 00:00:01 | ROWID | ROWID | | | |
|* 62 | INDEX UNIQUE SCAN | PK_GUPOLICYCO21 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 63 | TABLE ACCESS BY INDEX ROWID | GGCODE | 1 | 50 | 3 (0)| 00:00:01 | | | | | |
|* 64 | INDEX RANGE SCAN | PK_GGCODE | 1 | | 2 (0)| 00:00:01 | | | | | |
|* 65 | INDEX UNIQUE SCAN | PK_PHEAD2 | 1 | 27 | 2 (0)| 00:00:01 | | | | | |
| 66 | TABLE ACCESS BY INDEX ROWID | GGUSER | 1 | 19 | 1 (0)| 00:00:01 | | | | | |
|* 67 | INDEX UNIQUE SCAN | PK_GGUSER | 1 | | 0 (0)| | | | | | |
| 68 | TABLE ACCESS BY INDEX ROWID | GGCOMPANY | 1 | 54 | 1 (0)| 00:00:01 | | | | | |
|* 69 | INDEX UNIQUE SCAN | PK_GGCOMPANY | 1 | | 0 (0)| | | | | | |
| 70 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRELATEDPARTY | 1 | 85 | 4 (0)| 00:00:01 | | | | | |
|* 71 | INDEX RANGE SCAN | PK_CINSURED4 | 1 | | 3 (0)| 00:00:01 | | | | | |
|* 72 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 3 | 5 (0)| 00:00:01 | | | | | |
|* 73 | FILTER | | | | | | | | | | |
| 74 | SORT AGGREGATE | | 1 | 35 | | | | | | | |
|* 75 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYENDORHEAD | 1 | 35 | 5 (0)| 00:00:01 | | | | | |
|* 76 | INDEX RANGE SCAN | PK_PHEAD2 | 1 | | 4 (0)| 00:00:01 | | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
执行时间:4分13s，同时还有两个连接谓词推入的执行计划。且在这步和下部的执行计划消耗的CPU高。
5.什么是连接谓词推入
连接谓词推入,是优化器处理带视图的目标SQL的另外一种优化手段，它是指虽然优化器
还是会把该SQL中视图的定义SQL语句当作一个独立的处理单元单独执行,但此时优化器
会把原本处于该视图查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部,
这样做是为了能使用上该视图内部相关基表上的索引,进而能走出基于索引的嵌套循环连接。
连接谓词推入使优化器在选择目标SQL的执行计划时多出了走基于索引的嵌套循环连接
这种选择,这就增加了走出更高效执行计划的可能性。
但是需要注意,连接谓词推入所带来的基于索引的嵌套循环并不一定能走出更高效的执行计划，
因为当做了连接谓词推入后，原目标SQL的视图中就和外部查询产生了关联,同时Oracle又必须
将该视图的定义SQL语句当作一个独立的处理单元来单独执行，这也就意味着对于外部查询所
在结果集中的每一行记录，上述视图的定义SQL语句都得单独执行一次,这样一旦外部查询所在
结果集的cardinality比较大的话,即便在执行上述视图的定义SQL语句时能用上索引,
整个SQL的执行效率也不一定会比不做连接谓词推入的时的哈希连接或排序合并连接高。
所以,oracle在做连接谓词推入时会考虑成本,只有当经过连接谓词推入后走嵌套循环连接
的等待改写SQL的成本值小于原SQL的成本值时，Oracle才会对目标SQL做连接谓词推入。
6.是否可以关闭连接谓词推入
第一个连接谓词推入的语句
(select to_char(WMSYS.WM_CONCAT(t.projectname))
from gupolicycopyitemacci t
where a.policyno = t.policyno
and a.endorseqno = t.endorseqno
and a.itemno = t.itemno) as itemNameDisplay;
--视图外部是A表,查询A表有多少数据。
select count(a.policyNo) FROM GuPolicyCopyMain m,
GuPolicyCopyRelatedParty ap,
GuPolicyCopyRiskDynamic rd,
GuPolicyCopyItemAcciList i,
GuPolicyCopyRisk r,
GuPolicyCopyItemMain a,
GuPolicyCopyEndorHead h,
GgRisk k,
ggCompany c,
ggUser u,
GgCode n
WHERE m.policyNo = r.policyNo
AND m.companyCode = c.companyCode
AND r.policyNo = a.policyNo
AND r.endorSeqNo = a.endorSeqNo
AND r.riskCode = a.riskCode
AND r.plancode = a.plancode
AND m.policyNo = ap.policyNo
AND m.endorSeqNo = r.endorSeqNo
AND r.policyno = rd.policyno
AND r.riskcode = rd.riskcode
AND r.endorseqno = rd.endorseqno
AND m.policyNo = h.policyNo
AND m.endorSeqNo = h.endorSeqNo
AND r.riskCode = k.riskCode
AND a.policyNo = i.policyNo(+)
AND a.endorSeqNo = i.endorSeqNo(+)
AND a.riskCode = i.riskCode(+)
AND a.plancode = i.plancode(+)
AND a.itemno = i.itemno(+)
AND n.codecode = m.channeltip
AND n.codetype = 'UnderWriteChannelTip'
AND u.usercode = m.salesmanCode
AND k.opencoverind <> '1'
AND k.riskClass in ('11', '10')
AND (TIMESTAMP'2023-12-12 12:11:13.000' between r.startDate and r.endDate)
AND h.endorSeqNo =
(SELECT MAX(endorSeqNo)
FROM GuPolicyCopyEndorHead t
WHERE t.policyNo = h.policyNo
AND t.validDate <= TIMESTAMP'2023-12-12 12:11:13.000')
and ((m.cancelind <> '1' or m.cancelind is null) and
(m.surrenderind <> '1' or m.surrenderind is null))
AND (r.riskCode = '1137')
and (r.policyNo = '6050400113720230000119' or
r.subPolicyNo = '6050400113720230000119')
AND (k.riskClass = '11')
order by r.startDate Desc;
COUNT(A.POLICYNO)
-----------------
129283
DB默认走连接谓词推入，刚好视图连接的外部表数据多(13万)，不合适走这个执行计划。
相当于视图执行13万次，有两个谓词推入，那就执行的次数更多。
7.关闭连接谓词推入
--取消连接谓词推入
alter session set "_push_join_predicate"=false;
SQL执行:0.5s;
Elapsed: 00:00:00.55
CTPROD@coredb1>
--取消连接谓词推入,后的执行计划。
--没有连接谓词推入:VIEW PUSHED PREDICATE 的执行计划了。
select * from table(dbms_xplan.display_cursor('2gs6gb92zcb51',null,null));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 70 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 46 | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYITEMACCI | 1 | 46 | 4 (0)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | ID_PC_ITEMACCI_REF_ITEMMAIN | 1 | | 3 (0)| 00:00:01 | | |
| 4 | SORT ORDER BY | | 1 | 535 | 70 (3)| 00:00:01 | | |
| 5 | HASH UNIQUE | | 1 | 535 | 69 (2)| 00:00:01 | | |
| 6 | CONCATENATION | | | | | | | |
| 7 | NESTED LOOPS | | 1 | 535 | 29 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 1 | 535 | 29 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 450 | 25 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS | | 1 | 396 | 24 (0)| 00:00:01 | | |
| 11 | NESTED LOOPS | | 1 | 377 | 23 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 350 | 21 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 1 | 300 | 18 (0)| 00:00:01 | | |
| 14 | NESTED LOOPS | | 1 | 240 | 15 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS OUTER | | 1 | 206 | 11 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | 1 | 165 | 8 (0)| 00:00:01 | | |
| 17 | NESTED LOOPS | | 1 | 124 | 5 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | GGRISK | 1 | 42 | 1 (0)| 00:00:01 | | |
|* 19 | INDEX UNIQUE SCAN | PK_GGRISK | 1 | | 0 (0)| | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISK | 1 | 82 | 4 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | IDX_GUPOLICYCOPYRISK_SPNO | 1 | | 3 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN | PK_GUPOLICYCOP6 | 1 | 41 | 3 (0)| 00:00:01 | | |
|* 23 | INDEX RANGE SCAN | PK_GUPOLICYCO17 | 1 | 41 | 3 (0)| 00:00:01 | | |
| 24 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISKDYNAMIC | 1 | 34 | 4 (0)| 00:00:01 | | |
|* 25 | INDEX RANGE SCAN | PK_GUPOLICYCOPYRISKDYNAMIC | 1 | | 3 (0)| 00:00:01 | | |
|* 26 | TABLE ACCESS BY GLOBAL INDEX ROWID| GUPOLICYCOPYMAIN | 1 | 60 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 27 | INDEX UNIQUE SCAN | PK_GUPOLICYCO21 | 1 | | 2 (0)| 00:00:01 | | |
| 28 | TABLE ACCESS BY INDEX ROWID | GGCODE | 1 | 50 | 3 (0)| 00:00:01 | | |
|* 29 | INDEX RANGE SCAN | PK_GGCODE | 1 | | 2 (0)| 00:00:01 | | |
|* 30 | INDEX UNIQUE SCAN | PK_PHEAD2 | 1 | 27 | 2 (0)| 00:00:01 | | |
| 31 | SORT AGGREGATE | | 1 | 35 | | | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYENDORHEAD | 1 | 35 | 5 (0)| 00:00:01 | | |
|* 33 | INDEX RANGE SCAN | PK_PHEAD2 | 1 | | 4 (0)| 00:00:01 | | |
| 34 | TABLE ACCESS BY INDEX ROWID | GGUSER | 1 | 19 | 1 (0)| 00:00:01 | | |
|* 35 | INDEX UNIQUE SCAN | PK_GGUSER | 1 | | 0 (0)| | | |
| 36 | TABLE ACCESS BY INDEX ROWID | GGCOMPANY | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 37 | INDEX UNIQUE SCAN | PK_GGCOMPANY | 1 | | 0 (0)| | | |
|* 38 | INDEX RANGE SCAN | PK_CINSURED4 | 1 | | 3 (0)| 00:00:01 | | |
| 39 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRELATEDPARTY | 1 | 85 | 4 (0)| 00:00:01 | | |
| 40 | NESTED LOOPS | | 1 | 535 | 29 (0)| 00:00:01 | | |
| 41 | NESTED LOOPS | | 1 | 535 | 29 (0)| 00:00:01 | | |
| 42 | NESTED LOOPS | | 1 | 450 | 25 (0)| 00:00:01 | | |
| 43 | NESTED LOOPS | | 1 | 396 | 24 (0)| 00:00:01 | | |
| 44 | NESTED LOOPS | | 1 | 377 | 23 (0)| 00:00:01 | | |
| 45 | NESTED LOOPS | | 1 | 350 | 21 (0)| 00:00:01 | | |
| 46 | NESTED LOOPS | | 1 | 300 | 18 (0)| 00:00:01 | | |
| 47 | NESTED LOOPS | | 1 | 240 | 15 (0)| 00:00:01 | | |
| 48 | NESTED LOOPS OUTER | | 1 | 206 | 11 (0)| 00:00:01 | | |
| 49 | NESTED LOOPS | | 1 | 165 | 8 (0)| 00:00:01 | | |
| 50 | NESTED LOOPS | | 1 | 124 | 5 (0)| 00:00:01 | | |
|* 51 | TABLE ACCESS BY INDEX ROWID | GGRISK | 1 | 42 | 1 (0)| 00:00:01 | | |
|* 52 | INDEX UNIQUE SCAN | PK_GGRISK | 1 | | 0 (0)| | | |
|* 53 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISK | 1 | 82 | 4 (0)| 00:00:01 | | |
|* 54 | INDEX RANGE SCAN | PK_GUPOLICYCOPY | 1 | | 3 (0)| 00:00:01 | | |
|* 55 | INDEX RANGE SCAN | PK_GUPOLICYCOP6 | 1 | 41 | 3 (0)| 00:00:01 | | |
|* 56 | INDEX RANGE SCAN | PK_GUPOLICYCO17 | 1 | 41 | 3 (0)| 00:00:01 | | |
| 57 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRISKDYNAMIC | 1 | 34 | 4 (0)| 00:00:01 | | |
|* 58 | INDEX RANGE SCAN | PK_GUPOLICYCOPYRISKDYNAMIC | 1 | | 3 (0)| 00:00:01 | | |
|* 59 | TABLE ACCESS BY GLOBAL INDEX ROWID| GUPOLICYCOPYMAIN | 1 | 60 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 60 | INDEX UNIQUE SCAN | PK_GUPOLICYCO21 | 1 | | 2 (0)| 00:00:01 | | |
| 61 | TABLE ACCESS BY INDEX ROWID | GGCODE | 1 | 50 | 3 (0)| 00:00:01 | | |
|* 62 | INDEX RANGE SCAN | PK_GGCODE | 1 | | 2 (0)| 00:00:01 | | |
|* 63 | INDEX UNIQUE SCAN | PK_PHEAD2 | 1 | 27 | 2 (0)| 00:00:01 | | |
| 64 | SORT AGGREGATE | | 1 | 35 | | | | |
|* 65 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYENDORHEAD | 1 | 35 | 5 (0)| 00:00:01 | | |
|* 66 | INDEX RANGE SCAN | PK_PHEAD2 | 1 | | 4 (0)| 00:00:01 | | |
| 67 | TABLE ACCESS BY INDEX ROWID | GGUSER | 1 | 19 | 1 (0)| 00:00:01 | | |
|* 68 | INDEX UNIQUE SCAN | PK_GGUSER | 1 | | 0 (0)| | | |
| 69 | TABLE ACCESS BY INDEX ROWID | GGCOMPANY | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 70 | INDEX UNIQUE SCAN | PK_GGCOMPANY | 1 | | 0 (0)| | | |
|* 71 | INDEX RANGE SCAN | PK_CINSURED4 | 1 | | 3 (0)| 00:00:01 | | |
| 72 | TABLE ACCESS BY INDEX ROWID | GUPOLICYCOPYRELATEDPARTY | 1 | 85 | 4 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------
0.5s执行计划,由此可见，视图外部和视图连接的表的基数不能太大，否则视图和外部的基表走嵌套循环连接的效率极低。容易导致如上SQL性能低下。那么遇到上述 连接谓词推进导致的问题，
我们可以尝试取消或启用连接谓词推入进行对比，选出更好的执行计划。
8.总结
启用连接谓词推入的方法:
/*+push_pred*/
或者:
alter session set "_push_join_predicate"=true;
取消连接谓词推入的方法:
/*+no_push_pred*/
或者:
alter session set "_push_join_predicate"=false;
如上开启和关闭的方法，仅针对当前SQL或当前会话生效。如果需要在系统级别生效:
alter system set "_push_join_predicate"=true/false; --需要注意，系统级别影响较大，一般不建议设置。容易导致批量SQL异常。所以我们仅针对会话或单个SQL设置。