Neues vom SYSAUX-Tablespace…

Veröffentlicht am von

Der SYSAUX-Tablespace wurde von Oracle in der Version 10g eingeführt. Hier werden diverse sog. „Auxiliary“ Objekte gespeichert, die Oracle-eigenen Schemata zuzuordnen sind. Die Menge der enthaltenen Objekte sowie der Platzbedarf hängen u.a. von der Anzahl der in der Datenbank installierten Komponenten ab. In der Regel sind diese unkritisch – es kann aber vorkommen, dass der Platzbedarf im SYSAUX-Tablespaces plötzlich stark ansteigt, ohne dass etwas neues installiert oder überhaupt irgendetwas getan wurde.

Dies kann eine Reihe von Ursachen haben, ist aber immer ärgerlich – insbesondere, als der SYSAUX-Tablespace doch recht schwierig zu reorganisieren ist. Auch bei Oracle-Support sind die Ursachen leider eher nicht so bekannt, so dass SRs zum Thema langwierig und ergebnislos sein können.

Eine mögliche Ursache, Unified Auditing-Einträge in einer Tabelle im SYSAUD-Schema, wurde bereits vor einiger Zeit hier beschrieben.

Heute stellen wir zwei weitere Kandidaten vor.

WRI$_SQLSET_PLAN_LINES

im RU 19.7 gibt es eine neue Komponente namens „the automatic SQL Tuning Set (ASTS)“. Es handelt sich hierbei um ein System-verwaltetes Repository, das (sehr viele!!) historische Daten über SQL-Performance-Metriken und Ausführungspläne speichert. 

Die zugehörige neue Autotask Auto STS Capture Task ist in allen RU-Levels vor 19.7 (und auch ab 19.8 wieder) standardmäßig deaktiviert, in 19.7 aber standardmäßig aktiv. Dies führt dazu, dass alle 15 Minuten ein Capture für das o.a. SQL-Set namens SYS_AUTO_STS durchgeführt wird und entsprechende Einträge für jedes Statement in der genannten WRI$-Tabelle erfolgen. Dies wird i.A. weder jemals benötigt und vor allem – was noch schlimmer ist – auch niemals aufgeräumt (oder nach einem sinnvollen Intervall von 1 Jahr o.ä. …). Dies passiert auf allen Plattformen und sowohl in EE als auch SE2. Bei einem Kunden konnten wir ca. 3 Monate nach dem Upgrade auf 19.7 einen SYSAUX-Tablespace von stolzen 92 GB verzeichnen.

Die gute Nachricht: man kann die Autotask recht einfach wieder deaktivieren …

BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'Auto STS Capture Task',
    operation   => NULL,
    window_name => NULL);
END;
/

… und auch die vorhandenen Einträge wieder loswerden:

BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => ‘SYS_AUTO_STS’ );
END;
/

Zur Kontrolle sollte die Abfrage 

select task_name, enabled from dba_autotask_schedule_control;

auf jeden Fall das Ergebnis FALSE für die Task „Auto STS Capture Task“ liefern.

WRI$_ADV_OBJECTS

Dieses Problem kann auf allen Versionen >= 12.2 auftreten. Symptome:

  • Abfrage von V$SYSAUX_OCCUPANTS zeigt SM/ADVISOR an erster Stelle.
  • Weitere Abfrage von DBA_SEGMENTS zeigt, dass die Tabelle WRI$_ADV_OBJECTS sehr viel oder sogar den meisten Platz in SYSAUX verbraucht

Grund: Optimizer Statistics Advisor, ein neues Feature ab 12.2, läuft täglich während des Maintenance Windows und erzeugt Einträge in der Tabelle. Die zugehörige Task AUTO_STATS_ADVISOR_TASK läuft extrem oft und verursacht ein starkes Wachstum des SYSAUX Tablespaces.

Die Statistics Advisor Task(AUTO_STATS_ADVISOR_TASK) kann wie folgt gedroppt werden und gibt damit den Platz wieder frei:

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

Das kann je nachdem, wie viele Einträge schon vorhanden sind, auch schon mal ein bisschen dauern ( bei ca. 25 Mio Einträgen waren es ca. 20 Minuten).

Wenn zu viele Einträge vorhanden sind, können diejenigen Rows, die nicht mit der Task „AUTO_STATS_ADVISOR_TASK“ zu tun haben, in einer Hilfstabelle zwischengespeichert und die Tabelle WRI$_ADV_OBJECTS per Truncate geleert werden. Zuletzt werden die zuvor gesicherten Einträge wieder hinzugefügt (dies sind i.A. nur wenige).

Anschließend sollten die zur Tabelle gehörenden Indizes noch neu aufgebaut und die Tabelle mit „alter table WRI$_ADV_OBJECTS move“ reorganisiert werden. Achtung: in einer PDB geht dies nur unter Verwendung des dbms_pdb-Packages, da ansonsten die Fehlermeldung

ORA-65040: operation not allowed from within a pluggable database

erzeugt wird. Also:

exec dbms_pdb.exec_as_oracle_script('alter table WRI$_ADV_OBJECTS move tablespace sysaux');

Es gibt diverse Artikel in MOS zum SYSAUX-Thema, z.B.:

  • Troubleshooting Issues with SYSAUX Space Usage (Doc ID 1399365.1)
  • Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER (Doc ID 329984.1)
  • SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)

Schreibe einen Kommentar