SQL & PL/SQL

I have a procedure to load data from one source table into eight different destination tables. The 8 tables have some of the columns of the source table with a common key.

I have run into a couple of problems and have a few questions where I would like to seek advice:
1.) Procedure with and without the BULK COLLECT clause took the same time for 100,000 records. I thought I would see improvement in performance when I include BULK COLLECT with LIMIT.
2.) Updating the Load_Flag in source_table happens only for few records and not all. I had expected all records to be updated
3.) Are there other suggestions to improve the performance? or could you provide links to other posts or articles on the web that will help me improve the code?

Notes:
1.) 8 Destination tables have at least 2 Million records each, have multiple indexes and are accessed by application in Production
2.) There is an initial load of 1 Million rows with a subsequent daily load of 10,000 rows. Daily load will have updates for existing rows (not shown in code structure below)

The structure of the procedure is as follows

Declare
dest_type is table of source_table%ROWTYPE;
dest_tab dest_type ;

iCount NUMBER;

cursor source_cur is select * from source_table FOR UPDATE OF load_flag;

BEGIN
OPEN source_cur;

LOOP
FETCH source_cur -- BULK COLLECT
INTO dest_tab -- LIMIT 1000
;

EXIT WHEN source_cur%NOTFOUND;

FOR i in dest_tab.FIRST .. dest_tab.LAST LOOP

<Insert into app_tab1 values key, col12, col23, col34 ;>

<Insert into app_tab2 values key, col15, col29, col31 ;>

<Insert into app_tab3 values key, col52, col93, col56 ;>

UPDATE source_table SET load_flag = 'Y' WHERE CURRENT OF source_cur ;

iCount := iCount + 1 ;

IF iCount = 1000 THEN
COMMIT ;
iCount := 0 ;
END IF;

END LOOP;

END LOOP ;

COMMIT ;
END ;

Edited by: user11368240 on Jul 14, 2009 11:08 AM