Re: Oracle 11.2 auditing perf
- From: Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 15 Oct 2015 10:51:48 -0500
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
Other related posts:
