Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need to increase performance-bulk collect in cursor with limit and in the for loop inserting into th

938190Oct 28 2013 — edited Oct 28 2013

Hi all,

I have a performance issue in the below code,where i am trying to insert the data from table_stg into target_tab and in parent_tab tables and then to child tables via cursor with bulk collect .the target_tab and parent_tab are huge tables and have a row wise trigger enabled on it .the trigger is mandatory . This timetaken for this block to execute is 5000 seconds.Now my requirement is to reduce it to 5 to 10 mins.

can someone please guide me here.Its bit urgent .Awaiting for your response.

declare

vmax_Value NUMBER(5);

  vcnt number(10);

  id_val number(20);

  pc_id number(15);

  vtable_nm VARCHAR2(100);

  vstep_no  VARCHAR2(10);

  vsql_code VARCHAR2(10);

  vsql_errm varchar2(200);

  vtarget_starttime timestamp;

  limit_in number :=10000;

  idx           number(10);

          cursor stg_cursor is

         select

               DESCRIPTION,

               SORT_CODE,

               ACCOUNT_NUMBER,

                 to_number(to_char(CORRESPONDENCE_DATE,'DD')) crr_day,

                 to_char(CORRESPONDENCE_DATE,'MONTH') crr_month,

                 to_number(substr(to_char(CORRESPONDENCE_DATE,'DD-MON-YYYY'),8,4)) crr_year,

               PARTY_ID,

               GUID,

               PAPERLESS_REF_IND,

               PRODUCT_TYPE,

               PRODUCT_BRAND,

               PRODUCT_HELD_ID,

               NOTIFICATION_PREF,

               UNREAD_CORRES_PERIOD,

               EMAIL_ID,

               MOBILE_NUMBER,

               TITLE,

               SURNAME,

               POSTCODE,

               EVENT_TYPE,

               PRIORITY_IND,

               SUBJECT,

               EXT_PRD_ID_TX,

               EXT_PRD_HLD_ID_TX,

               EXT_SYS_ID,

               EXT_PTY_ID_TX,

               ACCOUNT_TYPE_CD,

               COM_PFR_TYP_TX,

               COM_PFR_OPT_TX,

               COM_PFR_RSN_CD

         from  table_stg;

type rec_type is table of stg_rec_type index by pls_integer;

v_rt_all_cols rec_type;

BEGIN

  vstep_no   := '0';

 

  vmax_value := 0;

  vtarget_starttime := systimestamp;

  id_val    := 0;

  pc_id     := 0;

  success_flag := 0;

          vstep_no  := '1';

          vtable_nm := 'before cursor';

    OPEN stg_cursor;

          vstep_no  := '2';

          vtable_nm := 'After cursor';

   LOOP

          vstep_no  := '3';

          vtable_nm := 'before fetch';

--loop

    FETCH stg_cursor BULK COLLECT INTO v_rt_all_cols LIMIT limit_in;

              vstep_no  := '4';

              vtable_nm := 'after fetch';

--EXIT WHEN v_rt_all_cols.COUNT = 0;

    EXIT WHEN stg_cursor%NOTFOUND;

