SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
---------------------------------------------------------------------------------
When looking at SQL Monitoring in Oracle Enterprise Manager, I find that this query is causing full tablescan on SYS.AUD$ in all our databases, i.e. test/dev and production databases:
sqlid: 4ztz048yfq32s
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');
The query monitors failed login counts.
As aud$ is has a size of 2 GB in one of our production databases, this tablescan generates 2 GB IO in that database, every 30th minute. All other databases are also experiencing tablescan on AUD$ from this query, and the shared disks rack in test/dev is also loaded by these tablescans.
The problem is missing indexes on SYS.AUD$ combined with the criteria "...WHERE returncode != 0...".
If the returncode in AUD$ is always >=0, then the criteria can be changed to "...WHERE returncode > 0...". Combined with this index:
create index aud$_idx3 on sys.aud$(returncode,timestamp#);
...the tablescan is gone and execution time is reduced from up to 39 seconds for the 2 GB tablescan to a few milliseconds.
My questions are:
1) I am too picky when a raise this query as a concern to the dba-team? Our database is not IO-bound, but has a general challenge with bad performance.
2) This is a query that we cannot change, as it is generated by Oracle Enterprise Manager Agent. Would it be feasible to point out this to Oracle so that the query and indexing can be improved?