What is a one sentence summary of your feature request?
Oracle Auditor Watermark Query
Please describe your idea in detail. What is your problem, why do you feel this idea is the best solution, etc.
During each Oracle Auditor collection cycle, the Collector needs to determine the latest available event timestamp (watermark) before proceeding with the collection of new audit events.
Currently, the following query is used against AUDSYS.AUD$UNIFIED:
SELECT * FROM ( SELECT EVENT_TIMESTAMP AS EXTENDED_TIMESTAMP FROM AUDSYS.AUD$UNIFIED ORDER BY EXTENDED_TIMESTAMP DESC ) WHERE ROWNUM = 1
This query sorts the audit records by EVENT_TIMESTAMP in descending order and then retrieves the latest timestamp.
How do you currently solve the challenges you have by not having this feature?
R&D suggested replacing the current query with an aggregate MAX() query:
SELECT MAX(EXTENDED_TIMESTAMP) AS EXTENDED_TIMESTAMP FROM ( SELECT EVENT_TIMESTAMP AS EXTENDED_TIMESTAMP FROM AUDSYS.AUD$UNIFIED )
Alternatively, the query could potentially be simplified to:
SELECT MAX(EVENT_TIMESTAMP) AS EXTENDED_TIMESTAMP FROM AUDSYS.AUD$UNIFIED
Reason for Request
The watermark lookup is performed periodically as part of each collection cycle. In environments containing a large number of records in AUDSYS.AUD$UNIFIED, sorting the dataset solely to identify the latest timestamp may introduce unnecessary database overhead.
Since only the maximum EVENT_TIMESTAMP value is required, using MAX() should provide the same functional result without requiring an explicit descending sort.
Expected Improvement
Optimize the Oracle Auditor watermark lookup to:
• Reduce unnecessary database workload during collection cycles.
• Improve efficiency when working with large Unified Audit datasets.
• Avoid sorting audit records when only the latest timestamp is required.
• Preserve the existing collection logic and return the same watermark value.
• Improve scalability of Oracle auditing in environments with high audit-event volumes.