FOR i IN 1 .. v_rt_all_cols.COUNT

  LOOP

 

   dbms_output.put_line(upper(v_rt_all_cols(i).event_type));

    if (upper(v_rt_all_cols(i).event_type) = upper('System_enforced')) then

              vstep_no  := '4.1';

              vtable_nm := 'before seq sel';

          select PC_SEQ.nextval into pc_id from dual;

              vstep_no  := '4.2';

              vtable_nm := 'before insert corres';

          INSERT INTO target1_tab

                       (ID,

                        PARTY_ID,

                        PRODUCT_BRAND,

                        SORT_CODE,

                        ACCOUNT_NUMBER,

                        EXT_PRD_ID_TX,         

                        EXT_PRD_HLD_ID_TX,

                        EXT_SYS_ID,

                        EXT_PTY_ID_TX,

                        ACCOUNT_TYPE_CD,

                        COM_PFR_TYP_TX,

                        COM_PFR_OPT_TX,

                        COM_PFR_RSN_CD,

                        status)

         VALUES

                        (pc_id,

                         v_rt_all_cols(i).party_id,

                         decode(v_rt_all_cols(i).product_brand,'LTB',2,'HLX',1,'HAL',1,'BOS',3,'VER',4,0),

                         v_rt_all_cols(i).sort_code,

                         'XXXX'||substr(trim(v_rt_all_cols(i).ACCOUNT_NUMBER),length(trim(v_rt_all_cols(i).ACCOUNT_NUMBER))-3,4),

                         v_rt_all_cols(i).EXT_PRD_ID_TX,

                         v_rt_all_cols(i).EXT_PRD_HLD_ID_TX,

                         v_rt_all_cols(i).EXT_SYS_ID,

                         v_rt_all_cols(i).EXT_PTY_ID_TX,

                         v_rt_all_cols(i).ACCOUNT_TYPE_CD,

                         v_rt_all_cols(i).COM_PFR_TYP_TX,

                         v_rt_all_cols(i).COM_PFR_OPT_TX,

                         v_rt_all_cols(i).COM_PFR_RSN_CD,

                         NULL);

              vstep_no  := '4.3';

              vtable_nm := 'after insert corres';

    else

          select COM_SEQ.nextval into id_val from dual;

              vstep_no  := '6';

              vtable_nm := 'before insertcomm';

      if (upper(v_rt_all_cols(i).event_type) = upper('REMINDER')) then

            vstep_no  := '6.01';

              vtable_nm := 'after if insertcomm';

          insert into parent_tab

             (ID ,

             CTEM_CODE,

             CHA_CODE,            

             CT_CODE,                           

             CONTACT_POINT_ID,             

             SOURCE,

             RECEIVED_DATE,                             

             SEND_DATE,

             RETRY_COUNT)

          values

             (id_val,

              lower(v_rt_all_cols(i).event_type), 

              decode(v_rt_all_cols(i).product_brand,'LTB',2,'HLX',1,'HAL',1,'BOS',3,'VER',4,0),

              'Email',

              v_rt_all_cols(i).email_id,

              'IADAREMINDER',

              systimestamp,

              systimestamp,

              0);  

     else

            vstep_no  := '6.02';

              vtable_nm := 'after else insertcomm';

          insert into parent_tab

             (ID ,

             CTEM_CODE,

             CHA_CODE,            

             CT_CODE,                           

             CONTACT_POINT_ID,             

             SOURCE,

             RECEIVED_DATE,                             

             SEND_DATE,

             RETRY_COUNT)

          values

             (id_val,

              lower(v_rt_all_cols(i).event_type), 

              decode(v_rt_all_cols(i).product_brand,'LTB',2,'HLX',1,'HAL',1,'BOS',3,'VER',4,0),

              'Email',

              v_rt_all_cols(i).email_id,

              'CORRESPONDENCE',

              systimestamp,

              systimestamp,

              0); 

       

        END if; 

              vstep_no  := '6.11';

              vtable_nm := 'before chop';

         if (v_rt_all_cols(i).ACCOUNT_NUMBER is not null) then 

                  v_rt_all_cols(i).ACCOUNT_NUMBER := 'XXXX'||substr(trim(v_rt_all_cols(i).ACCOUNT_NUMBER),length(trim(v_rt_all_cols(i).ACCOUNT_NUMBER))-3,4);

          insert into child_tab

             (COM_ID,                                            

             KEY,                                                                                                                                            

             VALUE)

          values

            (id_val,

             'IB.Correspondence.AccountNumberMasked',

             v_rt_all_cols(i).ACCOUNT_NUMBER);

         end if;

              vstep_no  := '6.1';

              vtable_nm := 'before stateday';

         if (v_rt_all_cols(i).crr_day is not null) then 

          insert into child_tab

             (COM_ID,                                            

             KEY,                                                                                                                                            

             VALUE)

          values

            (id_val,

             --'IB.Correspondence.Date.Day',

             'IB.Crsp.Date.Day',

             v_rt_all_cols(i).crr_day);

         end if;

              vstep_no  := '6.2';

              vtable_nm := 'before statemth';

         if (v_rt_all_cols(i).crr_month is not null) then 

          insert into child_tab

             (COM_ID,                                            

             KEY,                                                                                                                                            

             VALUE)

          values

            (id_val,

             --'IB.Correspondence.Date.Month',

             'IB.Crsp.Date.Month',

             v_rt_all_cols(i).crr_month);

         end if;

              vstep_no  := '6.3';

              vtable_nm := 'before stateyear';

         if (v_rt_all_cols(i).crr_year is not null) then 

          insert into child_tab

             (COM_ID,                                            

             KEY,                                                                                                                                            

             VALUE)

          values

            (id_val,

             --'IB.Correspondence.Date.Year',

             'IB.Crsp.Date.Year',

             v_rt_all_cols(i).crr_year);

         end if;

              vstep_no  := '7';

              vtable_nm := 'before type';

           if (v_rt_all_cols(i).product_type is not null) then

              insert into child_tab

                 (COM_ID,                                            

                 KEY,                                                                                                                                        

                 VALUE)

              values

                (id_val,

                 'IB.Product.ProductName',

               v_rt_all_cols(i).product_type);

            end if;

              vstep_no  := '9';

              vtable_nm := 'before title';         

          if (trim(v_rt_all_cols(i).title) is not null) then

          insert into child_tab

             (COM_ID,                                            

             KEY,                                                                                                                                            

             VALUE )

          values

            (id_val,

             'IB.Customer.Title',

             trim(v_rt_all_cols(i).title));

          end if;

              vstep_no  := '10';

              vtable_nm := 'before surname';

          if (v_rt_all_cols(i).surname is not null) then

            insert into child_tab

               (COM_ID,                                            

               KEY,                                                                                                                                          

               VALUE)

            values

              (id_val,

              'IB.Customer.LastName',

              v_rt_all_cols(i).surname);

          end if;

                        vstep_no  := '12';

                        vtable_nm := 'before postcd';

          if (trim(v_rt_all_cols(i).POSTCODE) is not null) then

          insert into child_tab

             (COM_ID,                                            

             KEY,                                                                                                                                            

             VALUE)                              

           values

            (id_val,

             'IB.Customer.Addr.PostCodeMasked',

              substr(replace(v_rt_all_cols(i).POSTCODE,' ',''),length(replace(v_rt_all_cols(i).POSTCODE,' ',''))-2,3));

          end if;

                        vstep_no  := '13';

                        vtable_nm := 'before subject';

          if (trim(v_rt_all_cols(i).SUBJECT) is not null) then

          insert into child_tab

             (COM_ID,                                            

             KEY,                                                                                                                                            

             VALUE)                              

           values

            (id_val,

             'IB.Correspondence.Subject',

              v_rt_all_cols(i).subject);

          end if;

                        vstep_no  := '14';

                        vtable_nm := 'before inactivity';

          if (trim(v_rt_all_cols(i).UNREAD_CORRES_PERIOD) is null or

              trim(v_rt_all_cols(i).UNREAD_CORRES_PERIOD) = '3' or

              trim(v_rt_all_cols(i).UNREAD_CORRES_PERIOD) = '6' or

              trim(v_rt_all_cols(i).UNREAD_CORRES_PERIOD) = '9') then

          insert into child_tab

             (COM_ID,                                            

             KEY,                                                                                                                                            

             VALUE)                              

           values

            (id_val,

             'IB.Correspondence.Inactivity',

              v_rt_all_cols(i).UNREAD_CORRES_PERIOD);

          end if;

                      vstep_no  := '14.1';

                      vtable_nm := 'after notfound';

   

    end if;

                      vstep_no  := '15';

                      vtable_nm := 'after notfound';

    END LOOP;

    end loop;

                      vstep_no  := '16';

                      vtable_nm := 'before closecur';

    CLOSE stg_cursor;

                      vstep_no  := '17';

                      vtable_nm := 'before commit';

  

    DELETE FROM table_stg;

  

  COMMIT;

                      vstep_no  := '18';

                      vtable_nm := 'after commit';

EXCEPTION

WHEN OTHERS THEN

  ROLLBACK;

  success_flag := 1;

  vsql_code := SQLCODE;

  vsql_errm := SUBSTR(sqlerrm,1,200);

  error_logging_pkg.inserterrorlog('samp',vsql_code,vsql_errm, vtable_nm,vstep_no);

  RAISE_APPLICATION_ERROR (-20011, 'samp '||vstep_no||' SQLERRM:'||SQLERRM);

end;

Thanks

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 25 2013
Added on Oct 28 2013
8 comments
598 views