098 - 11.2 About Performance and Storage


Oracle Security
By William Heney, Marlene Theriault
Table of Contents
Chapter 11.  Developing a Sample Audit Application

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:

  • 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 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.


Oracle Security
Oracle Security Handbook : Implement a Sound Security Plan in Your Oracle Environment
ISBN: 0072133252
EAN: 2147483647
Year: 1998
Pages: 154

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net