In this blog I want to introduce you to a workaround for a performance issue which randomly appeared during the upgrades of several Oracle 12c databases to 19c I performed for a financial services provider. During the upgrades we ran into a severe performance issue after the upgrades of more than 40 databases had worked just fine. While most of them finished in less than one hour, we run into one which would have taken days to complete.
After starting the database upgrade from Oracle 220.127.116.11.0 to Production Version 18.104.22.168.0 the upgrade locked up during compiling:
One select-statement on the unified_audit_trail was running for hours with no result, blocking the upgrade progress and consuming nearly all database resources. The size of the audit_trail itself was about 35MB, so not the size you would expect such a bottleneck from:
SQL> SELECT count(*) from gv$unified_audit_trail;
After some research and testing (see notes below) I found the following workaround (after killing the upgrade process):
SQL> begin DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => TRUE); end; / SQL> set timing on; SELECT count(*) from gv$unified_audit_trail; exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
As a first attempt I used the procedure below, described in Note 2212196.1.
But flush_unified_audit_trail lasted too long, so I killed the process after it ran for one hour. The flash procedure again worked fine after using clean_audit_trail as described above:
SQL> begin DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; for i in 1..10 loop DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS; end loop; end; /
A few days later we encountered the same issue on an Oracle 22.214.171.124 database which requires Patch 25985768 for executing dbms_audit_mgmt.transfer_unified_audit_records.
This procedure is available out of the box in the Oracle 12.2 database and in the Oracle 126.96.36.199 databases which have been patched with Patch 25985768.
To avoid to get caught in this trap it is my advise that you gather all relevant statistics before any upgrade from Oracle 12c to 19c and to query gv$unified_audit_trail in advance. This query usually finishes within a few seconds.
Doc ID 2212196.1
Master Note For Database Unified Auditing (Doc ID 2351084.1)
Bug 18920838 : 12C POOR QUERY PERFORMANCE ON DICTIONARY TABLE SYS.X$UNIFIED_AUDIT_TRAIL
Bug 21119008 : POOR QUERY PERFORMANCE ON UNIFIED_AUDIT_TRAIL
Performance Issues While Monitoring the Unified Audit Trail of an Oracle12c Database (Doc ID 2063340.1)