Re: When to Commit?

On Sat, 2 Jul 2005 05:14:18 +0800, "edmond" <edmond_at_hotmail.com> wrote:

>I often hear that for long txn, we should commit occasionally rather than
>having a long outstanding txn. But in Expert one-on-one Oracle, the author
>suggest we should commit when required rather than manually inserting commit
>i.e. if our logic is to commit at end of the long txn, so be it (so long as
>our rollback segment is large enough).
>
>What are the considerations behind these 2 approaches?
>

The considerations behind the first approaches are - we don't want big rollback segments
- we think we can avoid ora-1555, snapshot too old, by committing occasionally.

The considerations behind the second approach are - you shouldn't break up a *logical* unit of work into multiple physical transactions, because when you need to roll back the *logical* transaction, you can't anymore - Committing occasionally -above all inside a loop - *CAUSES* ora-1555 - There is overhead associated with a commit, so you are increasing the redolog volume
- Disk is cheap, so the first approach is pennywise and pound foolish. It just won't work, ever, and all the time you spend coding around your disk limitations would be better spend by doing things properly.

Obviously, as the author of Export one on one, makes abundantly clear, and what I have observed over and over and over again: the first approach doesn't *SOLVE ANYTHING*, and is just *PLAIN WRONG*.

A good question for a job interview would be: how do you prevent an ora-1555 (Note: even with UNDO segments ora-1555 can still happen!!) If the candidate answers: commit every 100 records or so, don't hire him.

--
Sybrand Bakker, Senior Oracle DBA