Re: Rollback and slow delete

"TC3 Racer" <gordonchapman_at_hotmailSENDNOSPAM.com> 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 - 13:35:14 CST