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:21:43 CST
