Fragmentierung einer Oracle-Datenbank

Ein kleiner Arikel was mit "Fragmentierung" innerhalb von Oracle gemeint ist, verschiedene Meinungen dazu und einige SQL's

Die Fragmentierung von Datenbank-Objekten (Tabellen, Indexe)

{Beschreibung}
Unter Fragmentierung von Datenbank-Objekten versteht man normalerweise, das ein Datenbank-Objekt (z.B: Tabelle oder Index) nicht mehr in das INITIAL_EXTENT passt.
Das kann passieren wenn das Datenbank-Objekt mit den falschen Storage-Parametern erzeugt wird (INITIAL_EXTENT zu klein) oder wenn das Datenbank-Objekt wächst.
In beiden fällen reicht das INITIAL_EXTENT nicht aus um alle Daten des Datenbank-Objekts zu speichern, daher werden weitere extents angelegt.

Für mich ist das eigendlich keine "Fragmentierung" da keine "Löcher" entstehen die nicht wieder mit Daten gefüllt werden könnten, und ich schliese mich daher der Meinung von Tom Kyte an:

"In fact, some of mine (Tables) are nearing 1,000 extents and I am perfectly happy. If having hundreds of extents was a problem -- I would not be happy. Since having lots of extents is not a problem, I have no problems with this situation.

This is not fragmentation. Fragmentation is when you have lots of irregular "holes" in a tablespace. In your system tablespace -- since space is never released, only allocated, you cannot have fragmentation. You do not have any HOLES in system -- just lots of stuff.

It should NEVER be your goal to absolutely minimize extents. That is a myth.
siehe Ask Tom Forum

Bei einem Index ist es ähnlich ( siehe auch hier im Ask Tom Forum)

{Identifikation}
Mit folgendem SQL können die Datenbank-Objekte idendifiziert werden, die mehr als zehn extents haben:

select * from dba_segments where extents > 10;

{Beseitigung}
Eine Möglichkeit diese Extents wieder in eim Extent zusammenzufassen ist ein Export und ein anschließender Import

Export der tabelle mit der COMPRESS=Y option
Drop der Tabelle (oder TRUNCATE)
Anschließender Import.

In der 10g Dokumentation habe ich den Befehl

ALTER TABLE MOVE TABLESPACE

gefunden, mit dem es möglich sein
sollte eine Tabelle in ein neues Tablespace zu verschieben und dabei die Storage-Parameter zu ändern ohne alle Foreign-keys und Tabellen-Trigger zu verlieren
(Dazu habe ich leider keine endgültige Aussage in der Oracle-Doku gefunden und daher sollte das getestet werden bevor man es einsetzt).
Allerdings muss auch hier genug Platz zur Verfügung stehen, da die Tabelle dupliziert wird.

Ab 9i exitstiert das package DBMS_REDEFINITION mit dem es möglich ist die logische oder physikalische Struktur zu verändern.
Währen dieser redefinition kann ganz normal auf die Tabelle zugegriffen werden. Ein Lock auf die Tabelle ist unabhängig von der größe nur für einen kurzen Zeitraum notwendig. Es wird auch hier mindestens der doppelte Speicherplatz benötigt. (PCTFREE)
Auch hier sollte es kein Problem mit Triggern und Foreign-Keys geben aber in der Doku habe ich auch hier keine eindeutige Aussage gefunden.

Wie ein Index neu erzeugt werden kann habe ich in Anhang beschrieben.

{Probleme}

Eine Tabelle zu droppen birgt viele Risiken, da bei diese Aktion alle an der Tabelle "hängenden" Objekte auch mitgelöscht werden, z.B: Trigger und Foreign Keys
Doppelter Plattenplatz, da die Tabelle auf jeden fall dupliziert werden muss (Auch bei ALTER TABLE MOVE).
Auch ein Recreate eines Indexes ist nicht risikolos, falls Unique- oder Primary Indexes gedropt werden. In diesem Fall ist die Datenbank nicht mehr in der Lage ist auf Eindeutigkeit oder Korrektheit zu prüfen und es ist daher möglich inkonsistente Daten in den dazugehörigen Tabellen abzulegen.

Fragmentiete Tablespaces

{Beschreibung}
Ein fragmentiertes Tablespace ist ein Tablespace in dem verschieden Objekte angelegt werden/wurden, die unterschiedliche Extentgrößen haben (oder PCTINCREASE verwenden) solange diese Objekte nicht gelöscht werden haben wir auch keine Fragmentierung. Falls aber Objekte (Tabellen, Indexe ..) gelöscht entstehen "Löcher", was erst einmal nicht weiter schlimm ist.
Falls diese "Löcher" aber unterschiedliche Größen haben können, passen die nächsten Extents von wachsenden Tabellen, oder neue Objekte nicht in die "Löcher".

