Mein SYSAUX-Tablespace wächst und wächst, was soll (kann) ich tun ..

6. April 2021 0 Von Markus Flechtner

Nachdem bis Oracle 9i mehr und mehr Oracle-Komponenten ihre Tabellen im SYSTEM-Tablespace abgelegt haben und dort für einen hohen Platzbedarf gesorgt haben, hat Oracle mit der Version 10g den SYSAUX-Tablespace eingeführt und alles das, was nicht Data Dictionary ist, vom SYSTEM-Tablespace nach SYSAUX verschoben. Jetzt bevölkern immer mehr Datenbank-Komponenten den SYSAUX-Tablespace und der wächst und wächst und wächst …

Wer oder was speichert Daten im SYSAUX-Tablespace?

Bei der Analyse des SYSAUX-Tablespace hilft die V$-View V$SYSAUX_OCCUPANTs:

SQL> desc v$sysaux_occupants

 Name                            Null?  Type
 ------------------------------------- ----------------------------
 OCCUPANT_NAME                         VARCHAR2(64)
 OCCUPANT_DESC                         VARCHAR2(64)
 SCHEMA_NAME                           VARCHAR2(64)
 MOVE_PROCEDURE                        VARCHAR2(64)
 MOVE_PROCEDURE_DESC                  VARCHAR2(64)
 SPACE_USAGE_KBYTES                   NUMBER
 CON_ID                                NUMBER

SQL> column OCCUPANT_NAME format a25
SQL> column OCCUPANT_DESC format a53
SQL> select OCCUPANT_NAME,OCCUPANT_DESC from v$sysaux_occupants;

OCCUPANT_NAME          OCCUPANT_DESC
--------------------- -----------------------------------------------------
LOGMNR                 LogMiner
LOGSTDBY              Logical Standby
SMON_SCN_TIME          Transaction Layer - SCN to TIME mapping
AUDSYS                 AUDSYS schema objects
PL/SCOPE              PL/SQL Identifier Collection
STREAMS                Oracle Streams
AUDIT_TABLES           DB audit tables
XDB                   XDB
XSAMD                 OLAP Catalog
AO                     Analytical Workspace Object Table
XSOQHIST               OLAP API History Tables
SM/AWR                Server Manageability - Automatic Workload Repository
SM/ADVISOR             Server Manageability - Advisor Framework
SM/OPTSTAT             Server Manageability - Optimizer Statistics History
SM/OTHER               Server Manageability - Other Components
STATSPACK              Statspack Repository
SDO                    Oracle Spatial
WM                     Workspace Manager
ORDIM                  Oracle Multimedia ORDSYS Components
ORDIM/ORDDATA          Oracle Multimedia ORDDATA Components
ORDIM/ORDPLUGINS      Oracle Multimedia ORDPLUGINS Components
ORDIM/SI_INFORMTN_SCHEMA  Oracle Multimedia SI_INFORMTN_SCHEMA Components
EM                     Enterprise Manager Repository
TEXT                   Oracle Text
ULTRASEARCH            Oracle Ultra Search
ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User
EXPRESSION_FILTER     Expression Filter System
EM_MONITORING_USER    Enterprise Manager Monitoring User
TSM                    Oracle Transparent Session Migration User
SQL_MANAGEMENT_BASE   SQL Management Base Schema
AUTO_TASK              Automated Maintenance Tasks
JOB_SCHEDULER          Unified Job Scheduler

32 rows selected.

 

Welche Komponenten benötigen im SYSAUX-Tablespace am meisten Platz?

SQL> set linesize 100

SQL> column occupant_name format a25
SQL> column occupant_desc format a53

SQL> select OCCUPANT_NAME,OCCUPANT_DESC, SPACE_USAGE_KBYTES USED_KB
  2  from V$SYSAUX_OCCUPANTS where SPACE_USAGE_KBYTES>0
  3  order by SPACE_USAGE_KBYTES desc;


