Fallstudie: ORA-01652 und fragmentierte Tablespaces

Seit Oracle 9i gibt es die sogenannten “System Managed Tablespaces”, bei denen das RDBMS die Extentgröße automatisch festlegt (“Autoallocate”, im Gegensatz zu “Uniform Extent Size”). Dieses Feature nimmt dem DBA einige Planungsarbeit ab, kann aber auch Tücken mit sich bringen, wie diese Fallstudie zeigt.

Szenario

• Oracle 10gR2 (10.2.0.4)
• Ein Tablespace für nahezu alle Tabellen der Anwendung:
  • 1 TB Größe
  • Locally Managed mit ASSM
  • Auto Extent Size
  • ca. 5000 Tabellen, viele davon > 1 GB und partitioniert.

Es wird ein “CREATE TABLE AS SELECT” (“CTAS”) ausgeführt, das nach einiger Laufzeit mit dem Fehler

ORA-01652: unable to extend temp segment by 128 in tablespace USERS

abbricht. Die Entwickler erwarteten, daß eigentlich noch genügend Platz im Tablespace hätte vorhanden sein müssen. Die Vergrößerung des Tablespaces bringt für einige Zeit Abhilfe, dann taucht der Fehler jedoch wieder auf.

Ursachenforschung

Was bedeutet die Fehlermeldung?

Zunächst einmal bedeutet sie, daß im Tablespace nicht mehr genügend freier Platz ist, um die angegebene Anzahl an Datenblöcken (hier: 128; bei einer Blockgröße von 8k also 1 MB) zu allokieren. Was hat es nun mit den Temp-Segmenten auf sich? Warum sind diese im USER-Tablespace und nicht im TEMP-Tablespace? Die Antwort ist, daß in bestimmten Fällen Temp-Segmente auch in permanenten Tablespaces angelegt werden. Im Einzelnen geschieht das bei folgenden Operationen:

1. SORT – Sortieroperationen bei SELECT oder DML/DDL
2. CREATE INDEX – Die Erzeugung eines Indizes bedingt Sortieroperationen. Diese werden im Default Temp Tablespace des Benutzers durchgeführt und belegen zusätzlich TEMP-Segmente im Ziel-Tablespace, während der Index dort zusammengebaut wird. Ist der Index fertig erzeugt, werden die Temp-Segmente in permanente Segmente umgewandelt.
3. CREATE PK CONSTRAINT
4. ENABLE CONSTRAINT
5. GLOBAL TEMPORARY TABLE (GTT) – Beim Zugriff auf eine GTT wird ein TEMP-Segment angelegt, das die Daten dieser Tabelle beinhaltet.
6. CREATE TABLE – Neue Tabellen werden zunächst als TEMP-Segment begonnen, wenn MINEXTENTS > 1 ist oder wenn “CREATE TABLE AS SELECT” eingesetzt wird.

Der letzte Punkt ist in unserem Szenario das Thema. Die Oracle-Doku empfiehlt bei dieser Fehlermeldung schlicht, den Tablespace um weitere Datafiles zu erweitern. Dies hatte aber im beschriebenen Szenario keine dauerhafte Abhilfe geschaffen. Wir wollen daher ermitteln,

• wie viel Platz derzeit im Tablespace verfügbar ist,
• wie viel Platz die neue Tabelle verbrauchen soll und
• welche Konsequenzen sich daraus ergeben.

Wie viel Platz ist im Tablespace?

Die “Lehrbuchmethode” zum Ermitteln des freien Platzes geht wie folgt:

SELECT SUM( blocks * b.block_size / 1024 / 1024 ) MB
  FROM dba_free_space f
     , ( SELECT block_size FROM dba_tablespaces WHERE tablespace_name='USERS' ) b
 WHERE tablespace_name='USERS';

In diesem Szenario wurden ca. 50 GB freier Platz gemeldet.

Wie viel Platz wird die neue Tabelle verbrauchen?

Hierfür gibt es verschiedene Ansätze:

• Wenn das Mengengerüst und die Größe der Rows in der neuen Tabelle bekannt ist, kann man den Platzverbrauch direkt per Multiplikation errechnen.
• Kennt man diese Werte nicht genau und weiß aber, wie viel Daten der abgefragten Tabelle(n) beim CTAS übrig bleiben, kann man die Größe der Quelltabelle(n) aus dem Data Dictionary abfragen und daraus die Größe der Zieltabelle ermitteln.
• In diesem Artikel wollen wir eine dritte Methode benutzen: Ausnutzung der Optimizer-Statistiken, um Mengengerüst und Zielgröße zu errechnen. Dies setzt voraus, daß für die Tabelle aktuelle und hinreichend genaue Statistiken vorliegen.

Dazu analysieren wir den SELECT-Teil des CTAS-Statements und betrachten nur den oberen Teil des Ausführungsplans, nämlich die Anzahl an erwarteten Bytes und informationshalber die Anzahl an erwarteten Rows.

EXPLAIN PLAN FOR
SELECT ... ;
SELECT ROUND( bytes / 1024 / 1024 ) MB, cardinality
  FROM plan_table<
 WHERE id = 0;

Hier die Ausgabe des SELECTS:

        MB CARDINALITY
---------- -----------
     38916   105443092

Wir erwarten also etwa 105 Millionen Rows, die circa 39 GB Platz verbrauchen werden. Im Tablespace sind aber angeblich 50 GB frei, warum bricht das CTAS dann ab?

Extentgrößen und Fragmentierung im Tablespace

