Oracle-DB: Accessing Unified_Audit_Trail is very slow. Why?

After migrating DB instances to Exadata cloud service in Oracle's OCI cloud we noticed that selects from the view UNIFIED_AUDIT_TRAIL did not succeed in accepted time, even if they were quite simple.
Looking at the execution plan shows that nearly the whole excution time is spent on full table scan on table X$UNIFIED_AUDIT_TRAIL.
A click in the column "Waits" in that line shows the wait events for that execution plan line that have been recorded by ASH.
Mostly X$-tables will present an SQL interface for Oracle's internal memory structures. In this case the wait event 'Disk file operations I/O' let the X$UNIFIED_AUDIT_TRAIL table look like a wrapper for file structures on disk.

Trying to explain what happened

Some blog posts and a note in the documentation gave the needed background info.

Note:1. When the database is writeable, audit records are written to the unified audit trail. If the database is not writable (typically occurs when the database is closed or is read-only as in Oracle Data GuardADG), the Oracle Database writes audit records to external operating system spillover .BIN files in the $ORACLE_BASE/audit/$ORACLE_SID directory. The audit data present in the .BIN files is also surfaced in the UNIFIED_AUDIT_TRAIL data dictionary view.
The view UNIFIED_AUDIT_TRAIL is fed from two sources:
  • The table audsys.AUD$UNIFIED contains the audit records created when the DB is writable
  • The table sys.X$UNIFIED_AUDIT_TRAIL shows the audit records in file system created when the DB is not writable. The view GV$UNIFIED_AUDIT_TRAIL provides access on this source across RAC instances.<
Our DB was cloned from a standby instance which had a lot of unified audit policy actions and had been used by lots of read/only connections.
So several millions of unified audit records have been stored in file system. This slowed down access by view UNIFIED_AUDIT_TRAIL up to unusability.

One-off solution in our case was to purge the unified audit trail by DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL.

The investigation has been done using the free analysis tool Panorama (https://github.com/rammpeter/panorama).

Comments

Post a Comment

Popular posts from this blog

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo...
Read more

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet...
Read more

Oracle-DB: Identify excessive logon/logoff operations with short-running sessions

Image
Creating and destroying database sessions in loop may lead to significant database load. This blog post contains more about quantifying this load. Applications should use connection pooling at client or database side to prevent excessive session creation. But how can I identify the client processes causing this sessions? Scan DBA_Audit_Trail to identify context info If auditing logon/logoff-operations is activated for your database, scan DBA_Audit_Trail. You can do this by 2 was: Alternative 1: Use my tool "Panorama" to evaluate audit trail Panorama is available here. Go to menu "DBA general" / "Audit Trail": Select start and end time for selection Filter column "Action" for "LOGON" Set "Grouping" to "Minute" Execute selection via button "Show audit trail" Add columns of your choice to diagram by context menu (right mouse button) This way you can identify the machine, DB-user ...
Read more