OCCUPANT_NAME          OCCUPANT_DESC                                           USED_KB
--------------------- ---------------------------------------------------- --------
SDO                    Oracle Spatial                                            208768
SM/OTHER               Server Manageability - Other Components                 166912
SM/AWR                 Server Manageability - Automatic Workload Repositor     125888
XDB                    XDB                                                         70208
SM/OPTSTAT             Server Manageability - Optimizer Statistics History      58560
SM/ADVISOR             Server Manageability - Advisor Framework                 48384
AO                     Analytical Workspace Object Table                         42432
ORDIM/ORDDATA          Oracle Multimedia ORDDATA Components                      16512
SQL_MANAGEMENT_BASE   SQL Management Base Schema                                15104
LOGMNR                 LogMiner                                                    11072
AUDSYS                 AUDSYS schema objects                                      9664
WM                     Workspace Manager                                           6720
PL/SCOPE               PL/SQL Identifier Collection                               5120
JOB_SCHEDULER          Unified Job Scheduler                                       4224
SMON_SCN_TIME          Transaction Layer - SCN to TIME mapping                   3456
TEXT                   Oracle Text                                                  3008
STREAMS                Oracle Streams                                              1728
LOGSTDBY               Logical Standby                                             1600
AUDIT_TABLES           DB audit tables                                             1152
AUTO_TASK              Automated Maintenance Tasks                                 576
EM_MONITORING_USER    Enterprise Manager Monitoring User                          512
ORDIM                  Oracle Multimedia ORDSYS Components                         448

22 rows selected.

Diese Belegung in meiner Test-Datenbank (19c, genauer: 19.10) ist sicherlich nicht typisch. Meiner Erfahrung nach benötigen die folgenden Komponenten meist recht viel Platz:

  • SM/OPTSTAT
  • SM/ADVISOR
  • SM/AWR
  • SM/OTHER
  • SQL_MANAGEMENT_BASE

Wenn man Auditing nutzt, dann ist der Platzbedarf für AUDSYS und AUDSYS_TABLES meist auch signifikant.

Einzelheiten zu den AWR-Daten im Tablespace SYSAUX liefert das Skript $ORACLE_HOME/rdbms/admin/awrinfo.sql.

Taucht in einer 12c Datenbank (oder höher) noch die Komponente “EM” (Enterprise Management Repository) auf, dann kann man diese Daten löschen. Das Repository wurde bis Oracle 11g vom “Enterprise Manager Database Control benötigt”. Der “Enterprise Manager Database Express” als Nachfolger braucht kein Repository. Wenn also in einer 12c-Datenbank noch die Komponente “EM” vorhanden ist, dann wurde aller Wahrscheinlichkeit nach das Repository beim Upgrade nicht gelöscht. Das Skript zum Löschen eines nicht mehr benötigten EM-Repositories ist $ORACLE_HOME/rdbms/admin/emremove.sql.

 

Wie kann man im SYSAUX-Tablespace aufräumen?

Für einige Komponenten liefert Oracle Prozeduren, mit denen man die jeweiligen Daten in einen anderen Tablespace verschieben kann:

Das löst natürlich nicht das Platzproblem, denn der insgesamt benötigte Platz bleibt natürlich gleich, entlastet aber den SYSAUX-Tablespace.

SQL>  select OCCUPANT_NAME,MOVE_PROCEDURE from V$SYSAUX_OCCUPANTS where move_procedure is not null;

OCCUPANT_NAME          MOVE_PROCEDURE
-------------------- --------------------------------------------------------------
LOGMNR                 SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY               SYS.DBMS_LOGSTDBY.SET_TABLESPACE
AUDSYS                 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
AUDIT_TABLES           DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
XDB                    XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
XSAMD                  DBMS_AMD.Move_OLAP_Catalog
AO                     DBMS_AW.MOVE_AWMETA
XSOQHIST               DBMS_XSOQ.OlapiMoveProc
SDO                    MDSYS.MOVE_SDO
WM                     DBMS_WM.move_proc
ORDIM                  ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA          ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS      ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA  ordsys.ord_admin.move_ordim_tblspc
EM                     emd_maintenance.move_em_tblspc
TEXT                   DRI_MOVE_CTXSYS
ULTRASEARCH            MOVE_WK
ULTRASEARCH_DEMO_USER  MOVE_WK

18 rows selected.

Generell hilft beim Aufräumen meist auch eine Reorganisation der großen Tabellen und Indizes, d.h.

