1 2 Previous Next 19 Replies Latest reply: Dec 28, 2012 11:17 AM by rp0428 RSS

    bulk collect usses with Limit clause

    siebelD
      I have to process 10 Lak records in on day and for other day run records may be within 50000 range.

      I am going use bulk collect with limit clause for this scenarioa...now question is what should be value for limit .

      for 10 lak records ..if I will put 1000 records of limit ...its becoming very slow .
      I tried with 1000 records limit for approx 1 lakh records ...procedure took 22 mins time in processing ..
      below is my code ....Please let me know what value I should put for limit clause.

      PROCEDURE insert_eim_src is

      TYPE t_tab_promo IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;

      v_seq t_tab_promo;
      v_batch_num t_tab_promo;
      v_name t_tab_promo;
      v_sku_src_num t_tab_promo;
      v_sub_type t_tab_promo;
      v_sku_actual_pos t_tab_promo;

      CURSOR cur_new_promo
      IS
      SELECT ROWNUM seq, TRUNC(rownum/5000) + 20000 batch_number , sku_org.NAME name,
      acct_promo_sku.src_num sku_src_num, acct_promo_sku.sub_type sub_type,
      promo_actual.sku_actual_pos sku_actual_pos
      FROM siebel.s_src acct_promo_hdr,
      siebel.s_src acct_title_format,
      siebel.s_src acct_promo_sku,
      siebel.s_src_x acct_promo_hdrx,
      siebel.s_src_x acct_promo_skux,
      siebel.s_prod_int prod,
      siebel.s_bu promo_hdr_org,
      siebel.s_bu sku_org,
      siebelwb.stg_sbl_acct_promo_actuals2 promo_actual
      WHERE acct_promo_hdr.sub_type = 'PLAN_ACCOUNT_PROMOTION'
      AND acct_promo_hdr.row_id = acct_title_format.par_src_id
      AND acct_title_format.sub_type = 'PLAN_ACCT_PROMOTION_CATEGORY'
      AND acct_title_format.row_id = acct_promo_sku.par_src_id
      AND acct_promo_sku.sub_type = 'PLAN_ACCOUNT_PROMOTION_PRODUCT'
      AND acct_promo_hdr.row_id = acct_promo_hdrx.par_row_id
      AND acct_promo_sku.row_id = acct_promo_skux.par_row_id(+)
      AND acct_promo_sku.prod_id = prod.row_id
      AND acct_promo_hdr.bu_id = promo_hdr_org.row_id
      AND acct_promo_sku.bu_id = sku_org.row_id
      AND prod.x_prod_material_num = promo_actual.material_number
      and prod.X_PROD_SALES_ORG=promo_actual.sales_org
      AND acct_promo_hdr.row_id = promo_actual.acct_promo_id
      and nvl(acct_promo_hdr.pr_accnt_id,0)=nvl(promo_actual.acct_siebel_rowid,0)
      and nvl(acct_promo_hdr.x_indirect_id,0)=nvl(promo_actual.indirect_acct_siebel_rowid,0)
      AND TRUNC (promo_actual.load_date) = TRUNC (SYSDATE);


      BEGIN
      OPEN cur_new_promo;
      LOOP
      FETCH cur_new_promo
      BULK COLLECT INTO v_seq ,
      v_batch_num ,
      v_name ,
      v_sku_src_num ,
      v_sub_type ,
      v_sku_actual_pos LIMIT 1000;

      FORALL r IN 1 .. v_seq.COUNT

      INSERT INTO siebel.eim_src
      (row_id, if_row_batch_num, if_row_stat, org_bu, src_num,
      sub_type, ext_attrib_16)values(v_seq(r),v_batch_num(r),'FOR_UPDATE',v_name(r),v_sku_src_num(r),
      v_sub_type(r),v_sku_actual_pos(r));
      commit;

      EXIT WHEN cur_new_promo%NOTFOUND;
      END LOOP;
      close cur_new_promo;
      END;
        • 1. Re: bulk collect usses with Limit clause
          971895
          my suggestion use the bind variables for insert script... it will be fast
          • 2. Re: bulk collect usses with Limit clause
            Purvesh K
            Please refrain from using Locale terms viz. Lak; Use 100 K instead as it helps people outside South Asian sub-continent to understand easily.
            I have to process 10 Lak records in on day and for other day run records may be within 50000 range.

            I am going use bulk collect with limit clause for this scenarioa...now question is what should be value for limit .

            for 10 lak records ..if I will put 1000 records of limit ...its becoming very slow .
            I tried with 1000 records limit for approx 1 lakh records ...procedure took 22 mins time in processing ..
            below is my code ....Please let me know what value I should put for limit clause.
            It is a common mis-conception that Bulk Collect will perform better than a normal Insert/Update statement.
            And the LIMIT does not have any standard value set, it is, AFAIK, a value to be deduced by a Trial-and-Error method.

            Try the code that you have prepared and test the execution time and resource code against the below:
             PROCEDURE insert_eim_src is
            begin 
            
            INSERT INTO siebel.eim_src
            (row_id, if_row_batch_num, if_row_stat, org_bu, src_num,
                                sub_type, ext_attrib_16)
                      SELECT ROWNUM seq, TRUNC(rownum/5000) + 20000 batch_number , sku_org.NAME name,
                             acct_promo_sku.src_num sku_src_num, acct_promo_sku.sub_type sub_type,
                             promo_actual.sku_actual_pos sku_actual_pos
                        FROM siebel.s_src acct_promo_hdr,
                             siebel.s_src acct_title_format,
                             siebel.s_src acct_promo_sku,
                             siebel.s_src_x acct_promo_hdrx,
                             siebel.s_src_x acct_promo_skux,
                             siebel.s_prod_int prod,
                             siebel.s_bu promo_hdr_org,
                             siebel.s_bu sku_org,
                             siebelwb.stg_sbl_acct_promo_actuals2 promo_actual
                       WHERE acct_promo_hdr.sub_type = 'PLAN_ACCOUNT_PROMOTION'
                         AND acct_promo_hdr.row_id = acct_title_format.par_src_id
                         AND acct_title_format.sub_type = 'PLAN_ACCT_PROMOTION_CATEGORY'
                         AND acct_title_format.row_id = acct_promo_sku.par_src_id
                         AND acct_promo_sku.sub_type = 'PLAN_ACCOUNT_PROMOTION_PRODUCT'
                         AND acct_promo_hdr.row_id = acct_promo_hdrx.par_row_id
                         AND acct_promo_sku.row_id = acct_promo_skux.par_row_id(+)
                         AND acct_promo_sku.prod_id = prod.row_id
                         AND acct_promo_hdr.bu_id = promo_hdr_org.row_id
                         AND acct_promo_sku.bu_id = sku_org.row_id
                         AND prod.x_prod_material_num = promo_actual.material_number
                         and prod.X_PROD_SALES_ORG=promo_actual.sales_org
                         AND acct_promo_hdr.row_id = promo_actual.acct_promo_id
                         and nvl(acct_promo_hdr.pr_accnt_id,0)=nvl(promo_actual.acct_siebel_rowid,0)
                         and nvl(acct_promo_hdr.x_indirect_id,0)=nvl(promo_actual.indirect_acct_siebel_rowid,0)
                         AND TRUNC (promo_actual.load_date) = TRUNC (SYSDATE);
                   commit;
                 END;
            • 3. Re: bulk collect usses with Limit clause
              siebelD
              How we can use here bind variable ..Please little me more eloborate on it ...
              • 4. Re: bulk collect usses with Limit clause
                971895
                as per previoues comment......please use the append hint as like below....

                {code } Insert/*+ APPEND */ into tablename select * from tablename
                                                                                                                                                                                                                                                                                                                                
                • 5. Re: bulk collect usses with Limit clause
                  Purvesh K
                  pujakhetan wrote:
                  How we can use here bind variable ..Please little me more eloborate on it ...
                  When using PL/SQL, each variable is a Bind Variable. You need not perform any extra work on it.

                  Read comments from Tom Kyte on Bind Variable.
                  • 6. Re: bulk collect usses with Limit clause
                    siebelD
                    Pravesh

                    Bind variable concept is clear but here on my case how differently I can use bind variable ..by using bulk collect I am already fetching data in variable only .

                    I already tried my code with simple insert and select which you said here ..but query was runing more that 2+ hours for 8 lakh records then I had cancelled the job and tried bulk collect approch ...

                    now just now I ran your query for 1 lak records ..will update you timing when it finishes....

                    if i go by bulk collect ..is there any issue ?
                    • 7. Re: bulk collect usses with Limit clause
                      BluShadow
                      pujakhetan wrote:
                      Pravesh

                      Bind variable concept is clear but here on my case how differently I can use bind variable ..by using bulk collect I am already fetching data in variable only .

                      I already tried my code with simple insert and select which you said here ..but query was runing more that 2+ hours for 8 lakh records then I had cancelled the job and tried bulk collect approch ...

                      now just now I ran your query for 1 lak records ..will update you timing when it finishes....

                      if i go by bulk collect ..is there any issue ?
                      BULK COLLECT and inserting in 'batches' will NEVER... I repeat... NEVER... be faster than just using a straight INSERT ... SELECT ... statement.

                      You also have a commit you are issuing inside your cursor loop. Not only can that introduce a snapshot too old error, but multiple commits will also slow down your process.

                      Purvesh is correct, the fastest way to insert all your data will simply be...
                      PROCEDURE insert_eim_src is
                      BEGIN
                        INSERT INTO siebel.eim_src
                            (row_id
                            ,if_row_batch_num
                            ,if_row_stat
                            ,org_bu
                            ,src_num
                            ,sub_type
                            ,ext_attrib_16)
                        SELECT ROWNUM seq
                              ,TRUNC(rownum/5000) + 20000 batch_number
                              ,'FOR_UPDATE'
                              ,sku_org.NAME name
                              ,acct_promo_sku.src_num sku_src_num
                              ,acct_promo_sku.sub_type sub_type
                              ,promo_actual.sku_actual_pos sku_actual_pos
                        FROM   siebel.s_src acct_promo_hdr
                              ,siebel.s_src acct_title_format
                              ,siebel.s_src acct_promo_sku
                              ,siebel.s_src_x acct_promo_hdrx
                              ,siebel.s_src_x acct_promo_skux
                              ,siebel.s_prod_int prod
                              ,siebel.s_bu promo_hdr_org
                              ,siebel.s_bu sku_org
                              ,siebelwb.stg_sbl_acct_promo_actuals2 promo_actual
                        WHERE  acct_promo_hdr.sub_type = 'PLAN_ACCOUNT_PROMOTION'
                        AND    acct_promo_hdr.row_id = acct_title_format.par_src_id
                        AND    acct_title_format.sub_type = 'PLAN_ACCT_PROMOTION_CATEGORY'
                        AND    acct_title_format.row_id = acct_promo_sku.par_src_id
                        AND    acct_promo_sku.sub_type = 'PLAN_ACCOUNT_PROMOTION_PRODUCT'
                        AND    acct_promo_hdr.row_id = acct_promo_hdrx.par_row_id
                        AND    acct_promo_sku.row_id = acct_promo_skux.par_row_id(+)
                        AND    acct_promo_sku.prod_id = prod.row_id
                        AND    acct_promo_hdr.bu_id = promo_hdr_org.row_id
                        AND    acct_promo_sku.bu_id = sku_org.row_id
                        AND    prod.x_prod_material_num = promo_actual.material_number
                        and    prod.X_PROD_SALES_ORG=promo_actual.sales_org
                        AND    acct_promo_hdr.row_id = promo_actual.acct_promo_id
                        and    nvl(acct_promo_hdr.pr_accnt_id,0)=nvl(promo_actual.acct_siebel_rowid,0)
                        and    nvl(acct_promo_hdr.x_indirect_id,0)=nvl(promo_actual.indirect_acct_siebel_rowid,0)
                        AND    TRUNC (promo_actual.load_date) = TRUNC (SYSDATE);
                        commit;
                      END;
                      Perhaps making it a direct path insert will improve performance a little (depending on whether you already have data in the table or not etc.)..
                        INSERT /*+ APPEND */ INTO siebel.eim_src
                      If that is taking time to insert all your records then it's not because of slowness of the inserting itself, but because of the time it's taking to query the data, which would indicate you need to look at the performance issues of the query itself.

                      {message:id=9360003}
                      • 8. Re: bulk collect usses with Limit clause
                        siebelD
                        I will try by using append hint and will update you if it became faster or not ..thanks for your response
                        • 9. Re: bulk collect usses with Limit clause
                          Purvesh K
                          pujakhetan wrote:
                          Pravesh

                          Bind variable concept is clear but here on my case how differently I can use bind variable ..by using bulk collect I am already fetching data in variable only .

                          I already tried my code with simple insert and select which you said here ..but query was runing more that 2+ hours for 8 lakh records then I had cancelled the job and tried bulk collect approch ...

                          now just now I ran your query for 1 lak records ..will update you timing when it finishes....
                          Then I feel the main culprit should be the Select query you are using to fetch the data.

                          You can try to just execute the SELECT query and note the timing. Additionally, If select is taking majority of time, you may consider posting a new thread for a Tuning request of the Select statement {message:id=3292438}.
                          • 10. Re: bulk collect usses with Limit clause
                            siebelD
                            I have not tried yet using append hint ..but query that pravesh mentioned ..means insert with select caluse is running very slaw...

                            however indivisually when I tried select query only for 9 lakh records ..It showed result in second only ..that means query permance is good ..
                            problem is during insertion in this table ...

                            Please correct me if I am missing anything ..

                            On this EIM_SRC table ..approx 1 lakh records already exists ,indexes are also exists and last statistics ran yesterday
                            • 11. Re: bulk collect usses with Limit clause
                              Suman Rana
                              I think here issue is with the select query used in the CURSOR and maximum time is taking due to extract data from db instead of using FORALL. So try to optimize your SQL Query at first.. if not possible try to use PARALLEL_ENABLE pipeline function approach to improve the performance.

                              [Here is the link|https://forums.oracle.com/forums/thread.jspa?threadID=299056]
                              • 12. Re: bulk collect usses with Limit clause
                                siebelD
                                all
                                indivisually when I am running select query only ..its giving result in seconds only ....so query performace is good
                                • 13. Re: bulk collect usses with Limit clause
                                  padders
                                  however indivisually when I tried select query only for 9 lakh records ..It showed result in second only ..that means query permance is good ..
                                  Your query returned 9 x 100,000 records in one second? I rather doubt that.
                                  • 14. Re: bulk collect usses with Limit clause
                                    Purvesh K
                                    pujakhetan wrote:
                                    all
                                    indivisually when I am running select query only ..its giving result in seconds only ....so query performace is good
                                    Or Maybe, it is retrieving first few, say 10 or 50, records in those few seconds.
                                    1 2 Previous Next