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
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.