ALTER TABLE .. MOVE TABLESPACE SYSAUX ONLINE;
ALTER INDEX .. REBUILD TABLESPACE SYSAUX ONLINE;

 

Wenn allerdings auch noch alte Daten gelöscht werden sollen (siehe unten) dann sollte man diese Reorganisation erst nach dem Löschen der Daten durchführen.

Achtung: Bei der Reorganisation der Tabellen mittels “ALTER TABLE MOVE” werden die zugehörigen Indizes “UNUSABLE” und müssen neu aufgebaut werden.

Weitere Informationen zu diesem Vorgehen finden sich z.B. in der Support-Note 1563921.1.

 

Wie kann der Platzbedarf der anderen Komponenten reduziert werden?

SM/OPTSTAT:

Löschen alter Daten (hier z.B.: älter als 28 Tage)

exec DBMS_STATS.PURGE_STATS(SYSDATE-29);

SM/ADVISOR:

Bei den Advisor sollte man im ersten Schritt nachschauen, welcher Advisor am meisten Platz benötigt. Dabei hilft im ersten Schritt der Blick auf die Anzahl der Ausführungen:

SQL> column task_name format a40

SQL> select task_name,count(*) from dba_advisor_objects group by task_name;

TASK_NAME                                 COUNT(*)
---------------------------------------- ----------
SYS_AUTO_SPM_EVOLVE_TASK                          1
AUTO_STATS_ADVISOR_TASK                           1
SYS_AUTO_SQL_TUNING_TASK                          1
SYS_AI_SPM_EVOLVE_TASK                             1
INDIVIDUAL_STATS_ADVISOR_TASK                     1

Wenn man einen Advisor deaktiviert, werden auch die zugehörigen Daten gelöscht und man räumt somit auch auf. Wenn man danach den Advisor wieder aktiviert, dann startet man wieder neu (allerdings natürlich auch ohne die historischen Daten).

REM Deaktivieren des Statistic Advisors
exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

REM Deaktivieren des Tuning Advisors
DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);

REM Deaktivieren des Space Advisors
DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql space advisor', operation => NULL, window_name => NULL);

 

Advisor, die man nicht nutzt, kann man natürlich ausschalten und deaktiviert lassen. (Beim Statistics Advisor kommt dann allerdings die Meldung ” ORA-12012: error on auto execute of job  ..ORA-20001: Statistics Advisor: Invalid task name for the current user” im alert.log. Diese Meldung kann ignoriert warden.

 

Daten des Statistics Advisors können auch manuell aufgeräumt werden:

exec prvt_advisor.delete_expired_tasks;

SM/AWR:

Die Menge der AWR-Daten kann man nur durch Verkürzung der Aufbewahrungsfrist mit Hilfe der Prozedur DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS verringern (siehe unten). Ältere Daten werden dann automatisch gelöscht.

Die aktuellen Einstellungen (Aufbewahrungsfrist), im Beispiel 8 Tage, kann man mit der folgenden Abfrage ermitteln:

SQL> SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

SQL_MANAGEMENT_BASE:

Die SQL Management Base (SQL Plan Management) kann beeinflusst werden, in dem man

  1. Die Aufbewahrungsfrist (Parameter PLAN_RETENTION_WEEKS, Default 53 Wochen) und/oder
  2. Den prozentual erlaubten Anteil im SYSAUX-Tablespace anpassen (Parameter, SPACE_BUDGET_PERCENT, Default 10%)
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE from DBA_SQL_MANAGEMENT_CONFIG;

PARAMETER_NAME                            PARAMETER_VALUE
---------------------------------------- ------------------------------
[..]
PLAN_RETENTION_WEEKS                      53
SPACE_BUDGET_PERCENT                      10

 

Weiterhin können nicht genutzte SQL-Plan-Baselines gelöscht werden (Informationen hierzu siehe SQL Tuning Guide, Kapitel 28 “Managing SQL Plan Baselines”).

Ergänzend zu diesen Maßnahmen hilft auch die Verkürzung der Aufbewahrungsfristen (siehe nächster Abschnitt) dabei, den Platzbedarf des SYSAUX-Tablespaces gering zu halten.

 

Wie kann man dem Wachstum des SYSAUX-Tablespaces vorbeugen?

Standardmäßig hat Oracle meist recht großzügig bemessene Aufbewahrungsfristen für die Daten im SYSAUX-Tablespace festgelegt. Frühzeitige Verkürzung dieser Fristen (am besten direkt nach dem Anlegen einer Datenbank) verhindert ein überdimensionales Wachstum des SYSAUX-Tablespaces und macht Aufräumaktionen wie sie im vorherigen Abschnitt beschrieben sind oft unnötig:

REM SM/OPTSTAT
REM Aufbewahrungsfrist in Tagen (Default: 31 Tage)
exec dbms_stats.alter_stats_history_retention(7);

REM SM/ADVISOR
REM Aufbewahrungsfrist fuer den Statistics Advisor anpassen (Default: 30 Tage)
EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => 14);

