Re: Rollback and slow delete

Fair enough. Dynamically expanding the RBS will certainly slow things down a bit (locally managed tablespace is recommended for rollback segments), but it's not going to make a huge difference.

I would have thought, however, that you would have only encountered the 1555 problem as a result of someone else modifying the rows your transaction is hoping to delete some time later. Sounds a bit weird to be allowing modifications of data which you are about to blow away.

If that's the case, have you considered selecting all the rows "for update" (and hence locking them exclusively) before then going ahead and deleting them?

As in:

SQL> select * from emp for update;
14 rows selected.

SQL> delete from emp;
14 rows deleted.

SQL> commit;

Regards
HJR "TC3 Racer" <gordonchapman_at_hotmailSENDNOSPAM.com> wrote in message news:uaSC9.5826$XN5.829754_at_wards...
> Thanks all for replies. We're not committing with this transaction, but
> another process is possibly doing an insert and commit while the delete is
> taking place, as this delete is no taking several hours. I suppose in part
> because it is dynamically expanding the RBS
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:olRC9.80641$g9.227248_at_newsfeeds.bigpond.com...
> >
> > "TC3 Racer" wrote in message
> > news:SDPC9.7391$dz2.953129_at_stones...
> > > Using Oracle 8i/NT4, the amount of data being loaded as suddenly
> increased
> > > and we have started getting performance problems when deleting
> > >
> > > In 5 days we have 14,000,000 rows and we delete ( or try to delete)
any
> > data
> > > old than 2.5 days, but are getting the error
> > >
> > > 'ORA-01555: snapshot too old: rollback segment number 5 with name
"RBS4"
> > too
> > > small'
> > >
> > > I'm not bothered about recovering this so I've set 'nologging' but it
> > still
> > > writes to the redo log.
> >
> > NOLOGGING *never* switches off logging for regular inserts, updates or
> > deletes. Ever. Those 'conventional' DMLs are always logged. NOLOGGING is
> for
> > one or two very special commands and operations, such as Create table as
> > select, insert /* + APPEND*/ into X select from Y, create index, and
some
> > others.
> >
> > >
> > > Will partitioning the table also help
> > >
> >
> > Not really. Or, I should say, not in and of itself. Your fundamental
> problem
> > is that your rollback segments are not big enough to accomodate the
> deletion
> > of 7,000,000 without looping back on top of itself. Partitioning a table
> > into smaller bits presumably doesn't mean you are going to suddenly be
> > deleting less than 7,000,000 rows. However, partitioning could help
> because
> > instead of doing a delete, if the rows you're getting rid of were in a
> > partition of their own, you could do a quick piece of Partition DDL and
> > truncate the partition (or drop it). So yes, it might, but only if it
were
> > partitioned in a way that let you perform DDL instead of DML.
> >
> > My real question to you is this, though: you can't (normally) get a 1555
> > unless a new transaction over-writes the undo/rollback generated by a
> > previous transaction. If you were deleting 7 million rows, and not
> > committing anywhere through that until right at the end, then the first
> row
> > to be deleted is the start of a transaction, and (because it isn't
> committed
> > until the 7 millionth row is deleted) it cannot be overwritten. If the
> > rollback segment was too small to accommodate all 7 million deletes,
then
> > the rollback segment would start to grow in size until it *could*
> > accommodate them all.
> >
> > Your rollback segment isn't doing that (it seems). Your later deletes
are
> > overwriting the rollback generated by the earlier ones. That can only
> happen
> > if you're committing periodically through the delete process. Are you?
If
> > so, don't.
> >
> > (You can get 1555s for other reasons, but I thought I'd check this one
out
> > first).
> >
> > Regards
> > HJR
> >
> > > Thanks
> > >
> > > GC
> > >
> > >
> >
> >

>
> Received on Wed Nov 20 2002 - 14:50:11 CST