Skip to Main Content
  • Questions
  • Good Way to Capture Records of Previous Month from UNIFIED_AUDIT_TRAIL

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sue.

Asked: March 13, 2025 - 7:17 pm UTC

Last updated: March 14, 2025 - 1:49 pm UTC

Version: 19.22

Viewed 1000+ times

You Asked

v19.22


Requirement is to run some queries against the audit table at the beginning of the month to capture details about the previous calendar month.

EVENT_TIMESTAMP in UNIFIED_AUDIT_TRAIL is timestamp(6) (not date)

Is this an appropriate where clause to query this view? Or is there a better way when dealing with timestamp vs date?

       /* first day of last month at 00:00:00 */
 WHERE CAST( EVENT_TIMESTAMP AS DATE ) >= TRUNC( ADD_MONTHS( SYSDATE, -1 ), 'MM' )
       /* first day of current month at 00:00:00 */
  AND CAST( EVENT_TIMESTAMP AS DATE ) <  TRUNC( SYSDATE, 'MM' )



Thanks

and Chris said...

As a rule, applying no functions to columns in the where clause is better. This improves the optimizer's ability to use indexes, prune partitions, use other optimization techniques.

Casting event_timestamp to a date leads to this query scanning all the partitions of AUD$UNIFIED. Here's a trimmed plan showing the effect. Note the PARTITION RANGE ALL operation and all the function calls in the predicate information at the end:

set feed only
SELECT * FROM unified_audit_trail
WHERE CAST( EVENT_TIMESTAMP AS DATE ) >= TRUNC( ADD_MONTHS( SYSDATE, -1 ), 'MM' )
  AND CAST( EVENT_TIMESTAMP AS DATE ) <  TRUNC( SYSDATE, 'MM' );
set feed on

select * from dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE +PARTITION');

Plan hash value: 1534252265
 
--------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Pstart| Pstop |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |       |       |
|   1 |  VIEW                      | UNIFIED_AUDIT_TRAIL       |       |       |
|   2 |   UNION-ALL                |                           |       |       |
...
|  18 |        PARTITION RANGE ALL |                           |     1 |1048575|
|* 19 |         TABLE ACCESS FULL  | AUD$UNIFIED               |     1 |1048575|
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  ...
  19 - filter((CAST(CAST(FROM_TZ(INTERNAL_FUNCTION("EVENT_TIMESTAMP"),'0
              0:00') AT LOCAL AS timestamp) AS DATE )<TRUNC(SYSDATE@!,'fmmm') AND 
              CAST(CAST(FROM_TZ(INTERNAL_FUNCTION("EVENT_TIMESTAMP"),'00:00') AT 
              LOCAL AS timestamp) AS DATE )>=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm'))
              )


So rather than casting event_timestmap to a date, how about casting the other side to a timestamp?

set feed only
SELECT * FROM unified_audit_trail
WHERE EVENT_TIMESTAMP  >= CAST ( TRUNC( ADD_MONTHS( SYSDATE, -1 ), 'MM' ) AS TIMESTAMP )
  AND EVENT_TIMESTAMP  <  CAST ( TRUNC( SYSDATE, 'MM' ) AS TIMESTAMP );
set feed on

select * from dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE +PARTITION');

Plan hash value: 3117522405
 
---------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Pstart| Pstop |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |       |       |
|*  1 |  FILTER                     |                           |       |       |
|   2 |   VIEW                      | UNIFIED_AUDIT_TRAIL       |       |       |
|   3 |    UNION-ALL                |                           |       |       |
...
|  19 |         PARTITION RANGE ALL |                           |     1 |1048575|
|* 20 |          TABLE ACCESS FULL  | AUD$UNIFIED               |     1 |1048575|
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  ...
  20 - filter((CAST(FROM_TZ(INTERNAL_FUNCTION("EVENT_TIMESTAMP"),'00:00')
               AT LOCAL AS timestamp)<CAST(TRUNC(SYSDATE@!,'fmmm') AS TIMESTAMP ) AND 
              CAST(FROM_TZ(INTERNAL_FUNCTION("EVENT_TIMESTAMP"),'00:00') AT LOCAL AS 
              timestamp)>=CAST(TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm') AS TIMESTAMP 
              )))


Hmm, that still scans all partitions and applies several functions to event_timestamp. Some of these relate to time zone manipulation (FROM_TZ & AT LOCAL). Fortunately from 19c unified_audit_trail has an event_timestamp_utc column which should take care of this (assuming sysdate is in UTC).

Let's try using that:

set feed only
SELECT * FROM unified_audit_trail
WHERE EVENT_TIMESTAMP_UTC  >= CAST ( TRUNC( ADD_MONTHS( SYSDATE, -1 ), 'MM' ) AS TIMESTAMP )
  AND EVENT_TIMESTAMP_UTC  <  CAST ( TRUNC( SYSDATE, 'MM' ) AS TIMESTAMP );
set feed on

select * from dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE +PARTITION');

Plan hash value: 3236024292
 
-------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |       |       |
|*  1 |  FILTER                         |                           |       |       |
|   2 |   VIEW                          | UNIFIED_AUDIT_TRAIL       |       |       |
|   3 |    UNION-ALL                    |                           |       |       |
...
|  16 |         PARTITION RANGE ITERATOR|                           |   KEY |   KEY |
|* 17 |          TABLE ACCESS FULL      | AUD$UNIFIED               |   KEY |   KEY |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  ...
  17 - filter(("EVENT_TIMESTAMP">=CAST(TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm
              ') AS TIMESTAMP ) AND "EVENT_TIMESTAMP"<CAST(TRUNC(SYSDATE@!,'fmmm') AS 
              TIMESTAMP )))


Aha! AUD$UNIFIED is accessed using PARTITION RANGE ITERATOR and there's no functions applied to it in the predicates. So this looks like the best approach.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.