parallel优化案例

背景：某数据仓库系统 一个ETL流程执行了10多个小时才完成！平时都是1小时

问题定位：

查看ETL时间段内所有SQL的运行时间。
select count(1), sql_id
  from dba_hist_active_sess_history
 where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') >=
       '2018-08-09 19:49:59'
   and to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') <=
       '2018-08-10 02:13:50'
 group by sql_id
 order by 1 desc;
SQL_ID为gv0ahj0f3x1bv的SQL占用了4000+个快照，也就是说这个SQL持续了40000秒+


查看历史执行计划，发现是执行计划突变导致的
select distinct plan_hash_value from dba_hist_sql_plan where  sql_id ='gv0ahj0f3x1bv';
2837655065
3754523820

查看SQL文本
select * from dba_hist_sqltext where sql_id ='gv0ahj0f3x1bv';

INSERT INTO ANNE_WORK.SUP_MID_INSTRUMENT
  (AS_OF_DATE,
   ORG_L1,
   ORG_L2,
   ORG_UNIT_ID,
   ORG_UNIT_ID_BIZ,
   LOB_ID,
   PROD_ID,
   GL_ACCT_ID,
   SRC_ID,
   LOB_L1,
   CURRENCY_CD,
   CUST_NO,
   BIZ_ENTITY_ID,
   CHANNEL,
   CUST_MGR,
   INDUSTRY,
   ACCT_TYPE,
   CUST_ACCT_NO,
   MA_ACCT_NO,
   ACCT_STATUS_CD,
   LOAN_RISK_LVL_CD,
   ACCT_BAL,
   DAILY_AVG_BAL,
   EXTNL_INTEREST_INCM,
   EXTNL_INTEREST_EXPNS,
   TRANS_AMT,
   TRANS_CNT,
   INTEREST_RATE,
   ORGIN_DT,
   DUE_DT,
   ORIG_BAL,
   ORG_TERM,
   ACCRU_BASIS,
   RATE_FLG,
   RC_FREQ,
   KEY_RATE,
   REPRICING_SPREAD,
   LST_REPRICE_DT,
   NXT_REPRICE_DT,
   PMT_TYP,
   PMT_FREQ,
   FORE_CLOSURE,
   YQBZ,
   PRE_DRAW_DT,
   TRAN_BASE_RATE,
   TRAN_END_RATE,
   TRAN_METHOD,
   RATE_METHOD,
   USED_TRAN_METHOD,
   USED_RATE_METHOD,
   ADJ1_RT,
   ADJ2_RT,
   ADJ3_RT,
   ADJ4_RT,
   ADJ5_RT,
   PRE_DRAW_INT,
   FTP_INT_AJUST,
   I_BIZ_INCM,
   I_BIZ_EXPNS,
   INVESTMENT_INCM,
   FAIR_VALUE_CHANGED_INCM,
   EXCHANGE_INCM,
   BIZ_TAX_SURCHARGE,
   OTH_OPERATING_INCM,
   OTH_OPERATING_EXPNS,
   ASSET_LOSS_PRVS,
   NON_OPERATING_INCM,
   NON_OPERATING_EXPNS,
   EXPENSES01,
   EXPENSES02,
   EXPENSES03,
   EXPENSES04,
   EXPENSES05,
   EXPENSES06,
   EXPENSES07,
   EXPENSES08,
   EXPENSES09,
   EXPENSES10,
   EXPENSES11,
   EXPENSES12,
   EXPENSES13,
   EXPENSES14,
   EXPENSES15,
   PRE_TAX_PROFIT,
   INCM_TAX,
   NET_PROFIT,
   CREDIT_RISK_CAPITAL,
   OPERATIONAL_RISK_CAPITAL,
   MARKET_RISK_CAPITAL,
   CAPITAL_COST,
   ECONOMIC_VALUE_ADDED,
   PL_ACCT_ID,
   INVESTMENT_ACCT_ID,
   FAIR_VALUE_CHANGED_ACCT_ID,
   OVERDUE_DAYS,
   BAD_DEBT_IND,
   FTP_INCM,
   FTP_EXPNS,
   INCM_TAX_RATE,
   CAPITAL_COST_RATE,
   BIZ_TAX_RATE,
   GROSS_INCOME,
   ACCT_NUM_MODF,
   PROD_ID_CORE,
   LIAB_TYPE,
   INDV_OPER_LOAN_FLG,
   RWA_WEIGHT,
   ELC_TRANS_CNT,
   PERSON_PREC_NUM,
   OUT_BAL,
   OUT_AVG_BAL)
