performance issue how many records to commit
721678Sep 9 2009 — edited Sep 9 2009Hi, there,
I have a PL/SQL to process some data. Could someone help me understand how the number of rows commited in one loop impact the performace? how to find a approperiate commit size?
here's some test result:
test #, total amount of rows processed, number of rows commited in each loop, time spent, average time spent
test#1: 150,045, 100,000, 25min 52sec, 0.01034 s/record
test#2: 45,418, 1000,000, 41 min 11 sec, 0.0544 s/record
test#3: 1,976,568, 1000,000, 2 hr 17 min 10 sec, 0.00416 s/record
Looks like a bigger commit size is bad at processing smaller amount of records. But what would be criteria to pick the size?
Thanks