Mein SYSAUX-Tablespace wächst und wächst, was soll (kann) ich tun ..
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
- Die Aufbewahrungsfrist (Parameter PLAN_RETENTION_WEEKS, Default 53 Wochen) und/oder
- 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
- v$sysaux_occupants (Dokumentation)
Oracle-Dokumentation:
- Oracle Database 19c Administrators Guide – Section 12 Managing the SYSAUX Tablespace
- Oracle Database 19c SQL Tuning Guide – Kapitel 28 Managing SQL Plan Baselines
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:
