昨天大半夜接到一条SQL，反应说很慢，我非常愤怒，经过询问，三个月才需要跑这个SQL一次，你tm非要在马上法定节假日了 跑它吗?
SQL如下(巨长无比)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
select * from table(dbms_xplan.display_cursor(lower('0ah5a8dbk28fh')));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID0ah5a8dbk28fh, child number 0
-------------------------------------
INSERT INTO END_TRANS_ACCOUNT( BRANCH_NO ,COST_CENTER,CNTR_NO ,IPSN_NO
,POL_CODE ,ACCOUNT_NO ,I_INFO_GROUP_FLAG,SG_NO ,CURRENCY_CODE
,VALID_DATE ,CNTR_STAT ,INVALID_DATE,ENDORSE_STAT ,REDUCE_START_DATE
,GROUP_FLAG ,SET_STAT ,FREEZE_STAT,DEAD_DATE ,DEAD_CODE ,MED_DATE
,ADJ_STOP_CAUSE ,ADJ_STOP_DATE,DUTY_STOP_DATE ,IPSN_AGE ,IPSN_SEX
,IPSN_NUM ,I_INFO_PAY_ITRVL,I_INFO_PAY_DUR ,I_INFO_PREM
,I_INFO_INSUR_DUR ,FACE_AMNT ,EXPIRY_AMNT,SUM_ASS_AMNT ,FEE_ITRVL
,REV_GRNT ,REV_GRNT_RATE ,RIDER_INFO,RIDER1_CNTR_NO ,RIDER2_CNTR_NO
,RIDER3_CNTR_NO ,RIDER1_SA ,RIDER2_SA,ACCOUNT_V_B ,ACCOUNT_V_E
,BONUS_RATIO ,CLAIM_FLAG ,PREM_PAID_NUM,LAST_PREM_DATE ,LAST_PREM
,YEAR_PREM_SG ,YEAR_PREM_RG ,FTP_PREM,SUM_PREM ,OCC_AMNT ,PALBD_AMNT
,FEE_INCOME ,FEE_INCOME_TOTAL,BONUS_PERSISTENCY
,BONUS_PERSISTENCY_TOTAL ,BONUS_CREDITED,INV_GRNT_RATE
,BONUS_REV_ITRVL ,BONUS_SUM ,BONUS_AMNT,PAID_AMNT ,PAID_ANN_AMNT
,PAID_DEATH_AMNT ,PAID_DIS_AMNT,PAID_MED_AMNT ,PAID_MED_REIMB
,PAID_EXP_AMNT ,PAID_GRANT_AMNT ,
Plan hash value: 2746060288
---------------------------------------------------------------------------------------------------------------------------------
| Id|Operation| Name| Rows| Bytes |TempSpc| Cost (%CPU)|Time|
---------------------------------------------------------------------------------------------------------------------------------
|0 | INSERT STATEMENT|||||2581K(100)||
|1 |LOAD TABLE CONVENTIONAL|||||||
|2 |UNION-ALL|||||||
|3 |NESTED LOOPS OUTER||8 |1264 ||24(17)| 00:00:01 |
|*4 |HASH JOIN OUTER||8 |912 ||24(17)| 00:00:01 |
|*5 |HASH JOIN OUTER||8 |840 ||20(15)| 00:00:01 |
|*6 |HASH JOIN OUTER||8 |744 ||17(18)| 00:00:01 |
|*7 |HASH JOIN OUTER||8 |648 ||13(16)| 00:00:01 |
|*8 |HASH JOIN OUTER||8 |552 ||10(20)| 00:00:01 |
|9 |MERGE JOIN OUTER||8 |456 ||6(17)| 00:00:01 |
|10 |TABLE ACCESS BY INDEX ROWID| PRE_INSUR_APPL|8 |360 ||2(0)| 00:00:01 |
|11 |INDEX FULL SCAN|PRIMARY_KEY|8 |||1(0)| 00:00:01 |
|* 12 |SORT JOIN||8 |96 ||4(25)| 00:00:01 |
|13 |TABLE ACCESS FULL| TMP_FACE_AMNT_APPLID|8 |96 ||3(0)| 00:00:01 |
|14 |TABLE ACCESS FULL| TMP_ACCOUNT_V_E_APPLID|8 |96 ||3(0)| 00:00:01 |
|15 |TABLE ACCESS FULL| TMP_YEAR_PREM_RG_APPLID|8 |96 ||3(0)| 00:00:01 |
|16 |TABLE ACCESS FULL| TMP_YEAR_PREM_SG_APPLID|8 |96 ||3(0)| 00:00:01 |
|17 |TABLE ACCESS FULL| TMP_SUM_PRE_APPLID|8 |96 ||3(0)| 00:00:01 |
|18 |TABLE ACCESS FULL| TMP_INSUR_DUR_APPLID|8 |72 ||3(0)| 00:00:01 |
|19 |TABLE ACCESS BY INDEX ROWID|TMP_COST_CENTER_CNTRNO|1 |44 ||0(0)||
|* 20 |INDEX UNIQUE SCAN|KEY_COST_CENTER_CNTRNO|1 |||0(0)||
|* 21 |HASH JOIN RIGHT OUTER||4326K|1390M||613K(2)| 02:02:48 |
|22 |TABLE ACCESS FULL| TMP_COST_CENTER_CNTRNO|1877 |82588 ||5(0)| 00:00:01 |
|* 23 |HASH JOIN RIGHT OUTER||4326K|1209M||613K(2)| 02:02:48 |
|24 |TABLE ACCESS FULL| TMP_PAID_MED_AMNT_CNTRNO|1872 |50544 ||5(0)| 00:00:01 |
|* 25 |HASH JOIN RIGHT OUTER||4326K|1097M||613K(2)| 02:02:47 |
|26 |TABLE ACCESS FULL| TMP_INSUR_DUR_CNTRNO|1862 |48412 ||5(0)| 00:00:01 |
|* 27 |HASH JOIN RIGHT OUTER||4326K|990M||613K(2)| 02:02:46 |
|28 |TABLE ACCESS FULL| TMP_MEDDATE_CLAIMFLAG_ACCID |1 |35 ||2(0)| 00:00:01 |
|* 29 |HASH JOIN RIGHT OUTER||4326K|845M|165M|613K(2)| 02:02:46 |
|30 |TABLE ACCESS FULL| TMP_ACCOUNT_V_B_ACCID|8653K|66M||3616(5)| 00:00:44 |
|* 31 |HASH JOIN RIGHT OUTER||4326K|812M||558K(2)| 01:51:48 |
|32 |TABLE ACCESS FULL| TMP_ACCOUNT_V_E_ACCID|14 |112 ||3(0)| 00:00:01 |
|* 33 |HASH JOIN RIGHT OUTER||4326K|779M|165M|558K(2)| 01:51:47 |
|34 |TABLE ACCESS FULL| TMP_FEE_INCOME_ACCID|8653K|66M||3728(5)| 00:00:45 |
|* 35 |HASH JOIN RIGHT OUTER||4326K|746M|139M|507K(2)| 01:41:27 |
|36 |TABLE ACCESS FULL| TMP_FUND_AVRG1_ACCID|7337K|55M||3199(5)| 00:00:39 |
|* 37 |HASH JOIN RIGHT OUTER||4326K|713M|165M|458K(2)| 01:31:48 |
|38 |TABLE ACCESS FULL| TMP_FUND_AVRGS_ACCID|8653K|66M||3756(5)| 00:00:46 |
|* 39 |HASH JOIN RIGHT OUTER||4326K|680M|165M|410K(2)| 01:22:07 |
|40 |TABLE ACCESS FULL| TMP_PAID_AMNT_ACCID|8653K|66M||3728(5)| 00:00:45 |
|* 41 |HASH JOIN RIGHT OUTER||4326K|647M|165M|363K(2)| 01:12:46 |
|42 |TABLE ACCESS FULL| TMP_SUM_PRE_ACCID|8653K|66M||3728(5)| 00:00:45 |
|* 43 |HASH JOIN RIGHT OUTER||4326K|614M|165M|318K(2)| 01:03:45 |
|44 |TABLE ACCESS FULL| TMP_YEAR_PREM_RG_ACCID|8653K|66M||3728(5)| 00:00:45 |
|* 45 |HASH JOIN RIGHT OUTER||4326K|581M|165M|275K(2)| 00:55:03 |
|46 |TABLE ACCESS FULL| TMP_YEAR_PREM_SG_ACCID|8653K|66M||3644(5)| 00:00:44 |
|* 47 |HASH JOIN RIGHT OUTER||4326K|548M|165M|233K(2)| 00:46:42 |
|48 |TABLE ACCESS FULL| TMP_ACC_DIS_AMNT_ACCID|8653K|66M||3616(5)| 00:00:44 |
|* 49 |HASH JOIN RIGHT OUTER||4326K|515M|165M|193K(2)| 00:38:41 |
|50 |TABLE ACCESS FULL| TMP_FUND_OUTGO_ACCID|8654K|66M||3589(5)| 00:00:44 |
|* 51 |HASHJOIN RIGHT OUTER||4326K|482M|165M|154K(2)| 00:30:59 |
|52 |TABLE ACCESS FULL| TMP_FUND_INCOME_ACCID|8654K|66M||3728(5)| 00:00:45 |
|* 53 |HASHJOIN RIGHT OUTER||4326K|449M|165M|117K(2)| 00:23:36 |
|54 |TABLE ACCESS FULL| TMP_FEE_INCOME_TOTAL_ACC_ID |8654K|66M||3728(5)| 00:00:45 |
|* 55 |HASHJOIN RIGHT OUTER ||4326K|416M|132M| 82683(2)| 00:16:33 |
|56 |TABLE ACCESS FULL| TMP_FUND_B_ACCID|7338K|48M||2808(6)| 00:00:34 |
|* 57 |TABLE ACCESS FULL| PRE_MED_FUND_ACC|4326K|387M|| 51358(2)| 00:10:17 |
|58 |NESTED LOOPS OUTER||1 |344 ||1416K(1)| 04:43:24 |
|59 |NESTED LOOPS OUTER||1 |336 ||1416K(1)| 04:43:24 |
|60 |NESTED LOOPS OUTER||1 |328 ||1416K(1)| 04:43:24 |
|61 |NESTED LOOPS OUTER||1 |320 ||1416K(1)| 04:43:24 |
|62 |NESTED LOOPS OUTER||1 |312 ||1416K(1)| 04:43:24 |
|* 63 |HASH JOIN RIGHT SEMI||1 |304 |2134M|1416K(1)| 04:43:24 |
|64 |INDEX FAST FULL SCAN|LH_01|101M|970M||152K(2)| 00:30:36 |
|* 65 |HASH JOIN RIGHT OUTER||8653K|2426M|165M|1030K(1)| 03:26:11 |
|66 |TABLE ACCESS FULL| TMP_FUND_OUTGO_ACCID|8654K|66M||3589(5)| 00:00:44 |
|* 67 |HASH JOIN RIGHT OUTER||8653K|2360M|165M|896K(1)| 02:59:22 |
|68 |TABLE ACCESS FULL| TMP_SUM_PRE_ACCID|8653K|66M||3728(5)| 00:00:45 |
|* 69 |HASH JOIN RIGHT OUTER||8653K|2294M|165M|765K(1)| 02:33:10 |
|70 |TABLE ACCESS FULL| TMP_PAID_AMNT_ACCID|8653K|66M||3728(5)| 00:00:45 |
|* 71 |HASH JOIN RIGHT OUTER||8653K|2228M|165M|638K(1)| 02:07:37 |
|72 |TABLE ACCESS FULL| TMP_FUND_AVRGS_ACCID|8653K|66M||3756(5)| 00:00:46 |
|* 73 |HASH JOIN RIGHT OUTER||8653K|2162M|165M|513K(1)| 01:42:44 |
|74 |TABLE ACCESS FULL| TMP_FEE_INCOME_ACCID|8653K|66M||3728(5)| 00:00:45 |
|* 75 |HASH JOIN RIGHT OUTER||8653K|2096M|165M|392K(1)| 01:18:30 |
|76 |TABLE ACCESS FULL| TMP_ACCOUNT_V_B_ACCID|8653K|66M||3616(5)| 00:00:44 |
|* 77 |HASH JOIN RIGHT OUTER||8653K|2030M|132M|274K(2)| 00:54:56 |
|78 |TABLE ACCESS FULL| TMP_FUND_B_ACCID|7338K|48M||2808(6)| 00:00:34 |
|* 79 |HASH JOIN RIGHT OUTER||8653K|1972M|139M|162K(2)| 00:32:27 |
|80 |TABLE ACCESS FULL| TMP_FUND_AVRG1_ACCID|7337K|55M||3199(5)| 00:00:39 |
|* 81 |HASH JOIN RIGHT OUTER||8653K|1906M|| 52225(4)| 00:10:27 |
|82 |TABLE ACCESS FULL| TMP_PAID_MED_AMNT_ACCID| 30936 |332K||19(6)| 00:00:01 |
|* 83 |HASHJOIN RIGHT OUTER||8653K|1815M|| 52107(4)| 00:10:26 |
|84 |TABLE ACCESS FULL| TMP_COST_CENTER_CNTRNO|1877 |82588 ||5(0)| 00:00:01 |
|* 85 |HASHJOIN RIGHT OUTER||8653K|1452M|| 52004(3)| 00:10:25 |
|86 |TABLE ACCESS FULL| TMP_INSUR_DUR_CNTRNO|1862 |48412 ||5(0)| 00:00:01 |
|* 87 |HASHJOIN RIGHT OUTER ||8653K|1237M|| 51901(3)| 00:10:23 |
|88 |TABLE ACCESS FULL| TMP_ACCOUNT_V_E_ACCID|14 |112 ||3(0)| 00:00:01 |
|* 89 |HASHJOIN RIGHT OUTER||8653K|1171M|| 51800(3)| 00:10:22 |
|90 |TABLE ACCESS FULL| TMP_MEDDATE_CLAIMFLAG_ACCID |1 |35 ||2(0)| 00:00:01 |
|* 91 |TABLE ACCESS FULL| PRE_MED_FUND_ACC|8653K|883M|| 51700(3)| 00:10:21 |
|92 |TABLE ACCESS BY INDEX ROWID| TMP_YEAR_PREM_RG_ACCID|1 |8 ||1(0)| 00:00:01 |
|* 93 |INDEX UNIQUE SCAN|KEY_YEAR_PREM_RG_ACCID|1 |||1(0)| 00:00:01 |
|94 |TABLE ACCESS BY INDEX ROWID| TMP_YEAR_PREM_SG_ACCID|1 |8 ||1(0)| 00:00:01 |
|* 95 |INDEX UNIQUE SCAN|KEY_YEAR_PREM_SG_ACCID|1 |||1(0)| 00:00:01 |
|96 |TABLE ACCESS BY INDEX ROWID|TMP_ACC_DIS_AMNT_ACCID|1 |8 ||1(0)| 00:00:01 |
|* 97 |INDEX UNIQUE SCAN|KEY_ACC_DIS_AMNT_ACCID|1 |||1(0)| 00:00:01 |
|98 |TABLE ACCESS BY INDEX ROWID|TMP_FUND_INCOME_ACCID|1 |8 ||1(0)| 00:00:01 |
|* 99 |INDEX UNIQUE SCAN|KEY_FUND_INCOME_ACCID|1 |||1(0)| 00:00:01 |
| 100 |TABLE ACCESS BY INDEX ROWID|TMP_FEE_INCOME_TOTAL_ACC_ID |1 |8 ||1(0)| 00:00:01 |
|*101 |INDEX UNIQUE SCAN|KEY_FEE_INCOME_TOTAL_ACC_ID |1 |||1(0)| 00:00:01 |
|*102 |HASH JOIN RIGHT OUTER||8653K|4085M||202K(3)| 00:40:35 |
| 103 |TABLE ACCESS FULL| TMP_COST_CENTER_CNTRNO|1877 |82588 ||5(0)| 00:00:01 |
|*104 |HASH JOIN RIGHT OUTER||8653K|3722M||202K(3)| 00:40:34 |
| 105 |TABLE ACCESS FULL| TMP_PAID_MED_AMNT_CNTRNO|1872 |50544 ||5(0)| 00:00:01 |
|*106 |HASH JOIN RIGHT OUTER||8653K|3499M||202K(3)| 00:40:33 |
| 107 |TABLE ACCESS FULL| TMP_INSUR_DUR_CNTRNO|1862 |48412 ||5(0)| 00:00:01 |
|*108 |HASH JOIN RIGHT OUTER||8653K|3284M||202K(3)| 00:40:31 |
| 109 |TABLE ACCESS FULL| TMP_ACCOUNT_V_B_CNTRNO|1 |29 ||3(0)| 00:00:01 |
|*110 |HASH JOIN RIGHT OUTER||8653K|3045M||202K(3)| 00:40:30 |
| 111 |TABLE ACCESS FULL| TMP_ACCOUNT_V_E_CNTRNO|1 |29 ||3(0)| 00:00:01 |
|*112 |HASH JOIN RIGHT OUTER||8653K|2805M||202K(2)| 00:40:29 |
| 113 |TABLE ACCESS FULL| TMP_FEE_INCOME_CNTRNO|1 |25 ||3(0)| 00:00:01 |
|*114 |HASH JOIN RIGHT OUTER||8653K|2599M||202K(2)| 00:40:28 |
| 115 |TABLE ACCESS FULL| TMP_FUND_AVRGS_CNTRNO|1 |29 ||3(0)| 00:00:01 |
|*116 |HASH JOIN RIGHT OUTER||8653K|2360M||202K(2)| 00:40:26 |
| 117 |TABLE ACCESS FULL| TMP_IPSN_NO|1 |32 ||3(0)| 00:00:01 |
|*118 |HASH JOIN RIGHT OUTER||8653K|2096M||202K(2)| 00:40:25 |
| 119 |TABLE ACCESS FULL| TMP_PAID_AMNT_CNTRNO|1 |25 ||3(0)| 00:00:01 |
|*120 |HASH JOIN RIGHT OUTER||8653K|1889M||201K(2)| 00:40:24 |
| 121 |TABLE ACCESS FULL| TMP_PAID_EXP_AMNT_CNTRNO|1 |29 ||3(0)| 00:00:01 |
|*122 |HASH JOIN RIGHT OUTER||8653K|1650M||201K(2)| 00:40:23 |
| 123 |TABLE ACCESS FULL| TMP_SUM_PRE_CNTRNO|1 |29 ||3(0)| 00:00:01 |
|*124 |HASH JOIN RIGHT OUTER||8653K|1411M||201K(2)| 00:40:22 |
| 125 |TABLE ACCESS FULL| TMP_YEAR_PREM_RG_CNTRNO|1 |25 ||3(0)| 00:00:01 |
|*126 |HASH JOIN RIGHT OUTER||8653K|1204M||201K(2)| 00:40:20 |
| 127 |TABLE ACCESS FULL| TMP_YEAR_PREM_SG_CNTRNO|1 |25 ||3(0)| 00:00:01 |
|*128 |HASH JOIN RIGHT OUTER||8653K|998M||201K(2)| 00:40:19 |
| 129 |TABLE ACCESS FULL| TMP_ACC_DIS_AMNT_CNTRNO|1 |25 ||3(0)| 00:00:01 |
|*130 |HASH JOIN RIGHT OUTER||8653K|792M|165M|201K(2)| 00:40:18 |
| 131 |TABLE ACCESS FULL| TMP_FUND_OUTGO_ACCID|8654K|66M||3589(5)| 00:00:44 |
|*132 |HASHJOIN RIGHT OUTER||8653K|726M|165M|148K(2)| 00:29:41 |
| 133 |TABLE ACCESS FULL| TMP_FUND_INCOME_ACCID|8654K|66M||3728(5)| 00:00:45 |
|*134 |HASHJOIN RIGHT OUTER||8653K|660M|165M| 98472(2)| 00:19:42 |
| 135 |TABLE ACCESS FULL| TMP_FEE_INCOME_TOTAL_ACC_ID |8654K|66M||3728(5)| 00:00:45 |
|*136 |TABLE ACCESS FULL| PRE_MED_FUND_ACC|8653K|594M|| 51822(3)| 00:10:22 |
| 137 |NESTED LOOPS OUTER||1 |152 ||347K(2)| 01:09:29 |
| 138 |NESTED LOOPS OUTER||1 |108 ||347K(2)| 01:09:29 |
|*139 |HASH JOIN SEMI||1 |82 |693M|347K(2)| 01:09:29 |
|*140 |TABLE ACCESS FULL| PRE_MED_FUND_ACC|8653K|594M|| 51699(3)| 00:10:21 |
| 141 |INDEX FAST FULL SCAN|LH_01|101M|970M||152K(2)| 00:30:36 |
| 142 |TABLE ACCESS BY INDEX ROWID|TMP_INSUR_DUR_CNTRNO|1 |26 ||1(0)| 00:00:01 |
|*143 |INDEX UNIQUE SCAN|KEY_TMP_INSUR_DUR_CNTRNO|1 |||0(0)||
| 144 |TABLE ACCESS BY INDEX ROWID|TMP_COST_CENTER_CNTRNO|1 |44 ||1(0)| 00:00:01 |
|*145 |INDEX UNIQUE SCAN|KEY_COST_CENTER_CNTRNO|1 |||0(0)||
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identifiedby operation id):
---------------------------------------------------
4 - access("T1"."APPL_ID"="TMPAP30"."APPL_ID")
5 - access("T1"."APPL_ID"="TMPAP53"."APPL_ID")
6 - access("T1"."APPL_ID"="TMPAP50"."APPL_ID")
7 - access("T1"."APPL_ID"="TMPAP51"."APPL_ID")
8 - access("T1"."APPL_ID"="TMPAP44"."APPL_ID")
12 - access("T1"."APPL_ID"="TMPAP31"."APPL_ID")
filter("T1"."APPL_ID"="TMPAP31"."APPL_ID")
20 - access("T1"."CG_NO"="TMP"."CNTR_NO")
21 - access("T"."CNTR_NO"="TMP1"."CNTR_NO")
23 - access("T"."CNTR_NO"="TMP69"."CNTR_NO")
25 - access("T"."CNTR_NO"="TMPNO30"."CNTR_NO")
27 - access("T"."ACC_ID"="TMPID2046"."ACC_ID")
29 - access("T"."ACC_ID"="TMPID43"."ACC_ID")
31 - access("T"."ACC_ID"="TMPID44"."ACC_ID")
33 - access("T"."ACC_ID"="TMPID56"."ACC_ID")
35 - access("T"."ACC_ID"="TMPID82"."ACC_ID")
37 - access("T"."ACC_ID"="TMPID81"."ACC_ID")
39 - access("T"."ACC_ID"="TMPID65"."ACC_ID")
41 - access("T"."ACC_ID"="TMPID53"."ACC_ID")
43 - access("T"."ACC_ID"="TMPID51"."ACC_ID")
45 - access("T"."ACC_ID"="TMPID50"."ACC_ID")
47 - access("T"."ACC_ID"="TMPID58"."ACC_ID")
49 - access("T"."ACC_ID"="TMPID78"."ACC_ID")
51 - access("T"."ACC_ID"="TMPID79"."ACC_ID")
53 - access("T"."ACC_ID"="TMPID57"."ACC_ID")
55 - access("T"."ACC_ID"="TMPID77"."ACC_ID")
57 - filter("T"."FLAG"='1')
63 - access("T"."CG_ID"="B"."CG_ID" AND "B"."IPSN_NO"=TO_NUMBER("T"."IPSN_NO"))
65 - access("T"."ACC_ID"="TMPID78"."ACC_ID")
67 - access("T"."ACC_ID"="TMPID53"."ACC_ID")
69 - access("T"."ACC_ID"="TMPID65"."ACC_ID")
71 - access("T"."ACC_ID"="TMPID81"."ACC_ID")
73 - access("T"."ACC_ID"="TMPID56"."ACC_ID")
75 - access("T"."ACC_ID"="TMPID43"."ACC_ID")
77 - access("T"."ACC_ID"="TMPID77"."ACC_ID")
79 - access("T"."ACC_ID"="TMPID82"."ACC_ID")
81 - access("T"."ACC_ID"="TMPID69"."ACC_ID")
83 - access("T"."CNTR_NO"="TMP25"."CNTR_NO")
85 - access("T"."CNTR_NO"="TMPNO30"."CNTR_NO")
87 - access("T"."ACC_ID"="TMPID44"."ACC_ID")
89 - access("T"."ACC_ID"="TMPID2046"."ACC_ID")
91 - filter(("T"."FLAG"='2' OR "T"."FLAG"='5'))
93 - access("T"."ACC_ID"="TMPID51"."ACC_ID")
95 - access("T"."ACC_ID"="TMPID50"."ACC_ID")
97 - access("T"."ACC_ID"="TMPID58"."ACC_ID")
99 - access("T"."ACC_ID"="TMPID79"."ACC_ID")
101 - access("T"."ACC_ID"="TMPID57"."ACC_ID")
102 - access("T"."CNTR_NO"="TMP46"."CNTR_NO")
104 - access("T"."CNTR_NO"="TMPNO69"."CNTR_NO")
106 - access("T"."CNTR_NO"="TMPNO30"."CNTR_NO")
108 - access("T"."CNTR_NO"="TMPNO43"."CNTR_NO")
110 - access("T"."CNTR_NO"="TMPNO44"."CNTR_NO")
112 - access("T"."CNTR_NO"="TMPNO56"."CNTR_NO")
114 - access("T"."CNTR_NO"="TMPNO81"."CNTR_NO")
116 - access("T"."CNTR_NO"="TMPNO4"."CNTR_NO")
118 - access("T"."CNTR_NO"="TMPNO65"."CNTR_NO")
120 - access("T"."CNTR_NO"="TMPNO71"."CNTR_NO")
122 - access("T"."CNTR_NO"="TMPNO53"."CNTR_NO")
124 - access("T"."CNTR_NO"="TMPNO51"."CNTR_NO")
126 - access("T"."CNTR_NO"="TMPNO50"."CNTR_NO")
128 - access("T"."CNTR_NO"="TMPNO58"."CNTR_NO")
130 - access("T"."ACC_ID"="TMPID78"."ACC_ID")
132 - access("T"."ACC_ID"="TMPID79"."ACC_ID")
134 - access("T"."ACC_ID"="TMPID57"."ACC_ID")
136 - filter(("T"."FLAG"='4' OR "T"."FLAG"='6'))
139 - access("T"."CG_ID"="B"."CG_ID" AND "B"."IPSN_NO"=TO_NUMBER("T"."IPSN_NO"))
140 - filter("T"."FLAG"='2')
143 - access("T"."CNTR_NO"="TMPNO30"."CNTR_NO")
145 - access("T"."CNTR_NO"="TMP3"."CNTR_NO")
245 rows selected.
是一个insert select。然后其中的select是 一堆union all 组合起来的。通过粗略一看，看的我头晕眼花。
给对方打电话，询问情况，得知开发说以前跑的比现在快
我让对方跑select * fromtable(dbms_xplan.display_awr('0ah5a8dbk28fh'),null,null,'advanced'); 并将内容发给我
其中存在三个执行计划， cost 分别有三个，当前跑的这个是其中cost最大的那个
第一、我不在现场
第二、现在没时间，也没办法详细优化
所以我选择的方案，就是通过coe_xfr_sql_profile.sql 来将执行计划绑定为cost最小的那个!
后来对方领导决定先不kill，因为我和对方说，这里是DML操作，回滚时间会比较长。
这里反应出了问题，首先开发连select的速度都没测，就直接insert，真是。。而且，再弱也应该知道开并行吧?这里也没有开并行
等周二详细优化的时候，思路如下：
1、先检查统计信息，并检查这个SQL产生三个执行计划的主要原因
2、将union all 拆开，分别优化每个SQL(如果能用with as 尝试运用)
3、优化好查询速度之后 开并行跑。这里注意，看并行DML 要打开session级别的并行DML
未完待续……
