优化SQL一条_易贤网_足球游戏_中国足彩网￥体育资讯$

当前位置：首页 > 范文|应用文 > IT技术专栏 > 数据库

优化SQL一条

来源：易贤网阅读：1029 次日期：2014-11-03 11:22:11

温馨提示：易贤网小编为您整理了“优化SQL一条”,方便广大网友查阅！

昨天大半夜接到一条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技术专栏

中国足彩网信息请查看数据库

上一篇：ibatis之sql注入

下一篇：SQLServer中的存储过程

易贤网手机网站地址：优化SQL一条

由于各方面情况的不断调整与变化，易贤网提供的所有考试信息和咨询回复仅供参考，敬请考生以权威部门公布的正式信息和咨询为准！