Performance Issues Caused by DB Collector When AUD$ Is Very Large (Full Table Scan on AUD$) (Doc ID 1356088.1)

Last updated on APRIL 20, 2015

Applies to:

Oracle Audit Vault - Version 10.2.3.2 and later
Information in this document applies to any platform.
Checked for relevance on 08-MAR-2013

Symptoms

The database collector of the Audit Vault causes massive performance issues on source databases because of time consuming full table scans on the aud$ table. This happens when the aud$ table is very large.
The statement found in top of the AWR report is:

select SESSIONID, ENTRYID, STATEMENT, USERID, USERHOST, TERMINAL, ACTION#, RETURNCODE, OBJ$CREATOR, OBJ$NAME, AUTH$PRIVILEGES, AUTH$GRANTEE, NEW$OWNER, NEW$NAME, SES$ACTIONS, SES$TID, LOGOFF$LREAD, LOGOFF$PREAD, LOGOFF$LWRITE, LOGOFF$DEAD, COMMENT$TEXT, CLIENTID, SPARE1, SPARE2, PRIV$USED, SESSIONCPU, to_char(NTIMESTAMP#,'MM-DD-YYYY HH24:MI:SS.FF'), PROXY$SID, USER$GUID, INSTANCE#, PROCESS#, XID, AUDITID, SCN, AV_TRUNCATE_CLOB(SQLBIND), AV_TRUNCATE_CLOB(SQLTEXT)
from SYS.AUD$
where :prev<NTIMESTAMP# and NTIMESTAMP#<=:cur and INSTANCE# is not NULL;




Cause

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