When to commit changes?

Question

Using oracle 10g , accessed via perl DBI , I have a table with a few tens of million rows being updated a few times per second while being read from much more frequently form another process.

Soon the update frequency will increase by an order of magnitude (maybe two). Someone suggested that committing every N updates instead of after every update will help performance.

I have a few questions:

• Will that be faster or slower or it depends (planning to benchmark both way as soon as can get a decent simulation of the new load)
• Why will it help / hinder performance.
• If "it depends ..." , on what ?
• If it helps what's the best value of N ?
• Why can't my local DBA have an helpful straight answer when I need one? (Actually I know the answer to that one) :-)

EDIT:

@codeslave : Thanks, btw losing uncommited changes is not a problem, I don't delete the original data used for updating till I am sure everything is fine , btw cleaning lady did unplugs the server, TWICE :-)

Some googling showed it might help because of issue related to rollback segments, but I still don't know a rule of thumb for N every few tens ? hundreds? thousand ?

@diciu : Great info, I'll definitely look into that.

Answer 1

Faster/Slower?
It will probably be a little faster. However, you run a greater risk of running into deadlocks, loosing uncommitted changes should something catastrophic happen (cleaning lady unplugs the server), FUD, Fire, Brimstone, etc.

Why would it help?
Obviously fewer commit operations, which in turn means fewer disk writes, etc.

DBA's and straight answers?
If it was easy, you won't need one.

Answer 2

A commit results in Oracle writing stuff to the disk - i.e. in the redo log file so that whatever the transaction being commited has done can be recoverable in the event of a power failure, etc. Writing in file is slower than writing in memory so a commit will be slower if performed for many operations in a row rather then for a set of coalesced updates.

In Oracle 10g there's an asynchronous commit that makes it much faster but less reliable: http://articles.techrepublic.com.com/5100-10878_11-6158695.html

PS I know for sure that, in a scenario I've seen in a certain application, changing the number of coalesced updates from 5K to 50K makes it faster by an order of magnitude (10 times faster).

Answer 3

Reducing the frequency of commits will certainly speed things up, however as you are reading and writing to this table frequently there is the potential for locks. Only you can determine the likelihood of the same data being updated at the same time. If the chance of this is low, commit every 50 rows and monitor the situation. Trial and error I'm afraid :-)

Answer 4

If you "don't delete the original data used for updating till [you are] sure everything is fine", then why don't you remove all those incremental commits in between, and rollback if there's a problem? It sounds like you effectively have built a transaction systems on top of transactions.

Answer 5

@CodeSlave your your questions is answered by @stevechol , if i remove ALL the incremental commits there will be locks. I guess if nothing better comes along I'll follow his advice pick a random number , monitor the load and adjust accordingly. While applying @diciu twaks.

PS: the transaction on top of transaction is just accidental, I get the files used for updates by FTP and instead of deleting them immediately I set a cron job to deletes them a week later (if no one using the application has complained) that means if something goes wrong I have a week to catch the errors.

Answer 6

As well as reducing the commit frequency, you should also consider performing bulk updates instead of individual ones.