Re: Deleting Large Numbers of Rows w/o Filling up my redo logs

Pat wrote:
> I'm managing a server that spins about a million rows of logging
> information a day. This is up from about 200k rows a year ago, or
> about 400k rows last summer.
>
> Since we didn't want the log table to grow to absurd sizes, we like to
> only keep about 7 days worth of logs. So we've got a nightly job that
> basically does:
>
> begin transaction
> delete from log_table where timestamp < 7 days ago
> commit transaction
>
> Historically, this worked fine, although, for obvious reasons, the
> disks spun like rabid badgers while that job was running.
>
> Recently, as the logs have gotten larger though, the cleanup job has
> been putting so much stuff in the redo logs that the rest of the
> activity on the server is grinding to a halt.
>
> I changed the job recently to do something along the lines of:
>
> begin
> for i in reverse 7..30
> loop
> for j in reverse 1..24
> loop
> delete from log where "stamp" < sysdate - i - j/24;
> commit;
> end loop;
> sys.dbms_lock.sleep(60);
> end loop;
> end;
>
> So I'm deleting 1 hours worth of log at a time, or maybe 50k rows
> instead of 1M.
>
> In test, that seemed to work better e.g. redo logs seemed healthy and
> I got lots of little commits instead of one mega commit.
>
> My questions through are:
>
> 1) I'm putting lots of small transactions into the redo logs instead
> of one mega transaction, but I'm still spinning the same absolute
> amount of redos aren't I? Am I just going to move the problem here so
> that the redo's will be full of lots of little commits and the log
> writer won't be able to spool them off to archive logs mode quickly
> enough?
>
> 2) Is there a "better" way to delete a big chunk of rows? If I could
> take the database out of production I know the usual tricks e.g. copy
> the rows I want to keep into a new table, then drop the old one, but
> this is a live table that I can't mess with that way.
>
> Basically, how are other people deleting large numbers of rows?

If Enterprise Edition partition by range. Then you can truncate partition and have essentially no logging. Partitioning Option is an extra license so check with your sales rep.

If you are not using EE then you might consider getting an EE license for 1 core or the minimum number of named users and go that way. Anything in SE, with this much data, is going to be painful.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org