Introduction
Oracle Database 19c supports both traditional standard auditing and the newer unified auditing framework. When running in mixed mode (the default configuration), both auditing mechanisms can operate simultaneously, providing comprehensive audit coverage during migration periods. This blog demonstrates how both auditing types work together and showcases the performance benefits of unified auditing's partitioned architecture.
Understanding Mixed Mode Auditing
In mixed mode, Oracle Database allows:
- Standard Auditing: Traditional audit trail stored in
SYS.AUD$and accessed viaDBA_AUDIT_TRAIL - Unified Auditing: Modern audit trail stored in
AUDSYS.AUD$UNIFIEDand accessed viaUNIFIED_AUDIT_TRAIL - Simultaneous Operation: Both mechanisms can capture the same events
Environment Setup
First, let's verify the audit configuration and create a test user:
-- Check audit trail parameter
SHOW PARAMETER audit_trail;
NAME TYPE VALUE
----------- ------ ------------
audit_trail string DB, EXTENDED
The DB, EXTENDED value indicates standard auditing is enabled with extended information.
-- Create test user
CREATE USER donghua IDENTIFIED BY ComplexPassword;
GRANT DBA TO donghua;
Configuring Standard Auditing
Standard auditing uses the traditional AUDIT command to track database operations:
-- Enable standard auditing for table operations
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE BY donghua;
This captures all SELECT, INSERT, UPDATE, and DELETE operations on tables by user DONGHUA.
Configuring Unified Auditing
Unified auditing uses policy-based configuration for more flexible and centralized audit management:
-- Create unified audit policy
CREATE AUDIT POLICY donghua_table_audit
ACTIONS SELECT, INSERT, UPDATE, DELETE;
-- Apply policy to user
AUDIT POLICY donghua_table_audit BY donghua;
Testing: Both Auditing Mechanisms in Action
Test 1: Operations on Table T1 (Before Unified Policy)
-- Connect as donghua user
CREATE TABLE t1(id INT);
INSERT INTO t1 VALUES(1);
SELECT * FROM t1;
DROP TABLE t1;
Standard Audit Results:
SELECT username, action_name, obj_name, timestamp
FROM dba_audit_trail
WHERE username = 'DONGHUA' AND obj_name = 'T1'
ORDER BY timestamp DESC;
USERNAME ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------------- ----------- ------------
DONGHUA SELECT T1 09-OCT-25
DONGHUA INSERT T1 09-OCT-25
At this point, only standard auditing captured the operations since the unified audit policy was not yet created.
Test 2: Operations on Table T2 (After Unified Policy)
-- Connect as donghua user
CREATE TABLE t2 (id INT);
INSERT INTO t2 VALUES (1);
COMMIT;
SELECT * FROM t2;
DROP TABLE t2;
Unified Audit Results:
SELECT unified_audit_policies, dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE dbusername = 'DONGHUA'
AND event_timestamp_utc > TO_TIMESTAMP('2025-10-01','YYYY-MM-DD')
AND object_name = 'T2'
ORDER BY event_timestamp DESC;
UNIFIED_AUDIT_POLICIES DBUSERNAME ACTION_NAME OBJECT_NAME EVENT_TIMESTAMP
------------------------- ------------- -------------- --------------- ----------------------------------
DONGHUA_TABLE_AUDIT DONGHUA SELECT T2 09-OCT-25 01.37.44.549052000 PM
DONGHUA_TABLE_AUDIT DONGHUA INSERT T2 09-OCT-25 01.37.39.747061000 PM
Standard Audit Results (Same Operations):
SELECT username, action_name, obj_name, timestamp
FROM dba_audit_trail
WHERE username = 'DONGHUA' AND obj_name IN ('T1','T2')
ORDER BY timestamp DESC;
USERNAME ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------------- ----------- ------------
DONGHUA SELECT T2 09-OCT-25
DONGHUA INSERT T2 09-OCT-25
DONGHUA SELECT T1 09-OCT-25
DONGHUA INSERT T1 09-OCT-25
Key Observation: Dual Capture in Mixed Mode
Notice that operations on T2 appear in both audit trails:
- Unified Audit Trail: Captured by the
DONGHUA_TABLE_AUDITpolicy - Standard Audit Trail: Captured by the traditional
AUDITstatement
This demonstrates mixed mode operation where both mechanisms work simultaneously, ensuring no audit gaps during migration.
Unified Auditing Performance: Partitioning and Partition Pruning
One of the key advantages of unified auditing is its use of range partitioning on the EVENT_TIMESTAMP column, enabling efficient data management and query performance.
Partition Structure
The AUDSYS.AUD$UNIFIED table is partitioned by range on EVENT_TIMESTAMP with monthly intervals:
PARTITION BY RANGE ("EVENT_TIMESTAMP") INTERVAL (INTERVAL '1' MONTH)
This means:
- Each month's audit data is stored in a separate partition
- Old partitions can be easily archived or dropped
- Queries with time-based predicates benefit from partition pruning
The column EVENT_TIMESTAMP_UTC in view AUDSYS.UNIFIED_AUDIT_TRAIL , it maps to column EVENT_TIMESTAMP in table AUDSYS.AUD$UNIFIED.
Demonstrating Partition Pruning
Let's examine the execution plan for a time-filtered query:
EXPLAIN PLAN FOR
SELECT unified_audit_policies, dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE dbusername = 'DONGHUA'
AND event_timestamp_utc > TO_TIMESTAMP('2025-10-01','YYYY-MM-DD')
AND object_name IN ('T1','T2')
ORDER BY event_timestamp DESC;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Key Execution Plan Details:
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
...
| 17 | PARTITION RANGE ITERATOR| | 1 | 82 | 45551 (1)| 00:00:02 | 137 |1048575|
|* 18 | TABLE ACCESS FULL | AUD$UNIFIED | 1 | 82 | 45551 (1)| 00:00:02 | 137 |1048575|
...
Predicate Information (identified by operation id):
---------------------------------------------------
18 - filter("USERID"='DONGHUA' AND ("OBJ_NAME"='T1' OR "OBJ_NAME"='T2') AND "EVENT_TIMESTAMP">TIMESTAMP'
2025-10-01 00:00:00.000000000')
Partition Pruning in Action:
- Operation:
PARTITION RANGE ITERATOR(Id 17) - Pstart: 137 - Starting partition number
- Pstop: 1048575 - Maximum partition number (but only relevant partitions are scanned)
The optimizer uses the event_timestamp_utc > TO_TIMESTAMP('2025-10-01','YYYY-MM-DD') predicate to:
- Identify which partitions contain data from October 2025 onwards
- Skip all partitions before October 2025
- Only scan relevant monthly partitions (starting from partition 137)
This dramatically reduces I/O and improves query performance, especially in databases with years of audit history.
Performance Impact: Constant vs. Dynamic Predicates
The effectiveness of partition pruning depends heavily on whether the optimizer can determine partition boundaries at parse time. Let's compare two queries:
Query 1: Using Constant Timestamp (Optimal)
SET TIMING ON;
SELECT unified_audit_policies, dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE dbusername = 'DONGHUA'
AND event_timestamp_utc > TO_TIMESTAMP('2025-10-01','YYYY-MM-DD')
AND object_name IN ('T1','T2')
ORDER BY event_timestamp DESC;
Elapsed: 00:00:00.293
Query 2: Using Dynamic Expression (Suboptimal)
SELECT unified_audit_policies, dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE dbusername = 'DONGHUA'
AND event_timestamp_utc > SYSTIMESTAMP - 1
AND object_name IN ('T1','T2')
ORDER BY event_timestamp DESC;
Elapsed: 00:00:09.181
Performance Difference: 31x slower (9.181s vs 0.293s)
Execution Plan Comparison
Constant Predicate Plan:
| Id | Operation | Pstart| Pstop | Cost |
------------------------------------------------------------------
| 17 | PARTITION RANGE ITERATOR | 137 |1048575| 45551 |
| 18 | TABLE ACCESS FULL (AUD$UNIFIED) | 137 |1048575| 45551 |
- Pstart: 137 - Specific starting partition identified at parse time
- Cost: 45,551 - Lower cost due to fewer partitions scanned
Dynamic Predicate Plan:
| Id | Operation | Pstart| Pstop | Cost |
------------------------------------------------------------------
| 17 | PARTITION RANGE ITERATOR | KEY |1048575| 492K |
| 18 | TABLE ACCESS FULL (AUD$UNIFIED) | KEY |1048575| 492K |
- Pstart: KEY - Partition determined at runtime (cannot prune at parse time)
- Cost: 492,000 - 10.8x higher cost estimate
- Must evaluate
SYSTIMESTAMP - 1for each execution
Key Takeaway
For optimal partition pruning performance:
- ✅ Use constant values:
TO_TIMESTAMP('2025-10-01','YYYY-MM-DD') - ❌ Avoid dynamic expressions:
SYSTIMESTAMP - 1,SYSDATE - 7
When the optimizer sees a constant, it can:
- Determine exact partition boundaries at parse time
- Generate a more efficient execution plan
- Skip irrelevant partitions entirely
When using dynamic expressions, the optimizer must:
- Defer partition determination to runtime
- Potentially scan more partitions
- Re-evaluate the expression for each query execution
Conclusion
Oracle's mixed mode auditing provides a seamless transition path from standard to unified auditing. Both mechanisms can coexist, ensuring comprehensive audit coverage. The unified auditing framework's partitioned architecture delivers significant performance advantages through partition pruning, making it the recommended approach for modern Oracle databases.
When querying audit data with time-based filters using event_timestamp_utc, the optimizer automatically leverages partition pruning to scan only relevant monthly partitions, resulting in faster queries and reduced resource consumption—a critical feature for databases with extensive audit histories.
