优化SQL一条

2014-11-03来源：易贤网

昨天大半夜接到一条SQL，反应说很慢，我非常愤怒，经过询问，三个月才需要跑这个SQL一次，你tm非要在马上法定节假日了跑它吗?

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

---------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------

| 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

未完待续……

更多信息请查看IT技术专栏

推荐信息

解决MongoDB 排序超过内存限制的问题

Windows下MongoDb简单配置教程

Mongodb实战之全文搜索功能

MongoDB 管道的介绍及操作符实例

在Linux服务器中配置mongodb环境的步骤

mac下安装和配置mongodb的步骤详解

Mongo复制集同步验证的实例详解

MongoDB 中Limit与Skip的使用方法详解

Oracle删除数据报ORA 02292错误的巧妙解决方法

Oracle查询最近几天每小时归档日志产生数量的脚本写法

Oracle查看表结构命令详解

Oracle 11g如何清理数据库的历史日志详解

Oracle表中重复数据去重的方法实例详解

WIN7下ORACLE10g服务端和客户端的安装图文教程

Oracle查看表结构的几种方法示例代码

pl/sql连接远程oracle服务器的配置教程

IOS 数据库升级数据迁移的实例详解

mysql exists与not exists实例详解

关于Windows10下解决MySQL5.5数据库命令行中文乱码问题

细说MySQL死锁与日志二三事