1、问题描述
网友找我看一个跑批脚本，跑了一晚上没出结果，这里SQL太长我就不贴了，我们只看执行计划
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 315G(100)| |
| 1 | LOAD TABLE CONVENTIONAL | EDW_PROCITEMKIND_MAPPINGADD | | | | | |
| 2 | WINDOW SORT | | 91M| 93G| 99G| 315G (1)|999:59:59 |
|* 3 | HASH JOIN RIGHT OUTER | | 91M| 93G| | 315G (1)|999:59:59 |
| 4 | VIEW | | 32 | 4800 | | 4 (25)| 00:00:01 |
| 5 | SORT UNIQUE | | 32 | 704 | | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | ODS_HA_PRODUCT | 32 | 704 | | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 91M| 81G| | 315G (1)|999:59:59 |
| 8 | VIEW | | 1 | 52 | | 5 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 1 | 173 | | 5 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | ODS_ADD_HA_GROUP_SHORT_TERM_FEE_RATE | 1 | 137 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | ODS_ADD_HA_APP_GROUP | 44 | 1584 | | 3 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER | | 91M| 76G| | 315G (1)|999:59:59 |
| 13 | VIEW | | 13 | 2522 | | 232 (1)| 00:00:01 |
|* 14 | HASH JOIN | | 13 | 2314 | | 232 (1)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | ODS_HA_CS_MAIN | 13 | 299 | | 27 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | EDW_PROCTRANSACTION_MAPPINGADD | 2182 | 330K| | 205 (1)| 00:00:01 |
| 17 | VIEW | | 91M| 60G| | 315G (1)|999:59:59 |
| 18 | SORT UNIQUE | | 91M| 54G| 58G| 315G (1)|999:59:59 |
| 19 | UNION-ALL | | | | | | |
| 20 | NESTED LOOPS OUTER | | 45M| 43G| | 216G (1)|999:59:59 |
|* 21 | FILTER | | | | | | |
| 22 | NESTED LOOPS OUTER | | 45M| 11G| | 98G (1)|999:59:59 |
| 23 | VIEW | | 45M| 4942M| | 4053K (1)| 00:02:39 |
| 24 | SORT GROUP BY | | 45M| 5335M| 43G| 4053K (1)| 00:02:39 |
|* 25 | HASH JOIN | | 332M| 37G| | 23406 (6)| 00:00:01 |
|* 26 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_GROUP_PRODUCT_LOG | 26024 | 991K| | 171 (1)| 00:00:01 |
|* 27 | HASH JOIN | | 262K| 20M| | 22112 (1)| 00:00:01 |
|* 28 | HASH JOIN | | 245 | 14945 | | 21501 (1)| 00:00:01 |
|* 29 | HASH JOIN SEMI | | 238 | 8806 | | 184 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID BATCHED| ODS_ADD_HA_POLICY_GROUP_PRODUCT_LIABILITY_LOG | 238 | 7378 | | 180 (0)| 00:00:01 |
|* 31 | INDEX SKIP SCAN | INDEX_PGPL_LOG | 238 | | | 171 (0)| 00:00:01 |
| 32 | INDEX FAST FULL SCAN | INDEX_HCP | 2158 | 12948 | | 4 (0)| 00:00:01 |
|* 33 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_PLAN_PRODUCT_LIABILITY_LOG | 2142 | 51408 | | 21317 (1)| 00:00:01 |
| 34 | INDEX FAST FULL SCAN | INDEX_AAPLOG | 356K| 7650K| | 610 (1)| 00:00:01 |
|* 35 | VIEW PUSHED PREDICATE | | 1 | 149 | | 2152 (1)| 00:00:01 |
| 36 | SORT GROUP BY | | 1 | 133 | | 2152 (1)| 00:00:01 |
| 37 | VIEW | VM_NWVW_1 | 1 | 133 | | 2151 (1)| 00:00:01 |
| 38 | SORT GROUP BY | | 1 | 142 | | 2151 (1)| 00:00:01 |
|* 39 | FILTER | | | | | | |
| 40 | NESTED LOOPS | | 1 | 142 | | 2150 (1)| 00:00:01 |
| 41 | NESTED LOOPS | | 1 | 142 | | 2150 (1)| 00:00:01 |
| 42 | NESTED LOOPS | | 1 | 73 | | 2148 (1)| 00:00:01 |
| 43 | NESTED LOOPS | | 1 | 53 | | 1875 (1)| 00:00:01 |
|* 44 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_PLAN_LOG | 1 | 33 | | 1848 (1)| 00:00:01 |
|* 45 | TABLE ACCESS FULL | ODS_HA_CS_MAIN | 1 | 20 | | 27 (0)| 00:00:01 |
|* 46 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_INDIVIDUAL_PRODUCT_LOG | 25218 | 492K| | 273 (1)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | INDEX_APIP_LOG | 1 | | | 1 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID | ODS_ADD_HA_POLICY_INDIVIDUAL_PRODUCT_LIABILITY_LOG | 1 | 69 | | 2 (0)| 00:00:01 |
|* 49 | VIEW PUSHED PREDICATE | | 1 | 762 | | 2574 (1)| 00:00:01 |
| 50 | SORT GROUP BY | | 2224 | 295K| | 2574 (1)| 00:00:01 |
| 51 | VIEW | VM_NWVW_0 | 2224 | 295K| | 2573 (1)| 00:00:01 |
| 52 | SORT GROUP BY | | 2224 | 490K| | 2573 (1)| 00:00:01 |
|* 53 | HASH JOIN RIGHT OUTER | | 2224 | 490K| | 2572 (1)| 00:00:01 |
|* 54 | TABLE ACCESS FULL | ODS_ADD_HA_APP_PLAN_RELATIVITY_DATA | 1 | 59 | | 377 (1)| 00:00:01 |
|* 55 | HASH JOIN | | 2224 | 362K| | 2196 (1)| 00:00:01 |
|* 56 | TABLE ACCESS FULL | ODS_HA_APP_PLAN | 701 | 16824 | | 5 (0)| 00:00:01 |
|* 57 | HASH JOIN | | 2377 | 331K| | 2191 (1)| 00:00:01 |
|* 58 | HASH JOIN | | 274 | 37264 | | 2122 (1)| 00:00:01 |
| 59 | NESTED LOOPS | | 272 | 32640 | | 270 (0)| 00:00:01 |
| 60 | NESTED LOOPS | | 534 | 32640 | | 270 (0)| 00:00:01 |
|* 61 | TABLE ACCESS FULL | ODS_HA_APP_GROUP | 1 | 45 | | 3 (0)| 00:00:01 |
|* 62 | INDEX RANGE SCAN | IND_INDIVIDUAL_PRODUCT_LIABILITY | 534 | | | 98 (0)| 00:00:01 |
|* 63 | TABLE ACCESS BY INDEX ROWID | ODS_HA_APP_INDIVIDUAL_PRODUCT_LIABILITY | 534 | 40050 | | 267 (0)| 00:00:01 |
|* 64 | TABLE ACCESS FULL | ODS_HA_APP_INDIVIDUAL_PRODUCT | 238K| 3733K| | 1851 (1)| 00:00:01 |
|* 65 | TABLE ACCESS FULL | ODS_HA_APP_PLAN_PRODUCT_LIABILITY | 6497 | 45479 | | 68 (0)| 00:00:01 |
|* 66 | FILTER | | | | | | |
| 67 | NESTED LOOPS OUTER | | 45M| 11G| | 98G (1)|999:59:59 |
| 68 | VIEW | | 45M| 4942M| | 4053K (1)| 00:02:39 |
| 69 | SORT GROUP BY | | 45M| 5335M| 43G| 4053K (1)| 00:02:39 |
|* 70 | HASH JOIN | | 332M| 37G| | 23406 (6)| 00:00:01 |
|* 71 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_GROUP_PRODUCT_LOG | 26024 | 991K| | 171 (1)| 00:00:01 |
|* 72 | HASH JOIN | | 262K| 20M| | 22112 (1)| 00:00:01 |
|* 73 | HASH JOIN | | 245 | 14945 | | 21501 (1)| 00:00:01 |
|* 74 | HASH JOIN SEMI | | 238 | 8806 | | 184 (0)| 00:00:01 |
|* 75 | TABLE ACCESS BY INDEX ROWID BATCHED | ODS_ADD_HA_POLICY_GROUP_PRODUCT_LIABILITY_LOG | 238 | 7378 | | 180 (0)| 00:00:01 |
|* 76 | INDEX SKIP SCAN | INDEX_PGPL_LOG | 238 | | | 171 (0)| 00:00:01 |
| 77 | INDEX FAST FULL SCAN | INDEX_HCP | 2158 | 12948 | | 4 (0)| 00:00:01 |
|* 78 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_PLAN_PRODUCT_LIABILITY_LOG | 2142 | 51408 | | 21317 (1)| 00:00:01 |
| 79 | INDEX FAST FULL SCAN | INDEX_AAPLOG | 356K| 7650K| | 610 (1)| 00:00:01 |
|* 80 | VIEW PUSHED PREDICATE | | 1 | 149 | | 2152 (1)| 00:00:01 |
| 81 | SORT GROUP BY | | 1 | 133 | | 2152 (1)| 00:00:01 |
| 82 | VIEW | VM_NWVW_2 | 1 | 133 | | 2151 (1)| 00:00:01 |
| 83 | SORT GROUP BY | | 1 | 142 | | 2151 (1)| 00:00:01 |
|* 84 | FILTER | | | | | | |
| 85 | NESTED LOOPS | | 1 | 142 | | 2150 (1)| 00:00:01 |
| 86 | NESTED LOOPS | | 1 | 142 | | 2150 (1)| 00:00:01 |
| 87 | NESTED LOOPS | | 1 | 73 | | 2148 (1)| 00:00:01 |
| 88 | NESTED LOOPS | | 1 | 53 | | 1875 (1)| 00:00:01 |
|* 89 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_PLAN_LOG | 1 | 33 | | 1848 (1)| 00:00:01 |
|* 90 | TABLE ACCESS FULL | ODS_HA_CS_MAIN | 1 | 20 | | 27 (0)| 00:00:01 |
|* 91 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_INDIVIDUAL_PRODUCT_LOG | 25218 | 492K| | 273 (1)| 00:00:01 |
|* 92 | INDEX RANGE SCAN | INDEX_APIP_LOG | 1 | | | 1 (0)| 00:00:01 |
|* 93 | TABLE ACCESS BY INDEX ROWID | ODS_ADD_HA_POLICY_INDIVIDUAL_PRODUCT_LIABILITY_LOG | 1 | 69 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OPD"."WJS_PRODUCT_ID"="MAB"."WJS_PRODUCT_ID")
6 - filter("T"."IS_DELETED"='N')
7 - access("MAJ"."FULLPOLICYNO"="DLL"."HA_GROUP_POLICY_NO")
9 - access("A"."HA_APP_GROUP_NO"="B"."HA_APP_GROUP_NO")
12 - access("MAB"."HA_GROUP_POLICY_ID"=TO_NUMBER("MAJ"."FULLPOLICYNOID") AND "MAJ"."RISKCODE"="MAB"."RISK_CODE" AND "MAJ"."ID"="MAB"."CHANGE_ID")
14 - access("B"."HA_GROUP_POLICY_ID"=TO_NUMBER("T"."FULLPOLICYNOID") AND "T"."CURRENT_ENDOR_SEQ_NO"="B"."ENDOR_SEQ_NO")
15 - filter(("B"."HA_GROUP_POLICY_ID"=520001 AND "B"."STATUS"=1))
16 - filter(("T"."TRANSACTIONREASON"='08' AND TO_NUMBER("T"."FULLPOLICYNOID")=520001 AND TO_NUMBER("T"."FLAG")=1))
21 - filter((DECODE(TO_CHAR("TMP2"."STANDARD_PREMIUM"),NULL,0,"TMP2"."STANDARD_PREMIUM")=0 AND
DECODE(TO_CHAR("TMP2"."TOTAL_PREMIUM"),NULL,0,"TMP2"."TOTAL_PREMIUM")=0 AND DECODE(TO_CHAR("TMP2"."SUM_INSURED"),NULL,0,"TMP2"."SUM_INSURED")=0 AND
DECODE(TO_CHAR("TMP2"."TAX"),NULL,0,"TMP2"."TAX")=0 AND DECODE(TO_CHAR("TMP2"."NOTAX_PREM"),NULL,0,"TMP2"."NOTAX_PREM")=0 AND
DECODE(TO_CHAR("TMP2"."AMOUNT"),NULL,0,"TMP2"."AMOUNT")=0))
25 - access("B"."GROUP_PRODUCT_ID"="A"."HA_GROUP_PRODUCT_ID")
26 - filter("B"."IS_DELETED"='N')
27 - access("PPL"."HA_PLAN_ID"="PPO"."HA_PLAN_ID")
28 - access("A"."HA_GROUP_POLICY_ID"="PPL"."HA_GROUP_POLICY_ID" AND "A"."CHANGE_ID"="PPL"."CHANGE_ID" AND
"A"."OLD_CORE_LIABILITY_CODE"="PPL"."OLD_CORE_LIABILITY_CODE")
29 - access("A"."CHANGE_ID"="CP"."CHANGE_ID")
30 - filter("A"."IS_DELETED"='N')
31 - access("A"."HA_GROUP_POLICY_ID"=520001)
filter("A"."HA_GROUP_POLICY_ID"=520001)
33 - filter("PPL"."HA_GROUP_POLICY_ID"=520001)
35 - filter(("TMP1"."RISK_CODE"="TMP2"."RISK_CODE" AND "TMP1"."OLD_CORE_LIABILITY_CODE"="TMP2"."OLD_CORE_LIABILITY_CODE"))
39 - filter("TMP1"."HA_GROUP_POLICY_ID"=520001)
44 - filter(("T"."HA_GROUP_POLICY_ID"=520001 AND "T"."CHANGE_ID"="TMP1"."CHANGE_ID" AND "T"."PLAN_CODE"="TMP1"."PLAN_CODE" AND
"T"."HA_GROUP_POLICY_ID"="TMP1"."HA_GROUP_POLICY_ID"))
45 - filter(("CSM"."ID"="TMP1"."CHANGE_ID" AND "CSM"."STATUS"=1 AND "CSM"."IS_DELETED"='N'))
46 - filter(("PIP"."STATUS"=1 AND "PIP"."IS_DELETED"='N'))
47 - access("A"."CHANGE_ID"="TMP1"."CHANGE_ID" AND "A"."HA_GROUP_POLICY_ID"="TMP1"."HA_GROUP_POLICY_ID" AND "A"."HA_PLAN_ID"="T"."HA_PLAN_ID" AND
"A"."WJS_PRODUCT_ID"="TMP1"."WJS_PRODUCT_ID")
filter(("A"."HA_GROUP_POLICY_ID"=520001 AND "A"."CHANGE_ID"="CSM"."ID" AND "A"."CHANGE_ID"="T"."CHANGE_ID"))
48 - filter(("PIP"."INDIVIDUAL_PRODUCT_ID"="A"."HA_INDIVIDUAL_PRODUCT_ID" AND "A"."IS_DELETED"='N'))
49 - filter(("TMP1"."HA_GROUP_POLICY_ID"="TMP_2"."HA_GROUP_POLICY_ID" AND "TMP1"."WJS_PRODUCT_ID"="TMP_2"."WJS_PRODUCT_ID" AND
"TMP1"."PLAN_CODE"="TMP_2"."PLAN_CODE" AND "TMP1"."RISK_CODE"="TMP_2"."RISK_CODE"))
53 - access("APD"."HA_APP_PLAN_ID"="HIS"."ID" AND "APD"."WJS_PRODUCT_ID"="A"."WJS_PRODUCT_ID" AND "APD"."HA_APP_GROUP_ID"="HOG"."ID" AND
"A"."WJS_LIABILITY_CODE"="APD"."WJS_LIABILITY_CODE")
54 - filter(("APD"."HA_APP_GROUP_ID" IS NOT NULL AND "APD"."RELATIVITY_CODE"='Daily hospitalization allowance' AND "APD"."IS_DELETED"='N'))
55 - access("HPS"."HA_APP_PLAN_ID"="HIS"."ID")
56 - filter("HIS"."IS_DELETED"='N')
57 - access("A"."HA_APP_PLAN_ID"="HPS"."HA_APP_PLAN_ID")
58 - access("A"."HA_APP_INDIVIDUAL_PRODUCT_ID"="AIP"."ID")
61 - filter(("HOG"."HA_GROUP_POLICY_ID"=520001 AND "HOG"."STATUS"=7 AND "HOG"."IS_DELETED"='N'))
62 - access("A"."HA_APP_GROUP_ID"="HOG"."ID" AND "A"."OLD_CORE_LIABILITY_CODE"="TMP1"."OLD_CORE_LIABILITY_CODE")
filter("A"."OLD_CORE_LIABILITY_CODE"="TMP1"."OLD_CORE_LIABILITY_CODE")
63 - filter("A"."IS_DELETED"='N')
64 - filter("AIP"."IS_DELETED"='N')
65 - filter("HPS"."IS_DELETED"='N')
66 - filter((DECODE(TO_CHAR("TMP2"."STANDARD_PREMIUM"),NULL,0,"TMP2"."STANDARD_PREMIUM")<>0 AND
DECODE(TO_CHAR("TMP2"."TOTAL_PREMIUM"),NULL,0,"TMP2"."TOTAL_PREMIUM")<>0 AND DECODE(TO_CHAR("TMP2"."SUM_INSURED"),NULL,0,"TMP2"."SUM_INSURED")<>0 AND
DECODE(TO_CHAR("TMP2"."TAX"),NULL,0,"TMP2"."TAX")<>0 AND DECODE(TO_CHAR("TMP2"."NOTAX_PREM"),NULL,0,"TMP2"."NOTAX_PREM")<>0 AND
DECODE(TO_CHAR("TMP2"."AMOUNT"),NULL,0,"TMP2"."AMOUNT")<>0))
70 - access("B"."GROUP_PRODUCT_ID"="A"."HA_GROUP_PRODUCT_ID")
71 - filter("B"."IS_DELETED"='N')
72 - access("PPL"."HA_PLAN_ID"="PPO"."HA_PLAN_ID")
73 - access("A"."HA_GROUP_POLICY_ID"="PPL"."HA_GROUP_POLICY_ID" AND "A"."CHANGE_ID"="PPL"."CHANGE_ID" AND
"A"."OLD_CORE_LIABILITY_CODE"="PPL"."OLD_CORE_LIABILITY_CODE")
74 - access("A"."CHANGE_ID"="CP"."CHANGE_ID")
75 - filter("A"."IS_DELETED"='N')
76 - access("A"."HA_GROUP_POLICY_ID"=520001)
filter("A"."HA_GROUP_POLICY_ID"=520001)
78 - filter("PPL"."HA_GROUP_POLICY_ID"=520001)
80 - filter(("TMP1"."RISK_CODE"="TMP2"."RISK_CODE" AND "TMP1"."OLD_CORE_LIABILITY_CODE"="TMP2"."OLD_CORE_LIABILITY_CODE"))
84 - filter("TMP1"."HA_GROUP_POLICY_ID"=520001)
89 - filter(("T"."HA_GROUP_POLICY_ID"=520001 AND "T"."CHANGE_ID"="TMP1"."CHANGE_ID" AND "T"."PLAN_CODE"="TMP1"."PLAN_CODE" AND
"T"."HA_GROUP_POLICY_ID"="TMP1"."HA_GROUP_POLICY_ID"))
90 - filter(("CSM"."ID"="TMP1"."CHANGE_ID" AND "CSM"."STATUS"=1 AND "CSM"."IS_DELETED"='N'))
91 - filter(("PIP"."STATUS"=1 AND "PIP"."IS_DELETED"='N'))
92 - access("A"."CHANGE_ID"="TMP1"."CHANGE_ID" AND "A"."HA_GROUP_POLICY_ID"="TMP1"."HA_GROUP_POLICY_ID" AND "A"."HA_PLAN_ID"="T"."HA_PLAN_ID" AND
"A"."WJS_PRODUCT_ID"="TMP1"."WJS_PRODUCT_ID")
filter(("A"."HA_GROUP_POLICY_ID"=520001 AND "A"."CHANGE_ID"="CSM"."ID" AND "A"."CHANGE_ID"="T"."CHANGE_ID"))
93 - filter(("PIP"."INDIVIDUAL_PRODUCT_ID"="A"."HA_INDIVIDUAL_PRODUCT_ID" AND "A"."IS_DELETED"='N'))
2、问题分析
观察执行计划，可能出现性能的点就是谓词推入了，主表要是返回数据量小无所谓，但案例中主表返回有45M，这就要了命了
3、问题处理
因数据库版本11.2.0.1 Bug太多，而脚本也是在PL/SQL DEV中跑的，这里直接通过禁用谓词推入解决问题ALTER SESSION SET "_push_join_predicate" = false;
优化后的执行计划,17.29秒出结果
1276 rows selected.
Elapsed: 00:00:17.29
Execution Plan
----------------------------------------------------------
Plan hash value: 4034007401
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72M| 74G| | 45M (1)| 00:29:34 |
| 1 | WINDOW SORT | | 72M| 74G| 79G| 45M (1)| 00:29:34 |
|* 2 | HASH JOIN RIGHT OUTER | | 72M| 74G| | 28M (1)| 00:18:40 |
| 3 | VIEW | | 32 | 4800 | | 4 (25)| 00:00:01 |
| 4 | HASH UNIQUE | | 32 | 704 | | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | ODS_HA_PRODUCT | 32 | 704 | | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 72M| 64G| | 28M (1)| 00:18:40 |
| 7 | VIEW | | 1 | 52 | | 5 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 1 | 173 | | 5 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | ODS_ADD_HA_GROUP_SHORT_TERM_FEE_RATE | 1 | 137 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | ODS_ADD_HA_APP_GROUP | 44 | 1584 | | 3 (0)| 00:00:01 |
|* 11 | HASH JOIN RIGHT OUTER | | 72M| 60G| | 28M (1)| 00:18:40 |
| 12 | VIEW | | 6 | 1164 | | 232 (1)| 00:00:01 |
|* 13 | HASH JOIN | | 6 | 1068 | | 232 (1)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | ODS_HA_CS_MAIN | 9 | 207 | | 27 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | EDW_PROCTRANSACTION_MAPPINGADD | 524 | 81220 | | 205 (1)| 00:00:01 |
| 16 | VIEW | | 72M| 47G| | 28M (1)| 00:18:40 |
| 17 | SORT UNIQUE | | 72M| 48G| 55G| 28M (1)| 00:18:40 |
| 18 | UNION-ALL | | | | | | |
|* 19 | FILTER | | | | | | |
|* 20 | HASH JOIN RIGHT OUTER | | 36M| 37G| | 3416K (1)| 00:02:14 |
| 21 | VIEW | | 141 | 29187 | | 2353 (1)| 00:00:01 |
| 22 | HASH GROUP BY | | 141 | 20304 | | 2353 (1)| 00:00:01 |
| 23 | VIEW | VM_NWVW_1 | 141 | 20304 | | 2352 (1)| 00:00:01 |
| 24 | HASH GROUP BY | | 141 | 20022 | | 2352 (1)| 00:00:01 |
|* 25 | HASH JOIN | | 141 | 20022 | | 2351 (1)| 00:00:01 |
|* 26 | HASH JOIN | | 121 | 14762 | | 2077 (1)| 00:00:01 |
| 27 | NESTED LOOPS | | 121 | 12342 | | 2050 (1)| 00:00:01 |
| 28 | NESTED LOOPS | | 121 | 12342 | | 2050 (1)| 00:00:01 |
|* 29 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_PLAN_LOG | 121 | 3993 | | 1848 (1)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | INDEX_APIP_LOG | 1 | | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | ODS_ADD_HA_POLICY_INDIVIDUAL_PRODUCT_LIABILITY_LOG | 1 | 69 | | 2 (0)| 00:00:01 |
|* 32 | TABLE ACCESS FULL | ODS_HA_CS_MAIN | 2151 | 43020 | | 27 (0)| 00:00:01 |
|* 33 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_INDIVIDUAL_PRODUCT_LOG | 25309 | 494K| | 273 (1)| 00:00:01 |
|* 34 | HASH JOIN RIGHT OUTER | | 36M| 30G| 41M| 3413K (1)| 00:02:14 |
| 35 | VIEW | | 53379 | 40M| | 9411 (1)| 00:00:01 |
| 36 | HASH GROUP BY | | 53379 | 10M| 12M| 9411 (1)| 00:00:01 |
| 37 | VIEW | VM_NWVW_0 | 53379 | 10M| | 7117 (1)| 00:00:01 |
| 38 | HASH GROUP BY | | 53379 | 11M| 12M| 7117 (1)| 00:00:01 |
|* 39 | HASH JOIN RIGHT OUTER | | 53379 | 11M| | 4503 (1)| 00:00:01 |
|* 40 | TABLE ACCESS FULL | ODS_ADD_HA_APP_PLAN_RELATIVITY_DATA | 1 | 59 | | 377 (1)| 00:00:01 |
|* 41 | HASH JOIN | | 53379 | 8705K| | 4126 (1)| 00:00:01 |
|* 42 | TABLE ACCESS FULL | ODS_HA_APP_PLAN | 701 | 16824 | | 5 (0)| 00:00:01 |
|* 43 | HASH JOIN | | 57035 | 7964K| | 4121 (1)| 00:00:01 |
|* 44 | TABLE ACCESS FULL | ODS_HA_APP_PLAN_PRODUCT_LIABILITY | 6497 | 45479 | | 68 (0)| 00:00:01 |
|* 45 | HASH JOIN | | 6575 | 873K| | 4052 (1)| 00:00:01 |
|* 46 | HASH JOIN | | 6532 | 765K| | 2200 (1)| 00:00:01 |
|* 47 | TABLE ACCESS FULL | ODS_HA_APP_GROUP | 1 | 45 | | 3 (0)| 00:00:01 |
|* 48 | TABLE ACCESS FULL | ODS_HA_APP_INDIVIDUAL_PRODUCT_LIABILITY | 371K| 26M| | 2196 (1)| 00:00:01 |
|* 49 | TABLE ACCESS FULL | ODS_HA_APP_INDIVIDUAL_PRODUCT | 238K| 3733K| | 1851 (1)| 00:00:01 |
| 50 | VIEW | | 36M| 3914M| | 3187K (1)| 00:02:05 |
| 51 | HASH GROUP BY | | 36M| 4226M| 34G| 3187K (1)| 00:02:05 |
|* 52 | HASH JOIN | | 260M| 29G| | 23161 (5)| 00:00:01 |
|* 53 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_GROUP_PRODUCT_LOG | 26024 | 991K| | 171 (1)| 00:00:01 |
|* 54 | HASH JOIN | | 205K| 16M| | 22110 (1)| 00:00:01 |
|* 55 | HASH JOIN | | 192 | 11712 | | 21499 (1)| 00:00:01 |
|* 56 | HASH JOIN SEMI | | 187 | 6919 | | 182 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID BATCHED| ODS_ADD_HA_POLICY_GROUP_PRODUCT_LIABILITY_LOG | 187 | 5797 | | 178 (0)| 00:00:01 |
|* 58 | INDEX SKIP SCAN | INDEX_PGPL_LOG | 187 | | | 171 (0)| 00:00:01 |
| 59 | INDEX FAST FULL SCAN | INDEX_HCP | 2158 | 12948 | | 4 (0)| 00:00:01 |
|* 60 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_PLAN_PRODUCT_LIABILITY_LOG | 1683 | 40392 | | 21317 (1)| 00:00:01 |
| 61 | INDEX FAST FULL SCAN | INDEX_AAPLOG | 356K| 7650K| | 610 (1)| 00:00:01 |
|* 62 | FILTER | | | | | | |
|* 63 | HASH JOIN RIGHT OUTER | | 36M| 10G| | 3189K (1)| 00:02:05 |
| 64 | VIEW | | 141 | 29187 | | 2353 (1)| 00:00:01 |
| 65 | HASH GROUP BY | | 141 | 20304 | | 2353 (1)| 00:00:01 |
| 66 | VIEW | VM_NWVW_2 | 141 | 20304 | | 2352 (1)| 00:00:01 |
| 67 | HASH GROUP BY | | 141 | 20022 | | 2352 (1)| 00:00:01 |
|* 68 | HASH JOIN | | 141 | 20022 | | 2351 (1)| 00:00:01 |
|* 69 | HASH JOIN | | 121 | 14762 | | 2077 (1)| 00:00:01 |
| 70 | NESTED LOOPS | | 121 | 12342 | | 2050 (1)| 00:00:01 |
| 71 | NESTED LOOPS | | 121 | 12342 | | 2050 (1)| 00:00:01 |
|* 72 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_PLAN_LOG | 121 | 3993 | | 1848 (1)| 00:00:01 |
|* 73 | INDEX RANGE SCAN | INDEX_APIP_LOG | 1 | | | 1 (0)| 00:00:01 |
|* 74 | TABLE ACCESS BY INDEX ROWID | ODS_ADD_HA_POLICY_INDIVIDUAL_PRODUCT_LIABILITY_LOG | 1 | 69 | | 2 (0)| 00:00:01 |
|* 75 | TABLE ACCESS FULL | ODS_HA_CS_MAIN | 2151 | 43020 | | 27 (0)| 00:00:01 |
|* 76 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_INDIVIDUAL_PRODUCT_LOG | 25309 | 494K| | 273 (1)| 00:00:01 |
| 77 | VIEW | | 36M| 3914M| | 3187K (1)| 00:02:05 |
| 78 | HASH GROUP BY | | 36M| 4226M| 34G| 3187K (1)| 00:02:05 |
|* 79 | HASH JOIN | | 260M| 29G| | 23161 (5)| 00:00:01 |
|* 80 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_GROUP_PRODUCT_LOG | 26024 | 991K| | 171 (1)| 00:00:01 |
|* 81 | HASH JOIN | | 205K| 16M| | 22110 (1)| 00:00:01 |
|* 82 | HASH JOIN | | 192 | 11712 | | 21499 (1)| 00:00:01 |
|* 83 | HASH JOIN SEMI | | 187 | 6919 | | 182 (0)| 00:00:01 |
|* 84 | TABLE ACCESS BY INDEX ROWID BATCHED | ODS_ADD_HA_POLICY_GROUP_PRODUCT_LIABILITY_LOG | 187 | 5797 | | 178 (0)| 00:00:01 |
|* 85 | INDEX SKIP SCAN | INDEX_PGPL_LOG | 187 | | | 171 (0)| 00:00:01 |
| 86 | INDEX FAST FULL SCAN | INDEX_HCP | 2158 | 12948 | | 4 (0)| 00:00:01 |
|* 87 | TABLE ACCESS FULL | ODS_ADD_HA_POLICY_PLAN_PRODUCT_LIABILITY_LOG | 1683 | 40392 | | 21317 (1)| 00:00:01 |
| 88 | INDEX FAST FULL SCAN | INDEX_AAPLOG | 356K| 7650K| | 610 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OPD"."WJS_PRODUCT_ID"(+)="MAB"."WJS_PRODUCT_ID")
5 - filter("T"."IS_DELETED"='N')
6 - access("MAJ"."FULLPOLICYNO"="DLL"."HA_GROUP_POLICY_NO"(+))
8 - access("A"."HA_APP_GROUP_NO"="B"."HA_APP_GROUP_NO")
11 - access("MAB"."HA_GROUP_POLICY_ID"=TO_NUMBER("MAJ"."FULLPOLICYNOID"(+)) AND "MAJ"."RISKCODE"(+)="MAB"."RISK_CODE" AND
"MAJ"."ID"(+)="MAB"."CHANGE_ID")
13 - access("B"."HA_GROUP_POLICY_ID"=TO_NUMBER("T"."FULLPOLICYNOID") AND "T"."CURRENT_ENDOR_SEQ_NO"="B"."ENDOR_SEQ_NO")
14 - filter("B"."HA_GROUP_POLICY_ID"=515003 AND "B"."STATUS"=1)
15 - filter("T"."TRANSACTIONREASON"='08' AND TO_NUMBER("T"."FULLPOLICYNOID")=515003 AND TO_NUMBER("T"."FLAG")=1)
19 - filter(DECODE(TO_CHAR("TMP2"."STANDARD_PREMIUM"),NULL,0,"TMP2"."STANDARD_PREMIUM")=0 AND
DECODE(TO_CHAR("TMP2"."TOTAL_PREMIUM"),NULL,0,"TMP2"."TOTAL_PREMIUM")=0 AND DECODE(TO_CHAR("TMP2"."SUM_INSURED"),NULL,0,"TMP2"."SUM_INSURED")=0 AND
DECODE(TO_CHAR("TMP2"."TAX"),NULL,0,"TMP2"."TAX")=0 AND DECODE(TO_CHAR("TMP2"."NOTAX_PREM"),NULL,0,"TMP2"."NOTAX_PREM")=0 AND
DECODE(TO_CHAR("TMP2"."AMOUNT"),NULL,0,"TMP2"."AMOUNT")=0)
20 - access("TMP1"."HA_GROUP_POLICY_ID"="TMP2"."HA_GROUP_POLICY_ID"(+) AND "TMP1"."WJS_PRODUCT_ID"="TMP2"."WJS_PRODUCT_ID"(+) AND
"TMP1"."PLAN_CODE"="TMP2"."PLAN_CODE"(+) AND "TMP1"."RISK_CODE"="TMP2"."RISK_CODE"(+) AND "TMP1"."CHANGE_ID"="TMP2"."CHANGE_ID"(+) AND
"TMP1"."OLD_CORE_LIABILITY_CODE"="TMP2"."OLD_CORE_LIABILITY_CODE"(+))
25 - access("PIP"."INDIVIDUAL_PRODUCT_ID"="A"."HA_INDIVIDUAL_PRODUCT_ID")
26 - access("A"."CHANGE_ID"="CSM"."ID")
29 - filter("T"."HA_GROUP_POLICY_ID"=515003)
30 - access("A"."CHANGE_ID"="T"."CHANGE_ID" AND "A"."HA_GROUP_POLICY_ID"=515003 AND "A"."HA_PLAN_ID"="T"."HA_PLAN_ID")
31 - filter("A"."IS_DELETED"='N')
32 - filter("CSM"."STATUS"=1 AND "CSM"."IS_DELETED"='N')
33 - filter("PIP"."STATUS"=1 AND "PIP"."IS_DELETED"='N')
34 - access("TMP1"."HA_GROUP_POLICY_ID"="TMP_2"."HA_GROUP_POLICY_ID"(+) AND "TMP1"."WJS_PRODUCT_ID"="TMP_2"."WJS_PRODUCT_ID"(+) AND
"TMP1"."PLAN_CODE"="TMP_2"."PLAN_CODE"(+) AND "TMP1"."RISK_CODE"="TMP_2"."RISK_CODE"(+) AND
"TMP1"."OLD_CORE_LIABILITY_CODE"="TMP_2"."OLD_CORE_LIABILITY_CODE"(+))
39 - access("APD"."HA_APP_PLAN_ID"(+)="HIS"."ID" AND "APD"."WJS_PRODUCT_ID"(+)="A"."WJS_PRODUCT_ID" AND "APD"."HA_APP_GROUP_ID"(+)="HOG"."ID" AND
"A"."WJS_LIABILITY_CODE"="APD"."WJS_LIABILITY_CODE"(+))
40 - filter("APD"."HA_APP_GROUP_ID"(+) IS NOT NULL AND "APD"."RELATIVITY_CODE"(+)='Daily hospitalization allowance' AND "APD"."IS_DELETED"(+)='N')
41 - access("HPS"."HA_APP_PLAN_ID"="HIS"."ID")
42 - filter("HIS"."IS_DELETED"='N')
43 - access("A"."HA_APP_PLAN_ID"="HPS"."HA_APP_PLAN_ID")
44 - filter("HPS"."IS_DELETED"='N')
45 - access("A"."HA_APP_INDIVIDUAL_PRODUCT_ID"="AIP"."ID")
46 - access("A"."HA_APP_GROUP_ID"="HOG"."ID")
47 - filter("HOG"."HA_GROUP_POLICY_ID"=515003 AND "HOG"."STATUS"=7 AND "HOG"."IS_DELETED"='N')
48 - filter("A"."IS_DELETED"='N')
49 - filter("AIP"."IS_DELETED"='N')
52 - access("B"."GROUP_PRODUCT_ID"="A"."HA_GROUP_PRODUCT_ID")
53 - filter("B"."IS_DELETED"='N')
54 - access("PPL"."HA_PLAN_ID"="PPO"."HA_PLAN_ID")
55 - access("A"."HA_GROUP_POLICY_ID"="PPL"."HA_GROUP_POLICY_ID" AND "A"."CHANGE_ID"="PPL"."CHANGE_ID" AND
"A"."OLD_CORE_LIABILITY_CODE"="PPL"."OLD_CORE_LIABILITY_CODE")
56 - access("A"."CHANGE_ID"="CP"."CHANGE_ID")
57 - filter("A"."IS_DELETED"='N')
58 - access("A"."HA_GROUP_POLICY_ID"=515003)
filter("A"."HA_GROUP_POLICY_ID"=515003)
60 - filter("PPL"."HA_GROUP_POLICY_ID"=515003)
62 - filter(DECODE(TO_CHAR("TMP2"."STANDARD_PREMIUM"),NULL,0,"TMP2"."STANDARD_PREMIUM")<>0 AND
DECODE(TO_CHAR("TMP2"."TOTAL_PREMIUM"),NULL,0,"TMP2"."TOTAL_PREMIUM")<>0 AND DECODE(TO_CHAR("TMP2"."SUM_INSURED"),NULL,0,"TMP2"."SUM_INSURED")<>0 AND
DECODE(TO_CHAR("TMP2"."TAX"),NULL,0,"TMP2"."TAX")<>0 AND DECODE(TO_CHAR("TMP2"."NOTAX_PREM"),NULL,0,"TMP2"."NOTAX_PREM")<>0 AND
DECODE(TO_CHAR("TMP2"."AMOUNT"),NULL,0,"TMP2"."AMOUNT")<>0)
63 - access("TMP1"."HA_GROUP_POLICY_ID"="TMP2"."HA_GROUP_POLICY_ID"(+) AND "TMP1"."WJS_PRODUCT_ID"="TMP2"."WJS_PRODUCT_ID"(+) AND
"TMP1"."PLAN_CODE"="TMP2"."PLAN_CODE"(+) AND "TMP1"."RISK_CODE"="TMP2"."RISK_CODE"(+) AND "TMP1"."CHANGE_ID"="TMP2"."CHANGE_ID"(+) AND
"TMP1"."OLD_CORE_LIABILITY_CODE"="TMP2"."OLD_CORE_LIABILITY_CODE"(+))
68 - access("PIP"."INDIVIDUAL_PRODUCT_ID"="A"."HA_INDIVIDUAL_PRODUCT_ID")
69 - access("A"."CHANGE_ID"="CSM"."ID")
72 - filter("T"."HA_GROUP_POLICY_ID"=515003)
73 - access("A"."CHANGE_ID"="T"."CHANGE_ID" AND "A"."HA_GROUP_POLICY_ID"=515003 AND "A"."HA_PLAN_ID"="T"."HA_PLAN_ID")
74 - filter("A"."IS_DELETED"='N')
75 - filter("CSM"."STATUS"=1 AND "CSM"."IS_DELETED"='N')
76 - filter("PIP"."STATUS"=1 AND "PIP"."IS_DELETED"='N')
79 - access("B"."GROUP_PRODUCT_ID"="A"."HA_GROUP_PRODUCT_ID")
80 - filter("B"."IS_DELETED"='N')
81 - access("PPL"."HA_PLAN_ID"="PPO"."HA_PLAN_ID")
82 - access("A"."HA_GROUP_POLICY_ID"="PPL"."HA_GROUP_POLICY_ID" AND "A"."CHANGE_ID"="PPL"."CHANGE_ID" AND
"A"."OLD_CORE_LIABILITY_CODE"="PPL"."OLD_CORE_LIABILITY_CODE")
83 - access("A"."CHANGE_ID"="CP"."CHANGE_ID")
84 - filter("A"."IS_DELETED"='N')
85 - access("A"."HA_GROUP_POLICY_ID"=515003)
filter("A"."HA_GROUP_POLICY_ID"=515003)
87 - filter("PPL"."HA_GROUP_POLICY_ID"=515003)
Statistics
----------------------------------------------------------
80 recursive calls
0 db block gets
199136 consistent gets
104785 physical reads
0 redo size
126645 bytes sent via SQL*Net to client
16228 bytes received via SQL*Net from client
87 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1276 rows processed
想了解SQL优化原理的同学，可以添加微信号：chenjunbee
或关注微信公众号，定期更新优化案例