title: Bulk collect in Oracle running slow
body: <p>I am trying to load 90k data from source table with some manipulations in target table .I have created a procedure that uses <code>BULK COLLECT</code> with <code>LIMIT</code> but whenever I  execute that it throws:</p>
  
  <blockquote>
    <p>ORA-02395: exceeded call limit on IO usage<br> 
    ORA-06512: at "INSERT_EXTRACT_TABLE", line 46<br> 
    ORA-06512: at line 1<br> 
    02395. 00000 -  "exceeded call limit on IO usage"<br> 
    *Cause:<br>
    *Action:</p>
  </blockquote>
  
  <p>My code is </p>
  
  <pre><code>create or replace 
  PROCEDURE insert_extract_table(
      v_array_size IN PLS_INTEGER DEFAULT 100)
  IS
    i pls_integer;
  type array
  IS
    TABLE OF MISCDH_MIG_PARTY_LIST%ROWTYPE;
    v_data ARRAY;
    CURSOR v_cur
    IS
      SELECT  p1 AS ORIG_PARTY_ID,
        p2  AS ORIG_PARTY_NUMBER,
        p3    AS ORIG_PARTY_NAME,
        l_id,
        l_rel_code ,
        s_id,
        s_rel_code,
        CASE
          WHEN l_rel_code='PARTENT'
          THEN P1
          WHEN l_rel_code='CHILD'
          THEN
            (SELECT p1
            FROM Mapping B
            WHERE B.s_rel_code='PARENT'
            AND B.s_id        =a.s_id
            )
      END MIG_PARTY_ID,
      CASE
        WHEN l_rel_code  ='PARENT'
        OR s_rel_code='PARENT'
        THEN 'A'
        WHEN l_rel_code     ='CHILD'
        AND s_rel_code IS NULL
        THEN 'M'
      END MIG_STATUS,
      NULL                  AS MIG_STATUS_DESC,
      'MA' AS CREATED_BY,
      sysdate               AS CREATION_DATE,
      'MA' AS LAST_UPDATED_BY,
      sysdate               AS LAST_UPDATE_DATE
    FROM Mapping a;
  BEGIN
    OPEN v_cur;
    LOOP
      FETCH V_CUR bulk collect into V_DATA limit V_ARRAY_SIZE;
      FORALL i IN v_data.first ..v_data.last SAVE EXCEPTIONS
      insert into MISCDH_MIG_PARTY_LIST values V_DATA(I);
      commit;
      EXIT WHEN v_cur%NOTFOUND;
    END LOOP;
    close V_CUR;
  END insert_extract_table;
  </code></pre>
  
  <p>I am using Oracle 11g .Can anyone provide me tips how can I tune this code?</p>