優(yōu)化SQL一條_易賢網(wǎng)

當(dāng)前位置：首頁 > 范文|應(yīng)用文 > IT技術(shù)專欄 > 數(shù)據(jù)庫

優(yōu)化SQL一條

來源：易賢網(wǎng) 閱讀：1152 次日期：2014-11-03 11:22:11

溫馨提示：易賢網(wǎng)小編為您整理了“優(yōu)化SQL一條”,方便廣大網(wǎng)友查閱！

昨天大半夜接到一條SQL，反應(yīng)說很慢，我非常憤怒，經(jīng)過詢問，三個(gè)月才需要跑這個(gè)SQL一次，你tm非要在馬上法定節(jié)假日了跑它嗎?

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.

是一個(gè)insert select。然后其中的select是一堆union all 組合起來的。通過粗略一看，看的我頭暈眼花。

給對(duì)方打電話，詢問情況，得知開發(fā)說以前跑的比現(xiàn)在快

我讓對(duì)方跑select * from table(dbms_xplan.display_awr('0ah5a8dbk28fh'),null,null,'advanced'); 并將內(nèi)容發(fā)給我

其中存在三個(gè)執(zhí)行計(jì)劃， cost 分別有三個(gè)，當(dāng)前跑的這個(gè)是其中cost最大的那個(gè)

第一、我不在現(xiàn)場

第二、現(xiàn)在沒時(shí)間，也沒辦法詳細(xì)優(yōu)化

所以我選擇的方案，就是通過coe_xfr_sql_profile.sql 來將執(zhí)行計(jì)劃綁定為cost最小的那個(gè)!

后來對(duì)方領(lǐng)導(dǎo)決定先不kill，因?yàn)槲液蛯?duì)方說，這里是DML操作，回滾時(shí)間會(huì)比較長。

這里反應(yīng)出了問題，首先開發(fā)連select的速度都沒測，就直接insert，真是。。而且，再弱也應(yīng)該知道開并行吧?這里也沒有開并行

等周二詳細(xì)優(yōu)化的時(shí)候，思路如下：

1、先檢查統(tǒng)計(jì)信息，并檢查這個(gè)SQL產(chǎn)生三個(gè)執(zhí)行計(jì)劃的主要原因

2、將union all 拆開，分別優(yōu)化每個(gè)SQL(如果能用with as 嘗試運(yùn)用)

3、優(yōu)化好查詢速度之后開并行跑。這里注意，看并行DML 要打開session級(jí)別的并行DML

未完待續(xù)……

更多信息請查看IT技術(shù)專欄

更多信息請查看數(shù)據(jù)庫

上一篇：ibatis之sql注入

下一篇：SQLServer中的存儲(chǔ)過程

易賢網(wǎng)手機(jī)網(wǎng)站地址：優(yōu)化SQL一條

由于各方面情況的不斷調(diào)整與變化，易賢網(wǎng)提供的所有考試信息和咨詢回復(fù)僅供參考，敬請考生以權(quán)威部門公布的正式信息和咨詢?yōu)闇?zhǔn)！

相關(guān)閱讀數(shù)據(jù)庫

解決MongoDB 排序超過內(nèi)存限制的問題08月04日

Windows下MongoDb簡單配置教程08月04日

Mongodb實(shí)戰(zhàn)之全文搜索功能08月04日

MongoDB 管道的介紹及操作符實(shí)例08月04日

在Linux服務(wù)器中配置mongodb環(huán)境的步驟08月04日

mac下安裝和配置mongodb的步驟詳解08月04日

Mongo復(fù)制集同步驗(yàn)證的實(shí)例詳解08月04日

MongoDB 中Limit與Skip的使用方法詳解08月04日

Oracle刪除數(shù)據(jù)報(bào)ORA 02292錯(cuò)誤的巧妙解決方法08月04日

Oracle查詢最近幾天每小時(shí)歸檔日志產(chǎn)生數(shù)量的腳本寫法08月04日

Oracle查看表結(jié)構(gòu)命令詳解08月04日

Oracle 11g如何清理數(shù)據(jù)庫的歷史日志詳解08月04日

Oracle表中重復(fù)數(shù)據(jù)去重的方法實(shí)例詳解08月04日

WIN7下ORACLE10g服務(wù)端和客戶端的安裝圖文教程08月04日

Oracle查看表結(jié)構(gòu)的幾種方法示例代碼08月04日

pl/sql連接遠(yuǎn)程oracle服務(wù)器的配置教程08月04日

IOS 數(shù)據(jù)庫升級(jí)數(shù)據(jù)遷移的實(shí)例詳解08月04日

mysql exists與not exists實(shí)例詳解08月04日

關(guān)于Windows10下解決MySQL5.5數(shù)據(jù)庫命令行中文亂碼問題08月04日

細(xì)說MySQL死鎖與日志二三事08月04日

易賢網(wǎng)移動(dòng)網(wǎng)站

2025國考·省考課程試聽報(bào)名

報(bào)班類型
姓名
手機(jī)號(hào)
驗(yàn)證碼

午夜国产狂喷潮在线观看|国产AⅤ精品一区二区久久|中文字幕AV中文字幕|国产看片高清在线