REM SM/AWR
REM Achtung: hier wird die Aufbewahrungsfrist in Minuten angegeben
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>8*1440);

REM SQL_MANAGEMENT_BASE
REM Angabe in Wochen
Exec DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS',26);

 

Für die SQL Management Base kann man auch den erlaubten prozentuellen Platz im SYSAUX-Tablespace ändern (in diesem Fall ist ein AUTOEXTEND für den SYSAUX-Tablespace aber kontraproduktiv)

Exec DBMS_SPM.Configure(' SPACE_BUDGET_PERCENT',5);

 

Wie ist das in Container-Datenbanken?

Das oben Geschriebene gilt gleichermaßen für CDBs und Non-CDBs. Da es aber in Container-Datenbanken auf beiden Ebenen (CDB$ROOT bzw. PDBs) einen SYSAUX-Tablespace gibt, sind – grob gesagt – die Schritte sowohl im Root-Container als in den PDBs durchzuführen.

 

Fazit

Von Version zu Version wird der SYSAUX-Tablespace der Datenbank immer wichtiger und wird von immer mehr Komponenten genutzt. Daher verdient er auch Beobachtung und entsprechende Pflege um Platzproblemen vorzubeugen. Die hier dargestellten Schritte helfen hoffentlich, den Einstieg in dieses Thema zu erleichtern. Weiterführende Informationen gibt es in den zahlreichen Support-Notes zu diesem Thema.

 

V$Views

Oracle-Dokumentation:

MOS-Notes

  • SYSAUX New Mandatory Tablespace in Oracle 10g and higher (Doc ID 243246.1)
  • Troubleshooting Issues with SYSAUX Space Usage (Doc ID 1399365.1)
  • SRDC – How to Collect Standard Information for an Issue where Excessive SYSAUX Space is Used by the Automatic Workload Repository (AWR) (Doc ID 1934108.1)
  • General Guidelines for SYSAUX Space Issues (Doc ID 552880.1)
  • SYSAUX Tablespace Space Issue Because Of dbms_comparison (Doc ID 2089484.1)
  • High Storage Consumption for LOBs in SYSAUX Tablespace (Doc ID 396502.1)
  • SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)
  • Suggestions if your SYSAUX Tablespace grows rapidly or too large [ID 1292724.1]
  • Abnormal High Space Usage in Sysaux Tablespace – Unable to Purge [ID 1360000.1]
  • Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER (Doc ID 329984.1)
  • SM/ADVISOR “SM/ADVISOR” Taking Most of Space (Doc ID 2692726.1)
  • How to change the SM/OPTSTAT automatic purge time? (Doc ID 557582.1)
  • How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)
  • Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace (Doc ID 1499542.1)
  • How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

Blog-Posts etc.

  • Excessive growth in SYSAUX tablespace: https://thehelpfuldba.com/index.php/2018/09/18/excessive-growth-in-sysaux-tablespace/
  • Two Occupants That Affect The Most SYSAUX Tablespace Size: http://dbaparadise.com/2020/07/two-occupants-that-affect-the-most-sysaux-tablespace-size/
  • Oracle SYSAUX size is exponentially expanding after upgrade to Oracle 12cR2: https://geodatamaster.com/2020/04/06/oracle-sysaux-size-is-exponentially-expanding-after-upgrade-to-oracle-12cr2/
  • How to clean up SYSAUX manually: https://jonujoy.wordpress.com/2014/04/04/how-to-clean-up-sysaux-manually/

 

Amazon-Partner-Link: