This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jan 22, 2013 5:43 AM by user9093343 RSS

Bulk Upload - Slower for processing the Last few records

user9093343 Newbie
Currently Being Moderated
In most of the circumstances., I feel that the bulk collect script processes good enough but slows down after a given set of records are processed.
I was trying to load 14 million records through FORALL insert - it loads 10 Million records in 10 mins but the process slows down there on and for the remaining 4 million records., it takes more than 2 hours.

I have tried different set of Limits (100,500,1000,10000) and but still experience the same problem.

Could you please help me understand the actual problem?
  • 1. Re: Bulk Upload - Slower for processing the Last few records
    Bawer Journeyer
    Currently Being Moderated
    It looks like an undo space/memory problem.

    can you provide a small example based on your code?
  • 2. Re: Bulk Upload - Slower for processing the Last few records
    Karthick_Arp Guru
    Currently Being Moderated
    user9093343 wrote:
    In most of the circumstances., I feel that the bulk collect script processes good enough but slows down after a given set of records are processed.
    I was trying to load 14 million records through FORALL insert - it loads 10 Million records in 10 mins but the process slows down there on and for the remaining 4 million records., it takes more than 2 hours.

    I have tried different set of Limits (100,500,1000,10000) and but still experience the same problem.

    Could you please help me understand the actual problem?
    The primary question you should be asking is, why am i using BULK COLLECT (PL/SQL) and not just doing it in direct SQL?
  • 3. Re: Bulk Upload - Slower for processing the Last few records
    user9093343 Newbie
    Currently Being Moderated
    Since the record count is in millions for this dml operation., we thought of using bulk collect instead of direct SQL. We have very limited tablespace allocated and so., we usually end up with tablespace problems.
  • 4. Re: Bulk Upload - Slower for processing the Last few records
    user9093343 Newbie
    Currently Being Moderated
    Please find my sample code below. The funct_1 and func_2 will return comma separated values of few columns grouped by col3 and col4.
    Please provide your suggstions

    DECLARE
    CURSOR cur_test
    IS
    SELECT funct_1(col1) as col1, funct_2(col_2) as col2, col3, col4 from table1;

    TYPE v_number IS TABLE OF NUMBER
    INDEX BY PLS_INTEGER;

    TYPE v_varchar IS TABLE OF VARCHAR2 (2000)
    INDEX BY PLS_INTEGER;

    v_col1 v_number;
    v_col2 v_number;
    v_col3 v_varchar;
    v_col4 v_varchar;
    BEGIN
    DBMS_OUTPUT.put_line ( 'Started at: '
    || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
    );

    OPEN cur_test;

    LOOP
    FETCH cur_test
    BULK COLLECT INTO v_col1, v_col2, v_col3, v_col4
    LIMIT 100;

    EXIT WHEN v_col1.COUNT = 0;
    FORALL i IN 1 .. v_col1.COUNT
    INSERT INTO TABLE2
    (col1, col2, col3, col4
    )
    VALUES (v_col1 (i), v_col2 (i), v_col3 (i), v_col4 (i),
    SYSDATE
    );
    COMMIT;
    END LOOP;

    CLOSE cur_test;

    DBMS_OUTPUT.put_line ( 'Ended at: '
    || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
    );

    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line ( '['
    || TO_CHAR (SYSDATE,
    'mm/dd/yyyy hh24:mi:ss' || ']:'
    )
    || ' - [Loading TABLE2 table] - ABORTED.'
    || SQLERRM);

    ROLLBACK;
    END;
    /

    SHOW ERRORS
  • 5. Re: Bulk Upload - Slower for processing the Last few records
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    So how does bulk processing address the issue, when all it does is to add PL/SQL in between the source and target tables?

    Instead of this - reading data using the SQL engine and writing the data read using the SQL engine:
    INSERT INTO target SELECT ... FROM destination

    Your approach now adds PL/SQL between the read and write processing, and uses very expensive server memory (PGA) to read data into. Only to write that very same data from PL/SQL PGA memory back to the SQL engine.


    How is sending the SQL data via a detour from source to target table, better?
  • 6. Re: Bulk Upload - Slower for processing the Last few records
    Karthick_Arp Guru
    Currently Being Moderated
    user9093343 wrote:
    Since the record count is in millions for this dml operation., we thought of using bulk collect instead of direct SQL. We have very limited tablespace allocated and so., we usually end up with tablespace problems.
    You mean the UNDO. I have heard that before. And you will be issuing COMMIT in the loop, correct? Its as bad as it gets, I hope you have a very good restart mechanism in place incase the process fails in the middle otherwise you will be left with messed up data. Oh and yes forgot to mention about [url http://docs.oracle.com/cd/B19306_01/server.102/b14219/e1500.htm#sthref833]ORA-1555
  • 7. Re: Bulk Upload - Slower for processing the Last few records
    Bawer Journeyer
    Currently Being Moderated
    sometimes, the code says much more than words.

    as suggested, use insert into ... select ...
  • 8. Re: Bulk Upload - Slower for processing the Last few records
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user9093343 wrote:
    Please find my sample code below.
    Not very efficient code - and written in ugly uppercase too.

    The code is dangerous. Incremental commits can corrupt the integrity of your data. There are NO valid reasons for using incremental commits.

    As the basic process is reading a lot of data, and then writing a lot of data, trying to muck around with bulk processing is silly. The performance overhead is I/O. Lots of I/O is done.

    Bulk processing does not make a physical disk read, or logical buffer cache read, faster. It reduces context switching. And context switching is not what is killing performance. Doing tons of I/O is.

    Consider parallelising the I/O processing using DBMS_PARALLEL_EXECUTE as shown in sample code in {message:id=10571826}.
  • 9. Re: Bulk Upload - Slower for processing the Last few records
    Karthick_Arp Guru
    Currently Being Moderated
    Sorry to say your code does it all wrong.
    DECLARE
    CURSOR cur_test
    IS
    SELECT  funct_1(col1) as col1, funct_2(col_2) as col2, col3, col4 from table1;
    
    TYPE v_number IS TABLE OF NUMBER
    INDEX BY PLS_INTEGER;
    
    TYPE v_varchar IS TABLE OF VARCHAR2 (2000)
    INDEX BY PLS_INTEGER;
    
    v_col1       v_number;
    v_col2       v_number;
    v_col3       v_varchar;
    v_col4       v_varchar;
    BEGIN
    DBMS_OUTPUT.put_line (   'Started at: '
    || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
    );
    
    OPEN cur_test;
    
    LOOP
    FETCH cur_test
    BULK COLLECT INTO v_col1, v_col2, v_col3, v_col4
    LIMIT 100;
    
    EXIT WHEN v_col1.COUNT = 0;
    FORALL i IN 1 .. v_col1.COUNT
    INSERT INTO TABLE2
    (col1, col2, col3, col4
    )
    VALUES (v_col1 (i), v_col2 (i), v_col3 (i), v_col4 (i),
    SYSDATE
    );
    COMMIT;
    END LOOP;
    
    CLOSE cur_test;
    
    DBMS_OUTPUT.put_line (   'Ended at: '
    || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss')
    );
    
    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line (   '['
    || TO_CHAR (SYSDATE,
    'mm/dd/yyyy hh24:mi:ss' || ']:'
    )
    || ' - [Loading TABLE2 table] - ABORTED.'
    || SQLERRM);
    
    ROLLBACK;
    END;
    /
    The worst part is the exception handling.

    Just do it like this.
    begin
       dbms_output.put_line ('started at: '|| to_char (sysdate, 'mm/dd/yyyy hh24:mi:ss'));
       
       insert into table2
       (
          col1, 
          col2, 
          col3, 
          col4
       )
       select funct_1(col1) as col1, 
              funct_2(col_2) as col2, 
           col3, 
           col4 
         from table1;
       
       commit;
       
       dbms_output.put_line ('ended at: '|| to_char (sysdate, 'mm/dd/yyyy hh24:mi:ss'));
    end;
    /
    If you have UNDO space issue work with your DBA and get what you want. Additional suggestion try to remove the functino funct_1 and funct_2 and incorporate the logic in the SELECT directly.
  • 10. Re: Bulk Upload - Slower for processing the Last few records
    Nicosa Expert
    Currently Being Moderated
    Billy  Verreynne  wrote:
    How is sending the SQL data via a detour from source to target table, better?
    Easy to explain : We all know database is evil.
    Its only purpose is to have all human/application struggle to death anytime they want to read/write data.
    Asking the database to copy from one table to another is equivalent of asking the devil itself to babysit your children hoping nothing wrong would happen.
    Hence, the best path is to limit the interactions with database to "give me this" and "take that".

    Operating Systems also are evil, that is why when I need to move a file to a different directory, I prefer to print it down, then scan it and do some OCR then write it to the new location on disk.
    +(copy'n'paste and/or mv commands are just not trustworthy)+

    :-) :-) :-) :-)
  • 11. Re: Bulk Upload - Slower for processing the Last few records
    jeneesh Guru
    Currently Being Moderated
    Nicosa wrote:
    that is why when I need to move a file to a different directory, I prefer to print it down, then scan it and do some OCR then write it to the new location on disk.
    +(copy'n'paste and/or mv commands are just not trustworthy)+
    Well said..
  • 12. Re: Bulk Upload - Slower for processing the Last few records
    user9093343 Newbie
    Currently Being Moderated
    DIRECT SQL(insert into... select * from...) takes more than 2 hours and it keeps on running. most of the times I land into temp space issue also.
    But the way I have written here processes almost 10 millions in 10 seconds. I am worried about the remaining 1.4 millions only.

    Could you please provide other suggestions?

    I have directly used the sql instead of using funct also. thats also not helping.
  • 13. Re: Bulk Upload - Slower for processing the Last few records
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user9093343 wrote:
    DIRECT SQL(insert into... select * from...) takes more than 2 hours and it keeps on running. most of the times I land into temp space issue also.
    But the way I have written here processes almost 10 millions in 10 seconds. I am worried about the remaining 1.4 millions only.
    No. I think you are confused by being worried about an irrelevance.

    The entire process from start-to-end is relevant.

    And the optimal approach is:
    a) do a INSERT..SELECT
    b) do a INSERT..SELECT using DBMS_PARALLEL_EXECUTE to address incremental inserts/commits requirement

    Bulk process is slow. And will always be slower than an INSERT..SELECT.
  • 14. Re: Bulk Upload - Slower for processing the Last few records
    Dave Rabone Journeyer
    Currently Being Moderated
    You don't need to worry too much about undo for inserts ... Think about it - you don't have to preserve any data, just the fact that it was inserted somewhere, ie the rowid. 15 million rowids (plus a bit of overhead) is nothing.
1 2 Previous Next

Legend

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