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

On Jul 23, 4:03 pm, "Chris L." <diver...@xxxxxxxxxx> wrote:
On Jul 23, 4:36 pm, "bobdu...@xxxxxxxxx" <bobdu...@xxxxxxxxx> wrote:



On Jul 23, 2:20 pm, "Chris L." <diver...@xxxxxxxxxx> wrote:

On Jul 23, 12:37 pm, "bobdu...@xxxxxxxxx" <bobdu...@xxxxxxxxx> 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.

Oracle has told me because of the inequality (<) that it cannot use
the index, or to create a "function based index". This table doesn't
always contain 168 (or whatever) hours of data, this is controlled by
the application/administror's purge settings. It is as small as a
week, as long as they want, if they want to retain the audits for 4
months, they can, we'll still run this delete command every hour
(thats right - every hour we run this removing the stale elements).
As well, the "retain logs for X weeks" setting is configurable on the
fly, so ideally changing it won't result in redesigning the table (but
if thats the case then so be it).

If a partitioned table is the answer, do you think i could set this up
from an application, ie via sql? Would the partitioning scheme need
to be adjusted all the time, ie by creating new partitions as the days/
weeks go by? If anyone has any sample sql i'd love to see it.- Hide quoted text -

- Show quoted text -

An index won't be used in inequality ( != ) but will most certainly be
used in your case ( < ). As I said, using "less than sysdate-7"
caused the index to be used. For some reason, the "> CURRENT_TIMESTAMP
+ INTERVAL '-7' DAY(5)" part is what isn't allowing the index to be
used.

About partitions I can't offer any tips sorry... other than setting up
a night job that will both drop the oldest partition (from 7 days ago)
and create a future partition (for 7 days from now). This would mean
the purging would happen daily not hourly. (This shouldn't be a
problem really). I don't know if a partition-by-hour is possible /
recommended.

Regards
C.

On our production system, this is what one of the recommendations for
our query is (table name is different here, and retention period is
one week):

SQL The predicate
SYS_EXTRACT_UTC("PACKETLOGS"."LOGDATE")<SYS_EXTRACT_UTC(CURRENT_TIMESTAMP(6)+INTERVAL'-00007
00:00:00' DAY(5) TO SECOND(0)) used at line ID 2 of the execution plan
contains an expression on indexed column "LOGDATE". This expression
prevents the optimizer from selecting indices on table
"ORACLEUSER"."PACKETLOGS".

I don't want to use the SYSDATE as its a DATE and that is NOT going to
translate well when the retention period is smaller, ie a few hours
(not for this table but for others). What about if i used
LOCALTIMESTAMP - i've done a quick scan and it might be because the
CURRENT_TIMESTAMP is timezone specific, wheres the logdate is not. I
can assume our app's clients all run in the same timezone (hopefully
anyways!!!)... i'll give this a shot and i'll let you know what i
find.

.

Relevant Pages

  • Re: Best way to clear stale data from very active audit tables
    ... has a bunch of audit style tables. ... We're experiencing significant ... About partitions I can't offer any tips sorry... ...
    (comp.databases.oracle.server)
  • sql CLR trigger causing havoc in Access linked table
    ... Excuse if this is not the correct group -- I am not using an adp, but an upsized mdb file with linked tables. ... Have been running an Access front end on a SQL Server back end for several years. ... However, when the CLR trigger fires, SQL returns the ID of the row from the audit table, not the table where the original insert occurred. ... The new record still gets inserted correctly; the trigger fires correctly; the problem is solely how Access represents the current/new record. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Problems launching DTS package from Stored Procedure
    ... exec sp_bcp_dbcmptlevel "UsersDatabase" SET FMTONLY ON Select * from Users1 ... Audit Logout ... Here is an example of an UNSUCCESSFUL sql profiler log: ... > I cannot run enterprise manager on our server because it is not installed. ...
    (microsoft.public.sqlserver.dts)
  • Re: Sql Profiler trace file converted to a table
    ... 'Login Failed') ... 'SQL Transaction') ... 'CursorPrepare') ... 'Audit Statement GDR') ...
    (microsoft.public.sqlserver.security)
  • Re: file backup component
    ... I've gotten my problem solved.The reason why my SQL service sometimes can ... Otherwise you can try to use audit techniques to find out who's trying to ... file backup app or any other app that need LDF file a while. ... Delay the start of the SQL server service until manual start. ...
    (microsoft.public.windowsxp.embedded)