Moving Audit trail objects to different table-space to gain performance

Moving Audit trail objects to different table-space to gain performance:

If you have enabled audit trail to "DB" and and auditing all / max users, then you may observe some performance issues if your system is high CPU bound.

In one of my production system, I moved Audit trail objects to SYSAUX tables space and reduced some performance over-head.

With AUDIT_TRAIL set for database (AUDIT_TRAIL=db), and the AUD$ and FGA_LOG$ tables located in a dictionary segment space managed SYSTEM tablespace, "gc" wait events are sometimes observed during heavy periods of database logon activity. Testing has shown that under such conditions, placing the AUD$ and FGA_LOG$ tables in the SYSAUX tablespace, which uses automatic segment space management, reduces the space related wait events.

The impact of verifying that the AUD$ and FGA_LOG$ tables are in the SYSAUX table space is low. Moving them if they are not located in the SYSAUX does not require an outage, but should be done during a scheduled maintenance period or slow audit record generation window.

Use the DBMS_AUDIT_MGMT package to move them to the SYSAUX tablespace:


BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
--this moves table AUD$
audit_trail_location_value => 'SYSAUX');
END;
/


BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
--this moves table FGA_LOG$
audit_trail_location_value => 'SYSAUX');
END;
/

Please check in your test systems first then plan for your production after proper analysis.

Thanks.