Re: When to Commit?

On Sat, 02 Jul 2005 05:14:18 +0800, edmond interested us by writing:

>
> 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?

Ask those from whom you hear those comments for proof and you will find that often it"s based on "In SQL Server we ..." or "It's true - it happened to a friend of a friend of mine."

Generally I find the first recommendation comes from DBAs and developers who have had experience with non-Oracle databases and have assumed that Oracle works the same as the others.

While Oracle conforms to the SQL spec as well as any of the others (plus/minus a bit in all cases), the spec allows the vendors to use different internals. It's those internals that demand different development and design principles, especially in procedural interaction. What works well on one may truly suck on others (as described in Tom's books).

If you dig into it enough, you will find that frequent commits inside a long transaction will actually use additional resources and will make a typical 'longer transaction' unscalable to the point that no amount of added CPU will cause it to complete faster or allow one more user. And, it can aggravate the ORA-01555 problem that it is supposed to alleviate.

You can believe Tom Kyte's information, especially since the two books ("Expert One on One Oracle" and "Effective Oracle by Design") include examples that you can use to prove things to yourself.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***