Re: intermittent commit on insert ?

Norbert Vossiek 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
>
> "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
> >

The issue is well documented ... especially well by Tom Kyte in his book and at his web site.

Daniel Morgan Received on Tue Jun 04 2002 - 14:45:51 CDT