SpecialTopics

SYSAUX, Optimizing

Overview

Growth and performance of SYSAUX can get out of control. What follows are simple methods you can use to optimize it. I detailed each phase so as you can easily write a script to expedite for your environment. You naturally want to do this during off hours.

Who Is using the Most Space?

COL space_usage_kbytes FORMAT 999,999,999

SELECT occupant_name,occupant_desc,space_usage_kbytes 
FROM  v$sysaux_occupants
ORDER BY space_usage_kbytes DESC ;

SELECT occupant_name, occupant_desc, space_usage_kbytes 
FROM v$sysaux_occupants 
ORDER BY occupant_name;

In addition to these, this bug may affect SYSAUX erroneous growth (27489107).

Resize Examples

 --BFTS
 ALTER TABLESPACE sysaux RESIZE 10g;
 --SFTS Using OMF
 ALTER DATABASE DATAFILE '+DATA/TEST/sysaux01.dbf' RESIZE 10g;

AUDSYS

Complete Clean of the Unified Audit Trail

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => FALSE,
CONTAINER => dbms_audit_mgmt.container_current);
END;
/

Flush Unified Audit Trail from Memory

exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

Disable the Unified Audit Policies

 NOAUDIT POLICY ORA_SECURECONFIG;
 noaudit policy ORA_LOGON_FAILURES;

These are enabled by default.

To Re-enable Them

 AUDIT POLICY ORA_SECURECONFIG;
 audit policy ORA_LOGON_FAILURES;

SM/ADVISOR

Per SM/ADVISOR "SM/ADVISOR" Taking Most of Space (Doc ID 2692726.1), drop and recreate it.

-- Usage SM/ADVISOR

Column OCCUPANT_NAME format a15
SELECT occupant_name, occupant_desc, space_usage_kbytes
FROM v$sysaux_occupants
WHERE occupant_name LIKE 'SM/ADVISOR%';

COL TASK_NAME FORMAT A35
SELECT TASK_NAME, COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS 
GROUP BY TASK_NAME ORDER BY CNT DESC;

-- Drop

-- Get TASK_NAME
COL TASK_NAME FORMAT A35
SELECT TASK_NAME, COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS 
GROUP BY TASK_NAME ORDER BY CNT DESC;

 TASK_NAME                                  CNT
 ----------------------------------- ----------
 AUTO_STATS_ADVISOR_TASK_1                 4794

-- Set v_tname [TASK_NAME] and Drop
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK_1';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

-- CREATE_ADVISOR_TASK

-- Set v_tname [TASK_NAME] and Create
DECLARE
v_ret   VARCHAR2(32767);
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK_1';
v_ret   := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
END;
/

• Optionally create using: EXEC DBMS_STATS.INIT_PACKAGE();

-- Then EXECUTE_ADVISOR_TASK

DECLARE
v_tname VARCHAR2(32767);
v_ret   VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK_1';
v_ret   := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/

-- Confirm Recreated

COL TASK_NAME FORMAT A35
SELECT TASK_NAME, COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS 
GROUP BY TASK_NAME ORDER BY CNT DESC;

 TASK_NAME                                  CNT
 ----------------------------------- ----------
 AUTO_STATS_ADVISOR_TASK_1                 4794

WMSYS

Purge Old AWR Data

Temporarily Set Retention to 3 Days

Default is 7-8 days depending on your Oracle version (10,080 minutes).
Reset it to 3 days (4320 minutes).

 exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size => 3);
 exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings (interval => 60,retention => 4320);

1 day would be 1440 minutes.

Ensure Retention Has Been Changed

 SELECT retention FROM dba_hist_wr_control;

 +00003 00:00:00.0

Display Old AWR snap_id Numbers

 SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT 
 WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)  
 UNION 
 SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT 
 WHERE snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT);

 1001 24-JUL-21 10.00.14.449 AM  24-AUG-21 11.00.19.715 AM
 ...
 2001 24-AUG-21 01.00.30.429 PM  24-AUG-21 02.00.35.498 PM

Purge Old AWRs via snap_id Numbers
Replace 1001 and 2001 with first and last values found from the above.

 BEGIN
 dbms_workload_repository.drop_snapshot_range(low_snap_id =>1001,high_snap_id=>2001);
 END;
  /

Ensure AWRs Have Been Purged

 SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT 
 WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)  
 UNION 
 SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT 
 WHERE snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT);

 no rows selected

Reset Retention

 exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings (interval => 60,retention => 10080);
 exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size => 7);
 SELECT retention FROM dba_hist_wr_control;

Notice order of first two commands inverted from original change.

General

Delete Orphaned_ASH_Rows

Check for orphaned ASH rows:

SELECT COUNT(1) Orphaned_ASH_Rows
FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );

If the value is larger than zero then delete the orphan rows.

DELETE
FROM wrh$_active_session_history a
WHERE NOT EXISTS
  (SELECT 1
  FROM wrm$_snapshot
  WHERE snap_id       = a.snap_id
  AND dbid            = a.dbid
  AND instance_number = a.instance_number
  );

COMMIT;

Compact Segments

Get List of Schema in SYSAUX

  SELECT username FROM dba_users WHERE default_tablespace='SYSAUX' ORDER BY username;

  ANONYMOUS
  APPQOSSYS
  ...
  WMSYS
  XDB

WMSYS tends to be the biggest culprit.

Get List of All Tables in Each Schema

  SELECT owner || '.' || table_name FROM dba_tables
  WHERE owner='WMSYS' AND iot_type IS NULL
  ORDER BY table_name;

  WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_L
  WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S
  WMSYS.WM$BATCH_COMPRESSIBLE_TABLES$
  ...

COMPACT Each Table

 ALTER TABLE WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_L ENABLE ROW MOVEMENT;
 ALTER TABLE WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_L SHRINK SPACE CASCADE;
 ALTER TABLE WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_L SHRINK SPACE;

This method locks given table for the briefest possible time.
Perform for each table.

Rebuild Indexes

Get List of All Releveant Indexes

 SELECT trim(tablespace_name) || '.' || trim(index_name) FROM dba_indexes 
 WHERE owner='WMSYS' 
 AND   index_type='NORMAL' 
 AND   index_name NOT LIKE 'SYS%' 
 ORDER BY index_name;

 SYSAUX.WM$BCT_IDX
 SYSAUX.WM$CONS_COLUMNS_IDX
 SYSAUX.WM$ENV_SYS_PK
 ...

Rebuild Each Index

Make sure to rebuild them in the SAME tablespace they exist in (ex: SYSAUX).
Use the ONLINE option to avoid I\O issues.
PARALLEL n can be used to speed up the process.

 ALTER INDEX SYSAUX.WM$BCT_IDX REBUILD PARALLEL 10 ONLINE NOLOGGING TABLESPACE SYSAUX;

Sete AUTOEXTEND OFF

Consider setting AUTOEXTEND OFF on the SYSAUX tablespace after resizing it
approporiatly for your environment.

 ALTER TABESPACE sysaux RESIZE 5g;  
 ALTER TABLESPACE SYSAUX AUTOEXTEND OFF;

This will ensure the tablespace does not grow larger than your allocated disk space.

Reference

Tips if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1).
Doc ID 790039.1 How to Drop Plans from the SQL Plan Management (SPM) Repositry.
Doc ID 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER.
Doc ID 287679.1 Space Management In Sysaux Tablespace with AWR in Use.
Fixing a Corrupted SYSAUX Tablespace (Doc ID 950128.1).