| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizing commit intervals for efficiency
Jarl Petter Kvalsvik wrote:
>
> Hi!
>
> 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
There is no real "commit" overhead as such...
(Discussion about buffer, dbwr etc ommitted...) Oracle writes your changes pretty much to your tables/disks/files as they occur, so committing versus not committing really wont change the performance as such...
What is of concern what designates a logical unit of work for your batch ...If you want to be able to re-run the whole thing if it falls over, then commit only at the end and have rollback segments large enough to handle it.
If you want to have the job restartable from a particular txn, then commit after 'n' txns where
'n' is the max amount of work you want to redo after failure, and 'txn' is something that makes sense to your batch processing...
HTH --
"Some days you're the pigeon, and some days you're the statue." Received on Fri Sep 03 1999 - 04:01:09 CDT
 |
 |