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?
I'd look at weekly partitions.

Palooka Received on Sat Dec 20 2008 - 15:36:06 CST