Hi
select * from UNIFIED_AUDIT_TRAIL where event_timestamp >=
to_date('20200827 1510','yyyymmdd hh24mi') order by event_timestamp descseems to take quite a lot of time.
UNIFIED_AUDIT_TRAIL view has EVENT_TIMESTAMP as timestamp with local timezone.
This view is based on audsys.aud$unified table which has column EVENT_TIMESTAMP as timestamp
and it partitioned by this column.
In view UNIFIED_AUDIT_TRAIL there is CAST (event_timestamp AS TIMESTAMP WITH LOCAL TIME ZONE)
Now execution plan shows:
9 - access("AUDIT_OPTION"="AOM"."OPTION#")
14 - access("SPX"."PRIVILEGE"=(-"SYSTEM_PRIVILEGE"))
19 - access("ACTION"="ACT"."ACTION" AND "AUDIT_TYPE"="ACT"."TYPE")
24 - access(:Z>=:Z AND :Z<=:Z)
28 - filter(CAST(INTERNAL_FUNCTION("EVENT_TIMESTAMP") AS timestamp with local time zone)>=TIMESTAMP' 2020-08-27 15:10:00')
29 - access("AUDUNI"."AUDIT_OPTION"="AOM1"."OPTION#")
34 - access("SPX1"."PRIVILEGE"=(-"AUDUNI"."SYSTEM_PRIVILEGE"))
39 - access("AUDUNI"."ACTION"="ACT1"."ACTION" AND "AUDUNI"."AUDIT_TYPE"="ACT1"."TYPE")
42 - access(:Z>=:Z AND :Z<=:Z)
filter(CAST(FROM_TZ(INTERNAL_FUNCTION("EVENT_TIMESTAMP"),'00:00') AT LOCAL AS timestamp)>=TIMESTAMP' 2020-08-27 15:10:00')and no partition pruning is occuring.
How can I fetch rows efficiently?
lh
There are some bugs related to the performance of querying this view. Check note 2063340.1 to see if any apply to you.
If not, are you able to purge the audit to reduce the number of rows this processes?
UPDATE 6 Dec 2023From 19c, you can search using the event_timestamp_utc column on this view. This enables the database to use partition pruning on this table. e.g.
select * from UNIFIED_AUDIT_TRAIL where event_timestamp_utc >= to_date ( ... )
HT to Vince Rackliffe in the comments.