Re: Oracle 11.2 auditing perf

On 2015/10/15 09:47, Leroy Kemnitz wrote:

> What are people doing to improve the performance of the sys.aud$ table in Oracle 11.2?? Custom indexing??
>
> I am purging records daily from the audit and only keeping what is required. I also moved the table out of the system tablespace and into an audit tablespace.
>
> This sql is still running extremely slow for me -
>
> ----------
>
> 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');
>
> ----------

 I'm not one to custom-anything in the SYS schema, even if it's something that seems as harmless as an index.

You purge the AUD$ table, but how big is it?

select sum(blocks)*8192 size_bytes from dba_extents where owner = 'SYS' and segment_name = 'AUD$' and tablespace_name = 'SYSAUX';

This will be important because your query is likely doing a full table scan, which will read the AUD$ table to the highwater mark.

Also, the "timestamp" column (a keyword...sigh) is a DATE datatype, so I believe there's casting involved with the use of current_timestamp. You might want to change "current_timestamp - TO_DSINTERVAL('0 0:30:00')" to "SYSDATE - 1/24/2" or something more maintenance-friendly.

Finally, if you're licensed for it, parallel is an option...

HTH! GL! Go Badgers!

Rich

--
http://www.freelists.org/webpage/oracle-l