Re: Optimizing commit intervals for efficiency

>Does anyone have experiences in tuning commit intervals in a typical batch
>application?
>
>Say that you have a batch of 100000 transactions and every transaction on
>average does 10 updates. Are there any formulas/rules of thumb of how many
>transactions should be processed between each commit? Or is this something
>you have to find out the hard way? Or is the commit overhead in Oracle
>ignorable so that you would commit every transaction?
>
>Thank you for wasting time on this.
>- Jarl Petter
>
>

Having had to do the exact same thing, (process 4,000,000 rows), and do it without killing the system... and be able to restart...

In my experience, I/O was a problem. I determined this by using the performance tools that came with my server (NT4.0). Not being a trained DBA, but doing it anyway (small shop - no trained DBAs...), it looked as though the log files were getting written to faster than they got emptied. I increased the groups and members, , and that helped a little. What really helped was to modify my commit interval on the fly. In a nutshell, what happens is:

1. Build a list of the IDs of the rows you will be processing in a table.
2. Decide on an initial interval between commits (I chose 5 seconds)
3. Choose an initial batch size. This is how many rows you will process before a commit.
4. Get the 1st batch of IDs to process from your list, and process them.
5. Determine how long it took to process the batch (use the DBMS_UTIL.GETTIME procedure)
6. Adjust the batch size accordingly. (Warning - be sure you don't set the batch size to 0). I put a 'govenor' on my batch size adjustment so it would only vary by a factor of 2 (up or down) with each iteration.
7. Update the list of IDs showing which have been completed
8. Commit
9. Get the next batch and repeat for a few days ;).

My guess is that there is some reasonably optimum time interval (which will depend on your hardware and your processing), as well as the desired recoverablilty, and availabity of rollback segments, etc. You will have the best results if you can do more work in the SQL engine than in PL/SQL (ie, do a group by in SQL rather than your own counters and check)

HTH Dan Hekimian-Williams Received on Wed Sep 08 1999 - 06:51:25 CEST