Unified Auditing Purge Takes Too Long Time By DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL Using last_arch_timestamp (Doc ID 2573372.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Unified Auditing purge took almost 3 days to purge 10GB audit log by DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL using last_arch_timestamp.
  The awrinfo report shows that the old partition of AUDSYS.AUD$UNIFIED is larger than 10GB.
  
  COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE     
  --------- --------- --------------------------------------------------------------------- ---------------  
  NON_AWR    13,952.0 AUDSYS.AUD$UNIFIED.SYS_P3345                                          TABLE PARTITION  
  NON_AWR    12,288.0 AUDSYS.AUD$UNIFIED.SYS_P3361                                          TABLE PARTITION  
• AWR report shows that DELETE against AUDSYS.AUD$UNIFIED took a long time at DB CPU and I/O.
  
          Elapsed                  Elapsed Time
          Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
  ---------------- -------------- ------------- ------ ------ ------ -------------
           3,749.8              0           N/A   67.7  100.0     .1 adthxm85aqtbz
  Module: sqlplus@<MACHINE_NAME>(TNS V1-V3)
  delete from audsys.aud$unified where event_timestamp < :1 and (dbid = :2 or dbi
  d = 0)

• 10046 events trace shows that DELETE was processing gradually and no hung found.
  
  
  *** 2019-06-28T16:01:52.702782+09:00
  WAIT #18446744071364822264: nam='db file sequential read' ela= 18 file#=3 block#=476427 blocks=1 obj#=0 tim=26596834272979
  WAIT #18446744071364822264: nam='db file sequential read' ela= 17 file#=3 block#=476428 blocks=1 obj#=0 tim=26596834317638
  WAIT #18446744071364822264: nam='db file sequential read' ela= 17 file#=3 block#=476429 blocks=1 obj#=0 tim=26596834362285
  WAIT #18446744071364822264: nam='db file sequential read' ela= 15 file#=3 block#=476430 blocks=1 obj#=0 tim=26596834406885
  ... ...
  *** 2019-06-28T16:01:53.685430+09:00
  WAIT #18446744071364822264: nam='db file sequential read' ela= 17 file#=3 block#=476449 blocks=1 obj#=0 tim=26596835255635
  WAIT #18446744071364822264: nam='db file sequential read' ela= 18 file#=3 block#=476450 blocks=1 obj#=0 tim=26596835300658
  WAIT #18446744071364822264: nam='db file sequential read' ela= 16 file#=3 block#=476451 blocks=1 obj#=0 tim=26596835345928
  ... ...
  *** 2019-06-28T16:01:54.668904+09:00
  WAIT #18446744071364822264: nam='db file sequential read' ela= 17 file#=3 block#=476471 blocks=1 obj#=0 tim=26596836239111
  WAIT #18446744071364822264: nam='db file sequential read' ela= 15 file#=3 block#=476472 blocks=1 obj#=0 tim=26596836283733
  WAIT #18446744071364822264: nam='db file sequential read' ela= 16 file#=3 block#=476473 blocks=1 obj#=0 tim=26596836328263
  
  
  
• AUDSYS.AUD$UNIFIED uses the default monthly time interval partition, and everyday generates about 20 million new audit log.
  
  
  
  

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.