11.2 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. 11.2.1 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: 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 tables over a period of time to get a feeling for how often you need to clean the tables out. Remember that the goal with the DELETE audits is to be able to recover the deleted information, if necessary. Be sure to export from the audit tables often and keep the exports of the deletion information in a safe place! 11.2.2 Performance Suggestions Because performance is almost always an issue in a production database, you want to carefully map out how you are going to use this auditing tool. Here are some suggestions you can follow to help avoid degradation of performance on your system if you use this application: -
Do not index the AUDIT_ROW, AUDIT_COLUMN, and AUDIT_DELETE tables. Since you will rarely select from them, the index is not usually necessary. Having to update the index on each transaction will bog things down. -
If you are doing bulk updates or deletions and you don't need to audit the transactions, disable these triggers before the run by using the following command with the appropriate trigger name : ALTER TRIGGER triggername DISABLE; -
and enable the triggers when the large data load is completed. This will save a lot of audit writing, rollback allocation, and redo logging. -
Try to keep the trigger code as short as possible. Prior to Oracle version 7.3, triggers were not stored in compiled form in the database. In versions earlier than 7.3, the triggers must be compiled and parsed upon execution. However, once they have been compiled and parsed, they will be retained in the Shared Pool of the System Global Area (SGA). Thus, after the initial execution, the trigger performance will be better. Procedures, packages, and triggers (in version 7.3 and above) are stored in compiled form and will also be cached in the Shared Pool and can be pinned. -
Track only to the level of detail you need. For instance, if you don't care about the old or new values of updated columns , omit that portion of the system.  |
