10.4 Auditing and Performance
Auditing is usually not an enormous drain on performance, but there is an impact. Generally, the cost to performance of auditing is under about 5 percent. Enabling auditing on various areas of the database can affect performance in two ways:
If you enable auditing on a table with which users interact heavily, an entry will be made to the audit trail each time the audited action occurs. The overhead of frequent writes to the audit trail can impede overall database performance. Inside a PL/SQL program, statements are individually audited. Therefore, if a PL/SQL script is being run with auditing enabled on a table that is accessed in a loop, each time the table is accessed a record will be written to the audit trail. This could present a potentially high performance cost if too many objects or audit types are enabled.
If you are auditing to the database, every row of audit data is written to the system tablespace by default. The more objects you audit, the greater the amount of storage space consumed and the more potential fragmentation to the tablespace. You must ensure that the amount of auditing enabled is sufficient for the purposes you have envisioned but is not excessive. Monitoring the amount of data being stored into the SYS.AUD$ table on a daily basis during the time in which auditing is enabled will help the DBA learn how frequently the SYS.AUD$ table needs to be archived and cleaned out. You must be willing to take a proactive stance when enabling auditing to ensure that the performance costs of space used and system slowdown will remain reasonable.
Once a user has connected to the database with either session or privilege auditing enabled (we'll describe these types in the Section 10.6 later on), auditing will remain in effect for that user's session, even if auditing is disabled after the session has started. On the other hand, if modifications are made to schema object audit options, those options become available immediately and take effect in the current user 's session.