SELECT /*+ parallel(a,16) */
   A.AS_OF_DATE,
   A.ORG_L1,
   A.ORG_L2,
   A.ORG_UNIT_ID,
   A.ORG_UNIT_ID_BIZ,
   A.LOB_ID,
   A.PROD_ID,
   A.GL_ACCT_ID,
   A.SRC_ID,
   A.LOB_L1,
   A.CURRENCY_CD,
   A.CUST_NO,
   A.BIZ_ENTITY_ID,
   A.CHANNEL,
   A.CUST_MGR,
   A.INDUSTRY,
   A.ACCT_TYPE,
   A.CUST_ACCT_NO,
   A.MA_ACCT_NO,
   A.ACCT_STATUS_CD,
   A.LOAN_RISK_LVL_CD,
   A.ACCT_BAL,
   A.DAILY_AVG_BAL,
   A.EXTNL_INTEREST_INCM,
   A.EXTNL_INTEREST_EXPNS,
   A.TRANS_AMT,
   A.TRANS_CNT,
   A.INTEREST_RATE,
   A.ORGIN_DT,
   A.DUE_DT,
   A.ORIG_BAL,
   A.ORG_TERM,
   A.ACCRU_BASIS,
   A.RATE_FLG,
   A.RC_FREQ,
   A.KEY_RATE,
   A.REPRICING_SPREAD,
   A.LST_REPRICE_DT,
   A.NXT_REPRICE_DT,
   A.PMT_TYP,
   A.PMT_FREQ,
   NVL(TRIM(A.FORE_CLOSURE), 3) || CASE
     WHEN D.CUST_ID IS NOT NULL THEN
      'I'
     ELSE
      'C'
   END,
   A.YQBZ,
   A.PRE_DRAW_DT,
   B.TRAN_BASE_RATE,
   B.TRAN_END_RATE,
   B.TRAN_METHOD,
   B.RATE_METHOD,
   B.USED_TRAN_METHOD,
   B.USED_RATE_METHOD,
   B.ADJ1_RT,
   B.ADJ2_RT,
   B.ADJ3_RT,
   B.ADJ4_RT,
   B.ADJ5_RT,
   A.PRE_DRAW_INT,
   A.FTP_INT_AJUST,
   A.I_BIZ_INCM,
   A.I_BIZ_EXPNS,
   A.INVESTMENT_INCM,
   A.FAIR_VALUE_CHANGED_INCM,
   A.EXCHANGE_INCM,
   A.BIZ_TAX_SURCHARGE,
   A.OTH_OPERATING_INCM,
   A.OTH_OPERATING_EXPNS,
   A.ASSET_LOSS_PRVS,
   A.NON_OPERATING_INCM,
   A.NON_OPERATING_EXPNS,
   A.EXPENSES01,
   A.EXPENSES02,
   A.EXPENSES03,
   A.EXPENSES04,
   A.EXPENSES05,
   A.EXPENSES06,
   A.EXPENSES07,
   A.EXPENSES08,
   A.EXPENSES09,
   A.EXPENSES10,
   A.EXPENSES11,
   A.EXPENSES12,
   A.EXPENSES13,
   A.EXPENSES14,
   A.EXPENSES15,
   A.PRE_TAX_PROFIT,
   A.INCM_TAX,
   A.NET_PROFIT,
   A.CREDIT_RISK_CAPITAL,
   A.OPERATIONAL_RISK_CAPITAL,
   A.MARKET_RISK_CAPITAL,
   A.CAPITAL_COST,
   A.ECONOMIC_VALUE_ADDED,
   A.PL_ACCT_ID,
   A.INVESTMENT_ACCT_ID,
   A.FAIR_VALUE_CHANGED_ACCT_ID,
   A.OVERDUE_DAYS,
   A.BA