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

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);

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? Received on Sat Dec 20 2008 - 10:30:44 CST