Purging Audit Logs Taking Too Long Using DBMS_AUDIT_MGMT. How to tune DB_DELETE_BATCH_SIZE ? (Doc ID 1920432.1)

Last updated on OCTOBER 10, 2022

Applies to:

Goal

Using DBMS_AUDIT_MGMT to purge the audit logs take too much time, using the following procedure:

declare
retention_days number;
begin
retention_days := 100;

-- set last_archive_timestamp
SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => (sysdate - retention_days),
rac_instance_number => 1);
SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
last_archive_time => (sysdate - retention_days),
rac_instance_number => 1);

-- purge audit logs
SYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_DB_STD,
use_last_arch_timestamp => TRUE);

end;

Resulting in a query like the following one being executed on the system:

DELETE FROM SYS.AUD$ WHERE DBID = 3696240454 AND NTIMESTAMP# < to_timestamp('2014-05-10 13:00:00', 'YYYY-MM-DD HH24:MI:SS.FF') AND ROWNUM

This query took more than 8 hours to finish, as it's execution plan is using a full table scan. Is there a way we can improve it's performance?
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.