Re: why frequent commits are not good?

The point about frequent commits being bad is usually made with regard to batch updates.

PL/SQL Code which updates one row at a time and commits is much less efficient than code which updates all the target rows then commits. On the other hand, if the big update fails for any reason, rollback (hence restart) times can be huge. For this type of operation, it is common therefore to update in reasonable sized batches and commit regularly but not extremely frequently. Obviously, as others indicate, this requires some thought about the logic of incomplete batch updates.

Your other point about distributed SELECTs requiring a commit, and web applications has some elements that need consideration.

Distributed SELECTS do start a local transaction. If you NEVER commit, and stay connected
indefinitely, you will stop your rollback segment from being recycled - clearly, therefore, your session must commit or rollback at some point to avoid the side-effects on rollback segment sizes.

In these 'no action' distributed transactions, the rollback header and a rollback block are updated, so some redo is generated, so the commit or rollback does do some work. (A commit/rollback normally does nothing if there is nothing to commit or rollback). A rollback _appears_ to be slightly cheaper than a commit in these circumstances - but in operational terms, due to redo wastage, I suspect that there is no difference in performance.

However, committing after every select is an undesirable overhead - the cost of starting a new transaction is not trivial. It is interesting to note that Oracle's web application server always finishes a dialogue with 'reset package states' and 'rollback'. If your web app is connecting and disconnecting all the time, then it will not scale - so you should probably emulate Oracle's approach, with a near-permanent connection that 'resets' itself after each dialogue. This should supply the single 'rollback' you need to clear the local transaction. Since Web apps tend to be short snappy dialogues, this will probably be sufficient to avoid any side effects on size of rollbacks.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



NetComrade wrote in message <3ab2422b.596607426_at_news.earthlink.net>...


>I've read somewhere that frequent commits are not necessarily good for

>the database, can't find the doc, does anybody recall why?

>

>also, it appears that a SELECT over a db_link requires a commit after

>execution.

>

>our developers are thinking : 'why don't we just commit after every

>SELECT executed?' (they think it would be easier to implement.

>

>Can this potentially lead to problems?

>

>Thanx.

>---------------

>In case I forgot to mention:

>We use Oracle 7.3.4 and 8.1.6 on Solaris 2.6, 2.7 boxes

>---------------

>Andrey Dmitriev eFax: (978) 383-5892  Daytime: (917) 750-3630

>AOL: NetComrade ICQ: 11340726 remove NSPAM to email