Zum Inhalt springen
  • Von: Dr. Martin Jursch
  • Datenbank Development
  • 02.10.2013

Defragmentierung von Tablespaces für Arme

Wer in Oracle-Datenbanken Tablespaces defragmentieren will, kann die Daten per Export/Import oder „Alter Table…move“ in einen neu angelegten Tablespace umkopieren. Das bedeutet allerdings zugleich mehr Speicherplatz, privilegierte Rechte sowie die Nicht-Verfügbarkeit der Application. Wer es sich nicht leisten kann, hat eine Alternative: Die In-Place-Defragmentierung. DOAG Online zeigt, wie dies funktioniert.


Wer in Oracle-Datenbanken Tablespaces defragmentieren will, kann die Daten per Export/Import oder „Alter Table…move“ in einen neu angelegten Tablespace umkopieren. Das bedeutet allerdings zugleich mehr Speicherplatz, privilegierte Rechte sowie die Nicht-Verfügbarkeit der Application. Wer es sich nicht leisten kann, hat eine Alternative: Die In-Place-Defragmentierung. DOAG Online zeigt, wie dies funktioniert.

Wenn aufräumen zur Notwendigkeit wird. Foto: eyetronic - Fotolia.com

Die Fragmentierung von Tablespaces in Oracle-Datenbanken ist ein bekanntes Problem. Mit der Zeit werden, analog Festplatten in PCs, die einzelnen Datenobjekte über den gesamten Speicherbereich verteilt. Von der Defragmentierung sind zum einen die einzelnen Datenbankobjekte selbst betroffen – wie zum Beispiel Tabellen. Darüber hinaus werden die Datenbankobjekte auch innerhalb der Tablespaces verteilt. Kurz: Auch der Tablespace muss wieder aufgeräumt werden.

Die gängigste Methode hierfür ist Ihnen sicherlich bekannt: Sie legen parallel einen neuen Tablespace an und kopieren die Daten per Export/Import oder „Alter Table…move“ in diesen um. Danach liegen die Datenbankobjekte alle zusammenhängend am Anfang des neuen Tablespaces. Der alte Tablespace kann nach diesem Vorgang gelöscht werden.

Wird der Daten-Transfer per Export/Import durchgeführt, so stellen Sie sicher, dass alle Datenbankobjekte (auch Tabellen mit LONG-Spalten) verschoben werden können. Allerdings ist an dieser Stelle anzumerken, dass ein Export/Import deutlich langsamer als ein „Alter Table…move“ ist.

Wer sich für diesen Weg entscheidet, kann mit einer schnellen Reorganisation rechnen, da die Daten nur einmal kopiert werden. Allerdings setzt diese Methode mehrere Bedingungen voraus: Zeitweilig benötigen Sie den doppelten Speicherplatz. Dieser muss zuerst einmal bereitgestellt werden. Damit verbunden sind Rechte und Zugriffsmöglichkeiten, über die Sie vielleicht gar nicht verfügen.

Wenn die Umgebung von einem Provider extern administriert wird und eine andere Person die Applikation verwaltet, ist diese Methode möglicherweise nur schwer umsetzbar. Unter solchen Bedingungen kann die Laufzeit der Reorganisation mehrere Tage dauern – ein Zeitraum, in dem die Anwendung nicht verfügbar ist. Bedenkt man, dass Speicherplatz nur quartalsweise gemietet werden kann, so können zudem erhebliche Kosten entstehen, die für das Unternehmen nicht tragbar sind. In einer solchen Situation braucht man manchmal einen Plan B.

Plan B: In-Place Defragmentierung

Anfangs-Szenario

Als Beispiel dient ein Tablespace, der aus drei Daten-Dateien mit einer Größe von je 30 GB besteht. Die Datenbankobjekte sind so verteilt, dass nur insgesamt 2 GB am Ende der drei Dateien freigegeben werden können (siehe Abbildung 1). Darüber hinaus sind auch die Tabellen in sich defragmentiert.

Anfangs-Szenario
Abbildung 1: Anfangs-Szenario

Defragmentieren von Tabellen

Durch DML-Operationen (Data Manipulation Language) sind nach einiger Zeit unter Berücksichtigung von PCTFree nicht mehr alle Blöcke der Tabelle vollständig gefüllt. Das Löschen, Updaten und Einfügen von einzelnen Zeilen beispielsweise verursachen Lücken, in die neue Zeilen nicht mehr passen. Dies kann in ungünstigen Fällen dazu führen, dass die Tabelle den doppelten Platz beansprucht, der eigentlich nötig wäre. Auch bei Indices kann dieses Phänomen auftreten.

