DB performance is slow due to sys.dba_audit_session job

Problem:

When running Enterprise Manager Database or Grid Console, the following Query is showing high I/O Waiting for the User the following query takes a lot of time to run:

SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count FROM sys.dba_audit_session WHERE returncode != 0 AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')

Cause:

The 'TO_CHAR' conversion to do the date arithmetic is redundant and should NOT be used. This was reported in an internal BUG.

There is a known performance issue with DBA_AUDIT_SESSION table per non-published Bug 7633167 : SQL PERFORMANCE ISSUE IN DBA_AUDIT_SESSION.

Solution:

You can either disable 'Failed Login Count' job in OEM or purge aud$ table.

Disable the 'Failed Login Count' metric:

1. In the Grid Control Console:
2. Navigate to Targets ->  Databases -> Click on the Database Link
3. Navigate to Oracle Database -> Monitoring -> Metric and Collection Setting

4. Find “Failed Logins” and click "Every 30 Minutes" link:

 

5. Under Collection Schedule click Disable button:

   

6. Click Continue button.
7. Click OK button.
8. You should this confirmation message:

9. Click OK button.

Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$:

To purge all the audit records from the audit trail run this command as SYS:

SQL> truncate table aud$;

To delete rows from the audit trail related to a particular audited object run this command:

SQL> DELETE FROM sys.aud$ WHERE obj$name='<object_name>';

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.