Neues vom SYSAUX-Tablespace…

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);
BEGINv_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). 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_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)

Schreibe einen Kommentar