Bevor man nun den Tablespace defragmentiert, sollten zuerst die einzelnen Datenbankobjekte reorganisiert werden. Bei Tabellen verwendet man dazu den Befehl Alter table … shrink space cascade. Um diesen Befehl nutzen zu können, muss row movement aktiviert sein (siehe Kasten).

Dieser Befehl bringt als erstes alle Daten der Tabelle in vollständig gefüllten Blöcken unter (siehr Abbildung 2 - Schritt 1). Danach wird die High-Water-Mark (HWM) angepasst (Schritt 2). Schließlich werden die freien Blöcke oberhalb der neuen HWM freigegeben (Schritt 3).

So können die Datenbankobjekte reorganisiert werden
Abbildung 2: So können die Datenbankobjekte reorganisiert werden

Die Befehle lauten:

row movement aktivieren

Alter Table <tablename> enable rowmovement;

Nicht Partionierte Tabelle

Alter Table <tablename> shrink space cascade;

Partionierte Tabelle (für jede Partition)

Alter Table <tablename> modify partition <partitionname> shrink space

Partionierte Tabelle (mit Subpartitionen) (für jede Subpartition)

Alter Table <tablename> modify subpartition <subpartitionname> shrink space

Defragmentieren des Tablespaces

Das Defragmentieren des Tablespaces sollte grundsätzlich in drei Schritten erfolgen (siehe Abbildung 3). Zuerst werden die Tabellen defragmentiert. Dann werden alle Datenbankobjekte an den Anfang der Daten-Dateien verschoben. Schließlich werden die Daten-Dateien verkleinert.

 Defragmentierung von Tablespaces
Abbildung 3: Defragmentierung von Tablespaces

Ziel dieser Methode ist das Defragmentieren des Tabelspaces, der aus mehreren Daten-Dateien besteht. Dabei bleibt die Applikation verfügbar und prozessiert weiter Daten.

Defragmentierungs-Prozess:

Hierzu wird ein Prozess aufgesetzt (siehe auch Abbildung 4), der von einem normalen Schema aus gestartet wird (nicht SYS, SYSTEM,...). Dieser verschiebt iterativ die Datenbankobjekte innerhalb des Tablespaces. Es wird kontinuierlich die Situation im Tablespace analysiert. Dadurch kann der Prozess auf Änderungen durch die laufende Applikation reagieren.

  1. Der Recycle bin aller Schemata, die Objekte in diesem Tablespace haben, wird gelöscht.
  2. Die Defragmentierung erfolgt in einer Schleife mit m Durchgängen. (Schleife 1 / Beginn)
  3. Aus dba_extents und dba_free_space wird die Reihenfolge der Datenbankobjekte pro Daten-Datei vom Ende her bestimmt.
  4. In einer Schleife werden pro Daten-Datei die letzten n Datenbankobjekte bearbeitet. (Schleife 2 / Beginn)
  5. Pro Objekt wird bestimmt, welcher Befehl anzuwenden ist. Bei Objekten anderer Schemata wird ein Database-Link verwendet.
    Die Befehle sind:
Nicht partionierte Tabelle

Alter Table <tablename> move;

Partionierte Tabelle

Alter Table <tablename> move partition …;

Partionierte Tabelle (mit Subpartitionen)

Alter Table <tablename> move subpartition;

LOB nicht partionierte Tabelle

Alter Table <tablename> move LOB (<columnname>) store as <segmentname>;

LOB partionierte Tabelle

Alter Table <tablename> move partition <partitionname>LOB (<columnname>) store as <segmentname>;

LOB partionierte Tabelle (mit Subpartitionen)

Alter Table <tablename> move subpartition <subpartitionname>
LOB (<columnname>) store as <segmentname>;

Nicht partionierter Index

Alter index <indexname> rebuild;

Partionierter Index

Alter index <indexname> rebuild partition <partitionname>;

Partionierter Index (mit Subpartitionen)

Alter index <indexname> rebuild subpartition <subpartitionname>;

IOT

