To provide effective help here, we really need to see your code! Along with performance metrics, breaking down how long it takes to execute each part. Otherwise we're just guessing.
I discussed ways to get the performance profile in yesterday's SQL Office Hours; the recording will be available for this "soon" at:
https://asktom.oracle.com/pls/apex/f?p=100:551::::RP,551:P551_CLASS_ID:7563 That said, you almost certainly want to address this:
We are looping through this 400 to 500 records and do some manipulation and insert into 4 tables which has sequenceSQL inside loops => SLOOOOOOOOOWWWWWWWW!
Either you want to insert the query directly:
insert into ....
select ...
Or use bulk collection:
select ... bulk collect into ....
forall i in 1 .. recs.count
insert ....