Considerations for commit frequency in Oracle / Pl*Sql
Posted on 2009-05-11
I have a procedure that processes anywhere from 1M to 4M records. Basically it moves it from table A (staging) to table B (final production).
The commit frequency is 100 records !
I'd like to increase this to something big, like 10,000.
The code queries all unprocessed records, then updates to "loaded" after 100 records, then queries again all unprocessed records, etc. etc.
So if I am processing 4,000,000 records, the cursor is being executed 40,000 times !
So by increasing to 10,000 it seems like I can reduce the overhead a lot.
so am I looking at this correctly? anything else to look at?