Re: intermittent commit on insert ?

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

"Daniel Morgan" <dmorgan_at_exesolutions.com> schrieb im Newsbeitrag news:3CFCE560.9EB15807_at_exesolutions.com...
> Sunil wrote:
>
> > I need to insert into a table T1, data from two views V1 or V2. The
name
> > of the view will be passed in as a parameter to my procedure( l_view) .
Also
> > Each user has a limit on the number of records he can insert (say
l_limit )
> > . This is also passed into the procedure.
> >
> > I Have been thinking about dynamic sql on the lines of (
> > 'insert into T1 select * from ' || l_view || ' where rownum < '
||
> > l_limit ) as row by row processing will be slow.
> >
> > But I am afraid of hitting the rollback segment limit. So I would
like to
> > commit when ever 'n' records are inserted ( again this can be passed as
a
> > parameter ) .
> >
> > Is this possible, somehow?
> >
> > Regards,
> > Sunil.
>
> It is possible ... you just create a counter variable ... but don't do it.
> Intermittent commits are a great way to destroy the performance and
scalability
> of your database.
>
> If you have a rollback segment issue ... enlarge the rollback segments.
>
> Fix the problem at hand ... don't write bad code to circumvent it.
>
> Daniel Morgan
>
Received on Tue Jun 04 2002 - 12:00:48 CDT