troubleshooting Question

Taking too much time Inserting bulk load in oracle.

Avatar of dba_shashi
dba_shashiFlag for United States of America asked on
Oracle Database
5 Comments4 Solutions895 ViewsLast Modified:
Hi Experts
Please have a look at below script and help me load data faster. I have over 600 Million Records to be inserted
.
Many Thanks

CREATE OR REPLACE PROCEDURE GSDWRPTUSR.proc_ods_data_load (
   rec_ret_code   OUT   NUMBER,
   xreturn_msg    OUT   VARCHAR2
)
AS
   TYPE rec_acct IS RECORD (
      unique_acct_cd   gsdwrptusr.stag_tab_ods_dly.unique_acct_cd%TYPE,
      pos_date         gsdwrptusr.stag_tab_ods_dly.pos_date%TYPE
   );

   TYPE rec_accttype IS TABLE OF rec_acct;

   c_rec_accttype   rec_accttype;

--CURSOR FOR GETTING THE RECORDS FOR ACCT_HIST
   CURSOR accthistload
   IS
      SELECT DISTINCT unique_acct_cd, MIN (pos_date)
                 FROM stag_tab_ods_dly
             GROUP BY unique_acct_cd;

   TYPE rec_issu IS RECORD (
      unique_sec_cd        gsdwrptusr.stag_tab_ods_dly.unique_sec_cd%TYPE,
      reg_cusip            gsdwrptusr.stag_tab_ods_dly.reg_cusip%TYPE,
      ticker               gsdwrptusr.stag_tab_ods_dly.ticker%TYPE,
      sec_issue_nm         gsdwrptusr.stag_tab_ods_dly.sec_issue_nm%TYPE,
      gic_sector           gsdwrptusr.stag_tab_ods_dly.gic_sector%TYPE,
      gic_industry         gsdwrptusr.stag_tab_ods_dly.gic_industry%TYPE,
      sec_type_cd          gsdwrptusr.stag_tab_ods_dly.sec_type_cd%TYPE,
      mat_date             gsdwrptusr.stag_tab_ods_dly.mat_date%TYPE,
      curr_cd              gsdwrptusr.stag_tab_ods_dly.curr_cd%TYPE,
      price_multiplier     gsdwrptusr.stag_tab_ods_dly.price_multiplier%TYPE,
      country_cd           gsdwrptusr.stag_tab_ods_dly.country_cd%TYPE,
      coupon_rate          gsdwrptusr.stag_tab_ods_dly.coupon_rate%TYPE,
      shares_outstanding   gsdwrptusr.stag_tab_ods_dly.shares_outstanding%TYPE,
      inv_type_cd          gsdwrptusr.stag_tab_ods_dly.inv_type_cd%TYPE,
      pos_date             gsdwrptusr.stag_tab_ods_dly.pos_date%TYPE
   );

   TYPE rec_issutype IS TABLE OF rec_issu;

   c_rec_issutype   rec_issutype;

--CURSOR FOR GETTING THE RECORDS FOR ISSU_HIST
   CURSOR issuhistload
   IS
      SELECT DISTINCT unique_sec_cd, reg_cusip, ticker, sec_issue_nm,
                      gic_sector, gic_industry, sec_type_cd, mat_date,
                      curr_cd, price_multiplier, country_cd, coupon_rate,
                      shares_outstanding, inv_type_cd, MIN (pos_date)
                                                                     pos_date
                 FROM gsdwrptusr.stag_tab_ods_dly
             GROUP BY unique_sec_cd,
                      reg_cusip,
                      ticker,
                      sec_issue_nm,
                      gic_sector,
                      gic_industry,
                      sec_type_cd,
                      mat_date,
                      curr_cd,
                      price_multiplier,
                      country_cd,
                      coupon_rate,
                      shares_outstanding,
                      inv_type_cd
             ORDER BY pos_date;

   --CURSOR FOR GETTING THE RECORDS FOR POSN_HIST
   TYPE rec_posn IS RECORD (
      sec_sok           gsdwrptusr.issu_hist.sec_sok%TYPE,
      acct_sok          gsdwrptusr.acct_hist.acct_sok%TYPE,
      pos_date          gsdwrptusr.stag_tab_ods_dly.pos_date%TYPE,
      prc_base          gsdwrptusr.stag_tab_ods_dly.prc_base%TYPE,
      prc_local         gsdwrptusr.stag_tab_ods_dly.prc_local%TYPE,
      base_cost         gsdwrptusr.stag_tab_ods_dly.base_cost%TYPE,
      local_cost        gsdwrptusr.stag_tab_ods_dly.local_cost%TYPE,
      share_par_value   gsdwrptusr.stag_tab_ods_dly.share_par_value%TYPE,
      spot_rate         gsdwrptusr.stag_tab_ods_dly.spot_rate%TYPE,
      sec_issue_nm      gsdwrptusr.stag_tab_ods_dly.sec_issue_nm%TYPE
   );

   TYPE rec_posntype IS TABLE OF rec_posn;

   c_rec_posntype   rec_posntype;

   CURSOR posnhistload
   IS
      SELECT   (SELECT sec_sok
                  FROM issu_hist
                 WHERE sec_id = c.unique_sec_cd AND ROWNUM = 1),
               (SELECT acct_sok
                  FROM acct_hist
                 WHERE acct_id = c.unique_acct_cd AND ROWNUM = 1), c.pos_date,
               c.prc_base, c.prc_local, c.base_cost, c.local_cost,
               c.share_par_value, c.spot_rate, c.sec_issue_nm
          FROM stag_tab_ods_dly c
      ORDER BY c.pos_date;

   exit_on_error    EXCEPTION;
BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

   DECLARE
      exit_on_error   EXCEPTION;
   BEGIN
      rec_ret_code := 0;

      --RAISE EXCEPTION IN CASE OF FAILURE
      IF rec_ret_code != 0
      THEN
         RAISE exit_on_error;
      END IF;

      OPEN accthistload;

      LOOP
         FETCH accthistload
         BULK COLLECT INTO c_rec_accttype LIMIT 100000;

         DBMS_OUTPUT.put_line (   'Iteration count for acct_hist -'
                               || TO_CHAR (c_rec_accttype.COUNT)
                              );
         FORALL i IN 1 .. c_rec_accttype.COUNT
            INSERT      /*+ parallel (6,1) */INTO acct_hist
                        (acct_id, acct_sok,
                         data_src_id,
                         version_start_tmsmp,
                         version_end_tmsmp,
                         crte_tstp,
                         crte_pgm
                        )
                 VALUES (c_rec_accttype (i).unique_acct_cd, acct_seq.NEXTVAL,
                         'O',
                         TO_TIMESTAMP (c_rec_accttype (i).pos_date,
                                       'DD-MON-RR HH.MI.SS.FF AM'
                                      ),
                         TO_TIMESTAMP ('30-SEP-11 12.00.00.000000000 AM',
                                       'DD-MON-RR HH.MI.SS.FF AM'
                                      ),
                         TO_TIMESTAMP (SYSDATE, 'DD-MON-RR HH.MI.SS.FF AM'),
                         'BATCH'
                        );
         COMMIT;
         EXIT WHEN accthistload%NOTFOUND;
      END LOOP;

      --RAISE EXCEPTION IN CASE OF FAILURE
      IF rec_ret_code != 0
      THEN
         RAISE exit_on_error;
      END IF;

      OPEN issuhistload;

      LOOP
         FETCH issuhistload
         BULK COLLECT INTO c_rec_issutype LIMIT 100000;

         DBMS_OUTPUT.put_line (   'Iteration count for issu_hist -'
                               || TO_CHAR (c_rec_issutype.COUNT)
                              );
         FORALL i IN 1 .. c_rec_issutype.COUNT
            INSERT      /*+ parallel (6,1) */INTO gsdwrptusr.issu_hist hist
                        (hist.sec_sok, hist.sec_id,
                         hist.data_src_id, hist.version_start_tmsmp,
                         hist.version_end_tmsmp,
                         hist.crte_tstp, hist.crte_pgm, hist.cusip_id,
                         hist.ticker_id,
                         hist.pref_iss_nme,
                         hist.gics_sector_cde,
                         hist.gics_indus_cde,
                         hist.iss_typ,
                         hist.mat_exp_tmsmp,
                         hist.denom_curr_cde,
                         hist.prc_mltplr_crte,
                         hist.issuance_cntry_nme,
                         hist.cpn_crte,
                         hist.shr_outst_cqty,
                         hist.inv_type_cde
                        )
                 VALUES (issu_seq.NEXTVAL, c_rec_issutype (i).unique_sec_cd,
                         'O', c_rec_issutype (i).pos_date,
                         TO_TIMESTAMP ('30-SEP-11 12.00.00',
                                       'DD-MON-RR HH.MI.SS'
                                      ),
                         SYSDATE, 'BATCH', c_rec_issutype (i).reg_cusip,
                         c_rec_issutype (i).ticker,
                         c_rec_issutype (i).sec_issue_nm,
                         c_rec_issutype (i).gic_sector,
                         c_rec_issutype (i).gic_industry,
                         c_rec_issutype (i).sec_type_cd,
                         c_rec_issutype (i).mat_date,
                         c_rec_issutype (i).curr_cd,
                         c_rec_issutype (i).price_multiplier,
                         c_rec_issutype (i).country_cd,
                         c_rec_issutype (i).coupon_rate,
                         c_rec_issutype (i).shares_outstanding,
                         c_rec_issutype (i).inv_type_cd
                        );
         COMMIT;
         EXIT WHEN issuhistload%NOTFOUND;
      END LOOP;

      CLOSE issuhistload;

      INSERT      /*+ parallel (6,1) */INTO issu_temp_tab
                  (sec_id, sec_sok, version_start_tmsmp, version_end_tmsmpn)
         SELECT DISTINCT b.sec_id, b.sec_sok, b.version_start_tmsmp,
                         (a.version_start_tmsmp - 1) version_end_tmsmp_new
                    FROM gsdwrptusr.issuview a, gsdwrptusr.issuview b
                   WHERE a.sec_id = b.sec_id
                     AND b.ranking = a.ranking + 1
                     AND a.ranking > 0
                ORDER BY version_start_tmsmp DESC;

      --INSERTING VALUES IN TO POSN_HIST TABLE
      OPEN posnhistload;

      LOOP
         FETCH posnhistload
         BULK COLLECT INTO c_rec_posntype LIMIT 100000;

         DBMS_OUTPUT.put_line (   'Iteration count for posn_hist -'
                               || TO_CHAR (c_rec_posntype.COUNT)
                              );
         FORALL i IN 1 .. c_rec_posntype.COUNT
            INSERT INTO gsdwrptusr.posn_hist
                        (pos_sok, acct_sok,
                         sec_sok, data_src_id,
                         crte_tstp,
                         crte_pgm,
                         holding_dte,
                         bkpg_curr_book_cprc,
                         local_curr_book_cprc,
                         bkpg_unit_cost_camt,
                         local_unit_cost_camt,
                         qty_cqty,
                         spot_rate
                        )
                 VALUES (posn_seq.NEXTVAL, c_rec_posntype (i).acct_sok,
                         c_rec_posntype (i).sec_sok, 'O',
                         TO_TIMESTAMP (SYSDATE, 'DD-MON-RR HH.MI.SS.FF AM'),
                         'BATCH',
                         TO_TIMESTAMP (c_rec_posntype (i).pos_date,
                                       'DD-MON-RR HH.MI.SS.FF AM'
                                      ),
                         c_rec_posntype (i).prc_base,
                         c_rec_posntype (i).prc_local,
                         c_rec_posntype (i).base_cost,
                         c_rec_posntype (i).local_cost,
                         c_rec_posntype (i).share_par_value,
                         c_rec_posntype (i).spot_rate
                        );
         COMMIT;
         EXIT WHEN posnhistload%NOTFOUND;
      END LOOP;

      CLOSE posnhistload;

      proc_issu_update ();
--THIS HAS TO BE UN-COMMENTED POST TESTING THE DATA

   --DELETE FROM ISSU_TEMP_TAB
--DELETE FROM STG_ACCT;

   --CATCH THE RAISED EXCEPTION AND ROLLBACK THE TRANSACTION
--ALSO SHOW THE EXCEPTION MESSAGE.
   EXCEPTION
      WHEN exit_on_error OR NO_DATA_FOUND
      THEN
         BEGIN
            ROLLBACK;
            rec_ret_code := 1;
            xreturn_msg := SUBSTR (SQLERRM, 1, 256);
         END;
      WHEN OTHERS
      THEN
         BEGIN
            ROLLBACK;
            rec_ret_code := 1;
            xreturn_msg := SUBSTR (SQLERRM, 1, 256);
         END;
   END;
END;
/
ASKER CERTIFIED SOLUTION
oleggold

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros