Re: Best way to clear stale data from very active audit tables

On Jul 23, 12:37 pm, "bobdu..._at_gmail.com" <bobdu..._at_gmail.com> wrote:
> Hi there, i have a java application using oracle's jdbc driver that
> has a bunch of audit style tables. We're experiencing significant
> issues with our application when the data within these tables is being
> purged, and i need to know a better method for designing the schema
> (or application) to better handle this situation.
>
> Typically:
> - our "audit" table is created with a bunch of fields, one being
> "auditdate" which is a timestamp with a typical index on it.
> - this table is continually insert'ed into
> - select queries are run against this table on a regular basis
> - once an hour our "purge" job runs that does:
> DELETE FROM audits WHERE auditdate < (CURRENT_TIMESTAMP + INTERVAL
> '-7' DAY(5))
>
> When this table gets upwards of 20million+ records and many inserts
> are happenning concurrently, the purge job hammers the system, and
> causes the subsequent inserts to slow to a crawl, not to mention the
> DELETE command above to start taking upwards of 10+ minutes to run.
>
> I've debated doing a SELECT auditID with the where above and deleting
> the records one by one, but that seems horribly inefficient. Stopping
> the inserts altogether during the purge is not really an option. Can
> someone suggest an alternate approach or design for this scenario?
>
> A few other points:
> - our application has several different "audit" style tables, each of
> them has similar requirements, some get purged every week, some every
> 2 hours. I'm hoping this approach is universal for all because all
> are experiencing similar problems.
> - our application can also run against sql server, which while fast,
> occasionally has deadlock issues doing the above purging, inserting,
> and selecting concurrently... but it is fast. This also means we aim
> to have db agnostic code but i realize this is not always possible.
>
> Thank you for any advice you can offer!!
>
> Bob

Is the index on "auditdate" table being used by Oracle? I tested your statement and came up with "table access full" on the explain plan. Using "DELETE FROM audits WHERE auditdate < sysdate-7" instead, came up with "index range scan" on the plan.

I'm assuming that the table contains 168 hours (plus the current fraction) worth of logs and with the DELETE statement you posted (which runs hourly), you intend to delete 1 of those hours (the stalest)

Kind regards
C. Received on Mon Jul 23 2007 - 13:20:24 CDT