In Oracle Database Ver 12.2, when Unified Auditing is enabled, performance deteriorates compared to Ver 12.1.

(Doc ID 2341869.1)

Last updated on APRIL 09, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

If Unified Auditing is enabled in Oracle Database 12.2,
executing a large number of insert statements even if executing
"DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE" below is slow compared with Ver 12.1.

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,
DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);
END;
/

* Changed "AUDIT_TRAIL_WRITE_MODE" to "AUDIT_TRAIL_QUEUED_WRITE".

For example, if the INSERT statement is executed 200,000 times as follows,
processing time difference of 3 minutes or more occurs.
(The difference varies depending on data content and environment.)

==Ver12.2==============
SET TIMING ON
declare
begin
for i in 1..200000
loop
INSERT INTO TEST
VALUES(
'TESTDATA01',
sysdate
);
commit;
end loop;
end;
/

Elapsed: 00:04:39.86
====================

==Ver12.1==============
SET TIMING ON
declare
begin
for i in 1..200000
loop
INSERT INTO TEST
VALUES(
'TESTDATA01',
sysdate
);
commit;
end loop;
end;
/

Elapsed: 00:00:47.48
====================

Changes

 It can be a problem if you upgrade from Oracle Database 12.1 to Oracle Database 12.2

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