13 Replies Latest reply on Dec 4, 2012 10:40 PM by rp0428

    Bulk INSERT performance issues with collections

    977909
      I am developing stored procedure for Oracle 10g and I am hitting pretty heavy performance bottle neck while trying to pass list of about 2-3k items into procedure. Here's my code:

      ----------------------------------------------------------

      TYPE ty_id_list
      AS TABLE OF NUMBER(11);

      ----------------------------------------------------------


      PROCEDURE sp_performance_test (
      p_idsCollection IN schema.ty_id_list )
      AS

      TYPE type_numeric_table IS TABLE OF NUMBER(11) INDEX BY BINARY_INTEGER;
      l_ids type_numeric_table;
      data type_numeric_table;
      empty type_numeric_table;

      BEGIN

      EXECUTE IMMEDIATE
      'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
      COMMIT;

      SELECT COLUMN_VALUE BULK COLLECT INTO l_ids
      FROM TABLE(p_idsCollection);

      FOR j IN 1 .. l_ids.COUNT LOOP
      data(data.count+1) := l_ids(j);

      IF(MOD(data.COUNT,500) = 0 ) THEN
      FORALL i IN 1 .. data.COUNT
      INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
      VALUES (data(i), 'Y');
      data := empty;
      END IF;
      END LOOP;
      IF(data.count IS NOT NULL) THEN
      FORALL i IN 1 .. data.COUNT
      INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
      VALUES (data(i), 'Y');
      END IF;
      COMMIT;

      EXECUTE IMMEDIATE
      'ALTER TABLE schema.T_TEST_TABLE LOGGING';
      COMMIT;

      END sp_performance_test;

      ----------------------------------------------------------

      So the thing that adds up to the process quite drastically seems to be this part: data(data.count+1) := l_ids(j); If I skip getting element from the collection and change this line to data(data.count+1) := j;, procedure execution time will be 3-4 times faster (from over 30s to 8-9s for 3k items) - but this logic obviously is not the one i want.

      Can You guys give me a hint where could I improve my code to get better performance on inserting data? If any improvements can be done really.

      Thanks,
      Przemek
        • 1. Re: Bulk INSERT performance issues with collections
          6363
          I think this code should just be
          insert into schema.t_test_table (ref_id, active)
          select column_value, 'Y' from table(p_idscollection);
          1 person found this helpful
          • 2. Re: Bulk INSERT performance issues with collections
            spajdy
            1/ table schema.T_TEST_TABLE is thera any index or trigger ?
            2/ why you copy IN collection to new collection ?
            You should use it directly
            PROCEDURE sp_performance_test (
            p_idsCollection IN schema.ty_id_list )
            AS
            BEGIN
            
            EXECUTE IMMEDIATE
            'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
            COMMIT;
            FORALL i IN 1 .. p_idsCollection.COUNT
            INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
            VALUES (p_idsCollection(i), 'Y'); 
            COMMIT;
            
            EXECUTE IMMEDIATE
            'ALTER TABLE schema.T_TEST_TABLE LOGGING'; 
            COMMIT;
            
            END sp_performance_test;
            3/ it is this procedure called from other PL/SQL code or form other language (JAVA) ?
            If it is called form PL/SQL there should be a simple way - puge SQL
            INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
            SELECT ...
            FROM ...
            WHERE ..

            4/ if you must use your procedure and want to insert data in chunks try following
            PROCEDURE sp_performance_test(p_idscollection IN schema.ty_id_list) AS
              v_count PLS_INTEGER;
              v_low PLS_INTEGER;
              v_hi PLS_INTEGER;
              v_step_size PLS_INTEGER := 500;
            BEGIN
            
                EXECUTE IMMEDIATE 'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
                COMMIT;
                v_count := p_idscollection.count;
                v_low := 1;
                LOOP
                  v_hi := least(v_low + v_step_size -1,v_count);
                  FORALL i IN v_low .. v_hi
                    INSERT INTO schema.t_test_table(ref_id, active)
                    VALUES(p_idscollection(i),'Y');
                  COMMIT;
                  v_low := v_hi + 1;
                  EXIT WHEN v_low > v_count;
                END LOOP;    
            
                EXECUTE IMMEDIATE 'ALTER TABLE schema.T_TEST_TABLE LOGGING';
                COMMIT;
            
            END sp_performance_test;
            If performace problems still persist then trace you session and look where time is spent.
            1 person found this helpful
            • 3. Re: Bulk INSERT performance issues with collections
              Purvesh K
              No Database at hand but, I guess your code should be this way
               PROCEDURE sp_performance_test (
                 p_idsCollection IN schema.ty_id_list )
               AS
               
               TYPE type_numeric_table IS TABLE OF NUMBER(11) INDEX BY BINARY_INTEGER;
               l_ids type_numeric_table;
              -- data type_numeric_table;                    --------------> Not Required
              -- empty type_numeric_table;                 --------------> Not Required
               
               BEGIN
               
               EXECUTE IMMEDIATE
                 'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
              -- COMMIT;              --> No Commit required
              
               LOOP
                SELECT COLUMN_VALUE BULK COLLECT INTO l_ids Limit 100          ---> Use Limit to avoid Overloading PGA.
                FROM TABLE(p_idsCollection);
              
                FORALL i IN 1 .. l_ids.COUNT
                  INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
                  VALUES (l_ids(i), 'Y');
              
                exit when l_ids.count = 0;
               END LOOP; 
              
               COMMIT;
               
               EXECUTE IMMEDIATE
                 'ALTER TABLE schema.T_TEST_TABLE LOGGING'; 
               --COMMIT;              --------------> Not Required
               
               END sp_performance_test;
              I would suggest not performing DDL's in a Package execution.
              • 4. Re: Bulk INSERT performance issues with collections
                Suman Rana
                Hi Purvesh,

                I have some concerns abt your code..

                PROCEDURE sp_performance_test (
                p_idsCollection IN schema.ty_id_list )
                AS

                TYPE type_numeric_table IS TABLE OF NUMBER(11) INDEX BY BINARY_INTEGER;
                l_ids type_numeric_table;
                -- data type_numeric_table; --------------> Not Required
                -- empty type_numeric_table; --------------> Not Required

                CURSOR C1 is SELECT COLUMN_VALUE aa FROM TABLE(p_idsCollection);


                BEGIN

                EXECUTE IMMEDIATE
                'ALTER TABLE schema.T_TEST_TABLE NOLOGGING';
                -- COMMIT; --> No Commit required

                LOOP
                FETCH C1 BULK COLLECT INTO l_ids LIMIT 100;

                -- SELECT COLUMN_VALUE BULK COLLECT INTO l_ids Limit 100 ---> Use Limit to avoid Overloading PGA.
                FROM TABLE(p_idsCollection);                                                     -> We can not use LIMIT caluse along with SELECT Query, we can use this with explicit CURSOR


                FORALL i IN 1 .. l_ids.COUNT
                INSERT INTO schema.T_TEST_TABLE (REF_ID, ACTIVE)
                VALUES (l_ids(i), 'Y');

                -- exit when l_ids.count = 0;
                EXIT WHEN C1%NOTFOUND; ----> We can not use this clause before FORALL
                END LOOP;

                COMMIT;

                EXECUTE IMMEDIATE
                'ALTER TABLE schema.T_TEST_TABLE LOGGING';
                COMMIT;              ------------> Not Required

                END sp_performance_test;
                • 5. Re: Bulk INSERT performance issues with collections
                  Purvesh K
                  Yes. You are correct. Apology as it slipped from my mind.

                  I, in fact, agree with solution by 3360. Its concise and simple.
                  • 6. Re: Bulk INSERT performance issues with collections
                    Suman Rana
                    Yes solution provided by 3360 is very simple..but here issue is if u want to Insert a huge amount record into table..this query could create memory issue.. but using BULK COLLECT with LIMIT clause would not create memory consumption issue since here we are inserting record on batch chunk basis..
                    • 7. Re: Bulk INSERT performance issues with collections
                      padders
                      If p_idsCollection is large then you have a memory issue however you look at it.
                      • 8. Re: Bulk INSERT performance issues with collections
                        6363
                        Suman Rana wrote:
                        Yes solution provided by 3360 is very simple..
                        And will be the fastest method.
                        but here issue is if u want to Insert a huge amount record into table..this query could create memory issue..
                        What specific memory issue and what error are you referring to?
                        but using BULK COLLECT with LIMIT clause would not create memory consumption issue
                        Same question on memory issue? Also you do know that bulk collect fetches the results into memory and so uses more memory than the code I posted, which is why you need limit clause.

                        http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

                        >
                        The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory."
                        Specifically, memory for collections is stored in the program global area (PGA)
                        >
                        since here we are inserting record on batch chunk basis..
                        And using more memory and doing it slower as a result.
                        • 9. Re: Bulk INSERT performance issues with collections
                          John Spencer
                          While I agree with 3360 that a simple insert into select is the best approach based on what you have posted, I have to ask, how is the parameter p_idsCollection beng populated? Is there some user sitting in front of a computer somewhere typing in 2 - 3 K items to populate the list, or is the list getting populated through a database query somewhere?

                          If it is the latter, then the whole procedure may be redundant, and you could just do it with a simple insert select form wahtever query populates the list, which would be even faster than 3360's solution.

                          John
                          • 10. Re: Bulk INSERT performance issues with collections
                            Nikolay Savvinov
                            Hi
                            3360 wrote:
                            Suman Rana wrote:
                            Yes solution provided by 3360 is very simple..
                            ...
                            What specific memory issue and what error are you referring to?
                            my guess is he meant UNDO issue. it's possible, but rather unlikely given that the op is only passing a couple of thousand records to the procedure (unless each is megabytes big).

                            Best regards,
                            Nikolay
                            • 11. Re: Bulk INSERT performance issues with collections
                              6363
                              Nikolay Savvinov wrote:
                              Hi
                              3360 wrote:
                              Suman Rana wrote:
                              Yes solution provided by 3360 is very simple..
                              ...
                              What specific memory issue and what error are you referring to?
                              my guess is he meant UNDO issue. it's possible, but rather unlikely given that the op is only passing a couple of thousand records to the procedure (unless each is megabytes big).
                              Inserts generate the smallest amount of UNDO and it is not related in any way to the size of the row since all it contains is ROWIDs.

                              Regardless of what he meant, what he wrote was completely wrong.
                              • 12. Re: Bulk INSERT performance issues with collections
                                977909
                                Thanks for all tips guys and to answer some of Your questions:

                                1/ This procedure is being called from Java code so essentially procedure would be desired way to go.

                                2/ I initially tried INSERT-SELECT in the way similar to (don't have access to db so sorry for the syntax errors)
                                insert into schema.T_TEST_TABLE select column_value from table(p_idsCollection);
                                but that seemed to be slightly slower than my code from above for about 3k records.

                                Does it make sense to split this 3k into smaller chunks (500?) and perform INSERT-SELECT on these? Just a guess.

                                3/ Idea of re-writing collection into l_ids was to take advantage of type_numeric_table type index, i am not entirely sure if this does make sense either, it was just a blind try pretty much. I am looking for Your opinion guys if it's even worth to do it.

                                4/ @spajdy, I tried Your way of splitting and it gained me like 8s immediately so great thanks for that tip!

                                5/ Yes destination table does have 2 or 3 indexes and a trigger that takes ID value from the sequence

                                Thanks for all Your help and I'm looking forward more ideas guys!
                                • 13. Re: Bulk INSERT performance issues with collections
                                  rp0428
                                  Welcome to the forum!

                                  Whenever you post be sure to provide your 4 digit Oracle version.
                                  >
                                  1/ This procedure is being called from Java code so essentially procedure would be desired way to go.

                                  2/ I initially tried INSERT-SELECT in the way similar to (don't have access to db so sorry for the syntax errors)
                                  insert into schema.T_TEST_TABLE select column_value from table(p_idsCollection);
                                  but that seemed to be slightly slower than my code from above for about 3k records.

                                  Does it make sense to split this 3k into smaller chunks (500?) and perform INSERT-SELECT on these?
                                  >
                                  No - that doesn't make sense.

                                  What makes sense is to just use Java to do the INSERTS directly using Oracle's batch processing. You are spending more time monkeying around about functions and collections and everything you could have had the Java code written and executed by now. And it would have been faster and more scalable.

                                  K.I.S.S. - simpler is better. Not sure how you got into trying to use collections and stored procedures when all you need is simple Java.

                                  See the 'Setting the Statement Batch Value' section in the JDBC Developer's Guide. It has example code as well.
                                  http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraperf.htm#sthref1162

                                  You could use just use
                                  1. A FOR loop that provides your 3k different values
                                  2. An OraclePreparedStatement to do the insert
                                  3. Set the batch size to 100
                                  4. Have a cup of coffee while it finishes - it will only take a few seconds.

                                  This code isn't tested but you can check the example in the doc.
                                  int [] myInts = new int [3000];
                                  // call a method to populate your array here
                                  conn.setAutoCommit = false;
                                  OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement
                                     ("insert into schema.t_test_table (ref_id, active) values (?, 'Y')");
                                  ps.setExecuteBatch(100);
                                  
                                  for (int i = 0; i < myInts.length(); i++) {
                                     ps.setInt (1, myInts);
                                  int rows = ps.executeUpate();
                                  }
                                  conn.commit;
                                  ps.close()