{Identifikation}
Fragmentierte Tablepaces liegen vor wenn ein neues Objekt nicht erzeugt werden kann (oder eine Tabelle/Index nicht erweitert werden kann obwohl in summe genug freier Platz zur Verfügung stehen würde

{Beseitigung}
Benutzung von LMT's (Locally Managed Tablespaces) oder erstellen aller Datenbankobjekte mit demselben NEXTEXTENT und einem INITIALEXTENT der ein Vielfaches des NEXTEXTENTS ist.
Dadurch ist es möglich die entstandenen "Löcher" wiederzuverwenden.

{Probleme}
Das Datenbank-Objekte (Tabelle, Index usw) in mehrere Extents verteilt sind hat (lt. Tom Kyte, DBAzine) auf die Performance keinen (oder nur marginalen) Einfluss. Da das Problem im Nachhinein nur durch eine Redefinition der Storage-Parameter der Datenbank-Objekte gelöst werde kann gilt hier das gleiche wie bereits im Kapitel Fragmentierung von Datenbank-Objekten gesagte.
Besser ist es bereits beim Datebank Design auf LMT's einzusetzen oder (und) beim Erstellen der Datenbank-Objekte auf konsistente Extentgrößen zu achten.

Ein Tablespace, in dem nie Datenbank-Objekte gelöscht werden fragmentiert nicht! (was nicht heißt das in diesem Tablespace kein Platz verschwendet werden kann)

Anhang

Einen Index neu erzeugen

Der einfachste Weg einen Index neu zu erzeugen ist:

Alter index <index-name> rebuild tablespace < index-tablespace>;

Diese Vorgehensweise hat außerdem den Vorzug, dass der Zugriff auf den Index während der regenerierung weiter möglich ist. Das Tablespace sollte angegeben werden, da der Index sonst im Default-Tablespace des Users angelegt wird.

Beim neu Erzeugen eines Indexes haben wir die Möglichkeit dem Index neue Storage-Parameter mitzugeben.

Falls der Index aber korrupt ist, bleibt uns nichts anders übrig als den Index zu droppen und wieder neu anzulegen.

Zuerst lassen wir uns die index Spalten anzeigen
SELECT COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME = 'index_name'; SELECT TABLE_NAME, UNIQUENESS, TABLESPACE_NAME, INITIAL_EXENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE FROM USER_INDEXES WHERE INDEX_NAME = 'index_name';
Das obige SQL liefert uns die Spalten aus denen der Index aufgebaut ist.
Drop index <index-name>; Create [UNIQUE] index <index-name> On <table-name> ( <column-1> [ , <column-n> ] ) tablespace <tablespace> PCTFREE <pct-free> STORAGE ( NEXT <size>K INITIAL <size>K PCTINCREASE <pct_increase> MIN_EXTENTS <min_extents> MAX_EXTENTS <max_extents> ); {Hinweis}
- Um die Anzahl der Extents zu reduzieren sollte das initial extent auf einen Wert gesetzt werden das der komplette Index hineinpasst.
  Die Größe des alten Indexes kann mit:
  Select sum(bytes) from dba_extents where segment_name='<index-name>';
  ermittelt werden.
- Alternativ kann man auch die im nächsten Absatz beschriebenen Wege gehen.
- Der Primary-Key sollte nicht auf diese weise gedropped or neu erzeugt werden. (Doppelte Einträge in der Tabelle da eine Überprüfung des UNIQUE oder PRIMAY nicht mehr möglich ist)

Informationen über einen Index ermitteln

Allgemeine Informationen über einen Index können mit folgen SQL's ermittelt werden:

Analyze index <index-name> compute statistics; Select * from user_indexes where index_name= '<index-name>';

Tiefergehende Informationen werden durch folgende Statements in die Tabelle INDEX_STATS geschrieben:

Analyze index <index-name> validate structure;
Oder Validate index <index-name>;

Diese Tabelle kann nur eine Zeile enthalten, in der ein Index beschrieben ist. Die folgenden SQL's verifizieren auch die Integrität jedes Datenblocks.
Um z.B die größe des Indexes zu erhalten:

validate index <index_name>; select name "INDEX NAME", blocks * 8192 "BYTES ALLOCATED", btree_space "BYTES USED", (btree_space / (blocks * 8192))*100 "PERCENT USED" from index_stats; Im obigen Beispiel wird davon ausgegangen, das ein DB-Block eine Größe von 8Kb (8192 bytes) hat.
Es zeigt die Anzahl von Bytes an die für den Index allokiert sind, und NICHT die Anzahl die momentan benutzt wird.

email senden