This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Dec 28, 2012 9:17 AM by rp0428 RSS

bulk collect usses with Limit clause

siebelD Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    my suggestion use the bind variables for insert script... it will be fast
  • 2. Re: bulk collect usses with Limit clause
    Purvesh K Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    How we can use here bind variable ..Please little me more eloborate on it ...
  • 4. Re: bulk collect usses with Limit clause
    971895 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points