Hier kommen nun die verschiedenen Extentgrößen ins Spiel: In einem “System Managed Tablespace” werden die Extentgrößen automatisch gewählt, üblicherweise mit der Folge, daß Tabellen mit zunehmendem Wachstum zunehmend größere Extents zugewiesen bekommen. Oracle wählt dafür Vielfache von 64 kB, so daß frei werdende Extents häufig wieder verwendet werden können. Häufig — nicht immer. Schauen wir doch mal, welche Extentgrößen in einer der betroffenen Tabellen vorliegen:

SELECT bytes/1024 KB, count(*)
  FROM dba_extents
 WHERE tablespace_name='USERS'
   AND segment_name = 'MEINETAB'
 GROUP BY bytes
 ORDER BY 1;

Es zeigt sich eine ziemliche Vielfalt an Extentgrößen:

KB COUNT(*)

----- --------

   64 24231

  128 30

  192 28

  256 37

  ...

 1024 32297

 1088 1

  ...

 7680 1

 8192 6117

65536 5

Die Tabelle “meinetab” belegt also eine Menge 64 kB-Extents, eine noch größere Menge an 1 MB-Extents, noch relativ viele 8 MB-Extents und sogar ein paar 64 MB-Extents. Wenn wir davon ausgehen, daß bei einer Tabelle dieser Größe ein CTAS dazu führt, daß die neue Tabelle relativ schnell auch bei den hohen Extentgrößen landet, dann müssen wir im Tablespace prüfen, wie viele Möglichkeiten es dort überhaupt gibt, große Extents anzulegen. Das überprüfen wir einmal für drei verschiedene Größen:

var block_size NUMBER;
exec SELECT block_size INTO :block_size FROM dba_tablespaces WHERE tablespace_name='USERS';
SELECT SUM( TRUNC( blocks/64 )) * 64 * :block_size / 1024 / 1024 MB_64
     , SUM( TRUNC( blocks/128 )) * 128 * :block_size / 1024 / 1024 MB_128
     , SUM( TRUNC( blocks/1024 )) * 1024 * :block_size / 1024 / 1024 MB_1024
  FROM dba_free_space
 WHERE tablespace_name='USERS';

  MB_64  MB_128 MB_1024
------- ------- -------
  25481    7302    2528

Und nun sehen wir, wo unser vermeintlich freier Speicherplatz verloren gegangen ist. Der freie Platz im Tablespace ist in so viele kleine Einheiten zersplittert, daß eine Erhöhung der Extentgröße von 64 auf 128 Blocks schon einen Verlust von 18 GB bedeutet. Bei 1024 Blocks/Extent stehen uns sogar nur noch 10 % des Platzes gegenüber 64 Blocks/Extent zur Verfügung!

Maßnahmen

Nun stehen die klassischen Maßnahmen einer Defragmentierung bevor. Aber um einer künftigen Fragmentierung vorzubeugen, werden Objekte bekannter Größe in Tablespaces mit fester Extentgröße abgelegt:

1. Feststellen, bei welchen Extentgrößen wie viel Platz belegt wird.
2. Feststellen, welche Objekte diese Extentgrößen beanspruchen.
3. Anlegen mehrerer Tablespaces mit verschieden hoher, aber fester Extentgröße.
4. Verschieben der Objekte in die passenden Tablespaces.
5. Löschen des alten, fragmentierten Tablespaces.

Mein persönlicher Geschmack für Punkt 3 ist, drei Tablespaces für kleine, mittelgroße und sehr große Objekte anzulegen. Wie in der guten, alten Zeit. ;-)

Hier ein Skript zum Identifizieren, wie viel Platz derzeit von kleinen, mittelgroßen und sehr großen Extents belegt wird. Auch dies ist spezifisch für diesen Fall zugeschnitten; die Entscheidung, wo “klein” aufhört und “groß” beginnt, muss natürlich von Fall zu Fall getroffen werden:

SELECT groesse, COUNT(DISTINCT segment_name) segs, SUM(blocks) blks
  FROM (SELECT segment_name, CASE
                               WHEN blocks < 128 THEN 'klein'
                               WHEN blocks BETWEEN 128 AND 1023 THEN 'mittel'
                               ELSE 'groß'
                             END
                               AS groesse, blocks
          FROM dba_extents
         WHERE tablespace_name = 'USERS')
GROUP BY groesse
ORDER BY blks DESC;

GROESSE       SEGS       BLKS
------- ---------- ----------
mittel         120   71063376
groß            24   36020440
klein         1664   15754312

In den folgenden Punkten ginge es also darum, z.B. die 24 großen Objekte in einen Tablespace mit einer Extentgröße von 8 MB oder mehr zu verschieben. Auf diese Punkte soll hier aber nicht näher eingegangen werden, um den geneigten Leser nicht zu langweilen.

Fazit

Wer sich in einschlägigen Internet-Foren über Oracles Extent Management informieren will, trifft häufig auf die Aussage, daß das automatische Management den DBA von allen Sorgen der Fragmentierung befreien könne. In der Praxis zeigt es sich aber, daß eine Automatik nicht immer die Kenntnisse über das Mengengerüst einer Datenbank ersetzen kann. Gerade, wenn ein Mix aus sehr kleinen bis sehr großen Tabellen bei einer hohen Anzahl von Extents vorliegt, kann es bei System Managed Tablespaces zu sehr hohen Fragmentierungsgraden kommen.

Es empfiehlt sich dann, Tablespaces mit fester Extentgröße zu verwenden, da diese nicht fragmentiert werden können.

Weblinks

• ORA-1652 Error Troubleshooting (Doc ID 793380.1)
• Erklärung des ORA-01652 in der Oracle Knowledge Base
• Ask Tom: Wie funktioniert Autoallocate? (Englisch)