优化SQL一条

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


未完待续....

烂尾了,询问几次后,对方优化的欲望并不强烈,哎,自己的系统 自己都不着急,别人也没办法

还是那句话,优化虽易,乙方不易,且行且他妈珍惜吧

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值