昨天大半夜接到一条SQL，反应说很慢
SQL如下（巨长无比）
select * from table(dbms_xplan.display_cursor(lower('0ah5a8dbk28fh')));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0ah5a8dbk28fh, 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 | HASH JOIN 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 | HASH JOIN 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 | HASH JOIN 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 | HASH JOIN 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 | HASH JOIN 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 | HASH JOIN 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 | HASH JOIN 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 | HASH JOIN 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 | HASH JOIN 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 (identified by 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 * from table(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
未完待续....
烂尾了，询问几次后，对方优化的欲望并不强烈，哎，自己的系统 自己都不着急，别人也没办法
还是那句话，优化虽易，乙方不易，且行且他妈珍惜吧
扫一扫
378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
