Hi Tom,

Following loop is my biggest problem in live system. Following loop will run every day to insert more than 6 million records in OLTP system and taking 20 hours. Memory wise we don't have any issues.

6TB of memory
195 CPU cores
2.5 GHz cpu speed
Intel Xeon E5-4640 CPU Type


Following is the code: Can you please help me how to speed up the following loop?

BEGIN
OPEN rollup_csr;
LOOP
FETCH rollup_csr BULK COLLECT INTO v_csr_tab LIMIT 100;
EXIT WHEN v_csr_tab.COUNT = 0;
BEGIN

FORALL i in v_csr_tab.FIRST..v_csr_tab.count save exceptions
INSERT INTO blahblah
(create_date, test1, test2, test3, test4,
test5, test6, test7,
test8, test9, test10, test11, test12)
VALUES
(v_run_date,
v_csr_tab(i).test1, v_csr_tab(i).test2,
v_csr_tab(i).test3, v_csr_tab(i).test4,
v_csr_tab(i).test5, v_csr_tab(i).test6,
v_csr_tab(i).test7, v_csr_tab(i).test8, v_csr_tab(i).test9,
v_csr_tab(i).test10 / v_cnt, v_csr_tab(i).test11 / v_cnt, (((v_csr_tab(i).test11 / v_cnt) / (v_csr_tab(i).test10 / v_cnt)) * 100)
);

EXCEPTION

WHEN dml_errors THEN
null;
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 250);
INSERT INTO error_table
(err_num, err_msg, module, create_dtm)
VALUES
(v_err_code, v_err_msg, 'Inserting - blahblah', sysdate);

END;
COMMIT;
END LOOP;

CLOSE rollup_csr;
END;
END;
/

Greatly appreciate.

Mike

SQL> create table t as select s.* from dba_source s,
  2   ( select 1 from dual connect by level <= 10 );

Table created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
   5058450

1 row selected.

SQL>
SQL> create table t1 as select * from t where 1=0;

Table created.

SQL>
SQL> set timing on
SQL> declare
  2    cursor rollup_csr is select * from t;
  3    type row_list is table of t%rowtype index by pls_integer;
  4    v_csr_tab row_list;
  5  BEGIN
  6  OPEN rollup_csr;
  7  LOOP
  8    FETCH rollup_csr BULK COLLECT INTO v_csr_tab LIMIT 100;
  9    EXIT WHEN v_csr_tab.COUNT = 0;
 10    BEGIN
 11
 12      FORALL i in v_csr_tab.FIRST..v_csr_tab.count save exceptions
 13        INSERT INTO t1 values v_csr_tab(i);
 14    END;
 15  COMMIT;
 16  END LOOP;
 17
 18  CLOSE rollup_csr;
 19  END;
 20  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.33