INSERT /*+APPEND*/
FIRST WHEN error IS NOT NULL THEN INTO fu_error_log
  (...)
VALUES
  (...) 
WHEN instr_master_id IS NOT NULL AND update_rnk = 1 THEN INTO tmp_fb_prs_desc
  (...)
VALUES
  (...)
WHEN cash_instr_master_id IS NOT NULL THEN INTO tmp_fb_prs_desc
  (...)
VALUES
  (...) 
WHEN instr_master_id IS NULL THEN INTO tmp_fb_prs_desc
  (...)
VALUES
  (...)
  SELECT v2.*,
         CASE
           WHEN v2.fplx_instr_type_cd IS NULL THEN
            'Instr type cd not found'
           ...
           ELSE
            NULL
         END error
  FROM   (SELECT v.*,
                 ...
                 CASE v.instr_discrim_cd
                   WHEN 'OP' THEN
                    decode(v.raw_contract_price, 0, 1, v.raw_contract_price)
                   ELSE
                    NULL
                 END contract_size,
                 (SELECT COUNT(*)
                  FROM   fd_instr_cat_map ficm
                  JOIN   fe_instn fi ON fi.code_domain_id = ficm.code_domain_id
                                 AND    fi.instn_nm = 'Generic Bank'
                  WHERE  ficm.instr_type_cd = v.instr_type_cd
                  AND    rownum < 2) instr_map_id_error
          FROM   (SELECT ...
                         fb_proc.get_price(f.expnd_bid_price, b.bid_price, :b4, fit.instr_discrim_cd) bid_price,
                         ...
                         CASE
                           WHEN fpcc.prs_cusip_code IS NOT NULL THEN
                            'CSHDOM'
                           ELSE
                            fit.instr_type_cd
                         END instr_type_cd,
                         ...
                         rank() over(PARTITION BY fim.cusip ORDER BY fim.cusip, fim.is_actv DESC, fim.expiration_dt DESC) update_rnk
                  FROM   (SELECT a_in.*,
                                 rank() over(PARTITION BY a_in.cusip ORDER BY a_in.record_seq_num DESC) rnk
                          FROM   fb_prs_desc_a a_in) a
                  ...
                  LEFT   JOIN ff_instr_type fit ON fit.instr_type_cd =
                                                   decode(a.security_type || ':' || a.security_modifier || ':' ||
                                                          a.security_calc_cd || ':' || d.abs_factor,
                                                          '7:2:R:',
                                                          'GVAGCY',
                                                          fpsc.fplx_instr_type_cd)
                  LEFT   JOIN fb_prs_int_freq fpif ON fpif.prs_int_freq = a.int_freq
                  ...
                  LEFT   JOIN (SELECT fim_in.*,
                                     fo.expiration_dt,
                                     fo.instr_master_id fo_instr_master_id
                              FROM   fi_instr_master fim_in
                              LEFT   JOIN fi_option fo ON fim_in.instr_master_id = fo.instr_master_id) fim ON fim.cusip =
                                                                                                              a.cusip
                                                                                                       AND    fim.data_prov_id IN
                                                                                                              (:b3, :b2)
                                                                                                       AND    fim.definer_id IS NULL
                                                                                                       AND    (fim.fo_instr_master_id IS NULL OR
                                                                                                             fim.instr_type_cd =
                                                                                                             'RIGHTS' OR
                                                                                                             (fim.is_actv = 1 AND
                                                                                                             fim.expiration_dt >= :b1) OR
                                                                                                             extract(YEAR FROM
                                                                                                                       fb_proc.get_date(a.mat_opt_exp_dt)) =
                                                                                                             extract(YEAR FROM
                                                                                                                       fim.expiration_dt))
                  LEFT   JOIN fb_prs_cash_cusips fpcc ON fpcc.prs_cusip_code = a.cusip
                  ..
                  LEFT   JOIN fb_prs_int_calc_cd fpicc ON fpicc.prs_int_calc_cd = a.int_calc_cd
                  WHERE  a.rnk = 1
                  AND    rownum > 0) v
          LEFT   JOIN fd_instr_cat_map ficm ON ficm.instr_type_cd = v.instr_type_cd) v2
  ORDER  BY v2.record_seq_num,
            v2.cusip


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.19       0.20          0         72          0           0
Execute      1    224.99     919.61     389270    1940599     331076      409716
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    225.18     919.81     389270    1940671     331076      409716


********************************************************************************

UPDATE (SELECT fim.*,
               ...
        FROM   tmp_fb_prs_desc td
        JOIN   fi_instr_master fim ON fim.instr_master_id = td.instr_master_id
        WHERE  td.is_insert = 0)
SET    ...


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.05       0.16         97       1176          0           0
Execute      1     23.67     331.45      48576      39931     439455      409314
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     23.72     331.62      48673      41107     439455      409314

********************************************************************************

INSERT /*+APPEND*/
INTO fi_instr_master fim
  (...)
  SELECT ...
  FROM   tmp_fb_prs_desc
  WHERE  is_insert = 1



call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.61       1.62        293      11545          0           0
Execute      1      0.71      11.55      14262      13315       2858         401
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.32      13.18      14555      24860       2858         401

BEGIN
  INSERT
  INTO   tmp
  SELECT *
  FROM   t1,
         t2
  WHERE  t1.id = t2.id;
  --
  INSERT
  INTO   t8
         (a, b, c)
  SELECT a, b, c
  FROM   tmp
  WHERE  a = :x;
  --
  UPDATE (SELECT t9.*,
                 tmp.d,
                 tmp.e
          FROM   tmp,
                 t9
          WHERE  tmp.id = t9.id)
  SET    r = d,
         s = e;
  --
  COMMIT;
END;