Alter Table <IOtablename> move;

  1. Nun werden die Daten-Dateien des Tablespaces verkleinert. So kann man Oracle zwingen, die Datenbankobjekte weiter nach vorne zu kopieren. Das Verkleinern erfolgt in einer Schleife bis der Fehler „ORA-03297: file contains used data beyond requested RESIZE value“ auftritt. Oracle fasst nicht immer mehrere benachbarte freie Extents am Datei-Ende zusammen. Auch der Befehl Alter Tablespace … coalesce führt nicht immer zu dem gewünschten Ergebnis.
  2. Ende Schleife 2
  3. Das Ende der Schleife 1 erfolgt durch das Erreichen der angegebenen Schleifendurchgänge oder durch Unterschreiten des Schwellwertes bezüglich der Änderung des Tablespaces von Durchgang zu Durchgang.
  4. Die Statistiken der verschobenen Datenbank-Objekte müssen neu erstellt werden.
  5. Prozess Ende

Darstellung des Defragmentierungs-Prozesses
Abbildung 4: Darstellung des Defragmentierungs-Prozesses

 

Listings

Der Abbruch der Defragmentierung kann durch mehrere Faktoren erfolgen:

  • Feste Anzahl der Schleifendurchläufe in „loop_move
  • Keine Verkleinerung der Tablespace-Größe (bzw. Verkleinerung unter x%)
  • Erreichen einer definierten Tablespace-Größe

Diese Faktoren können zum Teil kombiniert werden. So sollten a und b zusammen verwendet werden, um eine Endlos-Schleife zu vermeiden.

Der Prozess hat den Vorteil, dass er aus einem beliebigen Schema aus durchgeführt werden kann. Es wird kein zusätzlicher Plattenplatz benötig, womit Aktivitäten außerhalb der Datenbank unnötig sind. Durch das iterative Herangehen mit kontinuierlicher Analyse der Situation kann die Applikation mit nur geringen Einschränkungen weiterlaufen (gegebenenfalls muss auf eine Verschiebung gewartet werden). Das ständige Anpassen des Tablespaces nach jeder Verschiebung gewährleistet die Verschiebung in die gewünschte Richtung und damit die gewünschte Verkleinerung des Tablespaces. Ein Nachteil ist sicherlich die geringere Performance, weil Objekte manchmal mehrfach verschoben werden. Auch die mehrfache Abfrage von dba_extents und dba_free_space ist performance-lastig.

End-Szenario

Am Ende der Defragmentierung bleibt ein 60GB-Tablespace mit ca. 15 GB endständigem freien Speicherplatz übrig.

End-Szenario
Abbildung 5: End-Szenario

Einschränkungen

Der vorgestellte Prozess ist zwar nicht Standard, aber er stellt in gewissen Situationen eine kostengünstige und ressourcenschonende Alternative zum Umkopieren zwischen zwei Tablespaces dar. Sobald ein größeres Projekt abgeschlossen wird, wird der Defragmentierer über einen Job angestoßen. Das mehrfache Verschieben von Objekten, die häufige Abfrage von dba_extents und dba_free_space sowie die Zeitverzögerungen durch Lock auf Objekte beeinträchtigt zwar die Performance, aber der Prozess kann im laufenden Betrieb der Applikation ausgeführt werden. Zusätzlich zu diesen Einschränkungen müssen auch folgende Faktoren berücksichtigt werden:

  • Datafile resize

In der Datenbank kann man sich nicht darauf verlassen, dass endständige freie Extents automatisch zusammengefasst werden. Daher muss beim datafile resize iterativ durch die endständigen Extents gegangen werden.

  • Tabellen mit LONG-Spalten

Einige Oracle-interne Tabellen haben LONG-Spalten. Es ist zum Beispiel der Fall bei der Tabelle PLAN_TABLE. Diese können nicht verschoben werden. Allerdings können sie im Prozess gelöscht werden, da sie später automatisch von Oracle neu erzeugt werden.

Vorteile

Nachteile

Kein zusätzlicher Plattenplatz.

Schlechtere Performance durch mehrfaches verschieben und häufige Abfrage von dba_extents / dba_free_space.

Kann von einem normalen Schema durchgeführt werden.

Zeitverzögerungen durch Lock auf Objekte die gerade verschoben werden.

Kann im laufenden Betrieb der Applikation ausgeführt werden.

 

Kann als Wartungs-Prozess automatisch wiederkehrend eingesetzt werden.