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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Processing
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
588 views