1 2 Previous Next 26 Replies Latest reply: Jan 22, 2013 7:43 AM by user9093343 RSS

    Bulk Upload - Slower for processing the Last few records

    user9093343
      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
          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_
            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
              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
                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
                  Billy~Verreynne
                  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_
                    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
                      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
                        Billy~Verreynne
                        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_
                          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-Oracle
                            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
                              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
                                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
                                  Billy~Verreynne
                                  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
                                    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