Sys.aud$ Issue In OUA DB (Doc ID 2234067.1)

Last updated on FEBRUARY 15, 2017

Applies to:

Oracle Utilities Advanced Spatial and Operational Analytics - Version 2.5.1.0.0 and later
Information in this document applies to any platform.

Goal


sys.aud$ issue in OUA DB  

------------------------------------
The following query is observed to put a heavy load on OUA production DB server:

SQL Id: 4ztz048yfq32s
SQL Text:
SELECT TO_CHAR(CURRENT_TIMESTAMP AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp,
  COUNT(username)                                                                 AS failed_count,
  TO_CHAR(MIN(TIMESTAMP), 'yyyy-mm-dd hh24:mi:ss')                                AS first_occur_time,
  TO_CHAR(MAX(TIMESTAMP), 'yyyy-mm-dd hh24:mi:ss')                                AS last_occur_time
FROM sys.dba_audit_session
WHERE returncode != 0
AND TIMESTAMP    >= CURRENT_TIMESTAMP - TO_DSINTERVAL('0 0:30:00');

This query is executed by Oracle Enterprise Manager (OEM or EM) running on OUA DB server .

It's responsible for large part of I/O load on the database, therefore decreasing available performance of server.

Query performance is poor because of large number of records in SYS.AUD$ database table, which keeps audit trail of all database logins.


Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms