现象：

今天上午发现外网数据库有几个sql特别的慢，经过分析这几个sql是往aud$基表中插数据的sql，这个表现在有记录数5千多万，占据了系统表空间约10g的空间，这样会导致数据库的性能下降（虽然oracle11g文档说性能影响可以忽略，但是通过我们的观察，确实带来性能下降明细），这样下去，随着审计记录的增长，会有将系统表空间撑爆的可能性。

另外查看了其他几个11g的数据库，都有这个现象，只是其他库的这张基表的数据目前还没有那么大（几十万行记录）。

 

原因分析：

在oracle11g中，数据库的审计功能是默认开启的（这和oracle10g的不一样，10g默认是关闭的），oracle11gR2的官方文档上写的是错的，当上说default是none，而且是审计到DB级别的，这样就会往aud$表里记录统计信息。

 

问题处理方法：

1.如果审计不是必须的，可以关掉审计功能；

SQL> alter system set audit_trail=none scope=spfile;

SQL>showdown immediate;

SQL>startup

2.删除已有的审计信息

可以直接truncate表aud$,

或者采取dbms_audit_mgmt来清除。

3.或者将aud$表移到另外一个表空间下，以减少system表空间的压力和被撑爆的风险。

 

附：11g中有关audit_trail参数的设置说明：

AUDIT_TRAIL

AUDIT_TRAIL enables or disables database auditing.

Values:

• none

  Disables standard auditing. This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant. If you created the database using Database Configuration Assistant, then the default is db.

• os

  Directs all audit records to an operating system file. Oracle recommends that you use the os setting, particularly if you are using an ultra-secure database configuration.

• db

  Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.

  If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

• db, extended

  Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.

  If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

• xml

  Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

• xml, extended

  Performs all actions of AUDIT_TRAIL=xml, and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. These columns are populated only when this parameter is specified.

You can use the SQL AUDIT statement to set auditing options regardless of the setting of this parameter.