Why would smaller commits increase the log volume? You're still inserting
the same amount of data into the table. The miniscule amount of log space to
hold the commit records isn't even worth mentioning.
I think everyone is missing the point here though - how much RBS space is
consumed by inserts? It's tiny! The space required to insert 1m rows is
problably around 10m. If you're RBS can't afford a 10m RBS you've got some
serious space issues.
--
Chuck Hamilton
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:7utpfuooqromk8krj1dd3nj0b73a8go8ga_at_4ax.com...
> 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 - 14:37:29 CDT
