Oracle Security

About Performance and Storage

Unquestionably, each of the audit trails you activate in your database add overhead to your system and consume disk space. The following sections provide suggestions you can implement to reduce the amount of overhead to your database performance.

Storage Suggestions

Ideally, you should create a separate tablespace for the audit data. Having the audit data on a separate tablespace will make administration considerably easier. Performance can be improved if you can put the audit data on a separate disk or filesystem from “real” data.

Make sure that you size the INITIAL and NEXT extents properly to:

• Avoid excessive dynamic space management and extension

• Not run out of extents (MAXEXTENTS)

• Avoid fragmentation

You may want to use the parameter AUTOEXTEND on the tablespace datafile for the audit trail storage to minimize the possibility of having a transaction hang due to lack of audit trail space. The AUTOEXTEND parameter enables the tablespace datafile to continue to extend automatically until it reaches a maximum space limit you have declared in the MAXSIZE parameter. The syntax you use to create a tablespace with AUTOEXEND enabled is:

CREATE TABLESPACE audit_data_ts 
  DATAFILE '/my_disk/my_dir/audit_data_ts01.dbf' 
   AUTOEXTEND ON NEXT 10m 
   MAXSIZE UNLIMITED
  DEFAULT STORAGE (INITIAL 100K 
                      NEXT 100K 
                MINEXTENTS 1 
                MAXEXTENTS UNLIMITED 
               PCTINCREASE 10) 
ONLINE;

You will want to purge the audit trail tables on a regular basis. Keep a close watch on the growth of the ...