Avatar of dba_shashi
dba_shashi
Flag for United States of America asked on

Taking too much time Inserting bulk load in oracle.

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;
/
Oracle Database

Avatar of undefined
Last Comment
dba_shashi

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
oleggold

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
schwertner

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dba_shashi

ASKER
Hi
Thanks for these valuable suggestion.
But all the requirement above is necessity here and nothing should be changed. Please guide me if any modification can be done further?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck