Re: intermittent commit on insert ?

On Tue, 4 Jun 2002 19:00:48 +0200, "Norbert Vossiek" <norbert.vossiek_at_gmx.li> wrote:

>You mentioned the performance hit of intermittent commits. Hhm. Let's say we
>insert some 1.000.000 records (which we do in cursor loops). What's the
>impact of e.g. 1.000 commits instead of one? Where would this effect come
>from? Task switching? Non-contiguous I/O?
>
>We use a configurable commit number variable and do not see any mentionable
>effect on such bulk transactions, i.e. if we vary the commit rate from every
>100th to every 1000th record.
>
>Norbert

Think about it. THINK. If you have a *logical* transaction consisting of 1 million records, what is the effect if you split it up into smaller ones?
You are approaching databases from the wrong end. If you need a rollback segment of 1G to store your 1 million records, you need a rollback segment of 1 G. It are the pennywise and pound-foolish who start to commit every n records. The day will come when they regret it.

And, oh yes, just to inform you : every transaction has overhead. Limiting the number of rows committed, will increase the overhead, will increase the redolog volume etc.
And just another one: it will increase the chance you are hitting the famous ora-1555 error, rollback segment too small, snapshot too old. That error is likely *being caused* by your 'smart' committing every n records.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Jun 04 2002 - 12:29:23 CDT