oracledba.help
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;

Stratagies

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

  1. Make sure to rebuild them in the SAME tablespace they exist in (ex: SYSAUX).
  2. Use the ONLINE option to avoid I\O issues.
  3. 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).