Online Table Shrink

Online Table Shrink
Freigabe von ungenutztem Speicherplatz
Autor: Ralf Durben, ORACLE Deutschland GmbH
DOAGNews Q2_2004
Dieses Werk ist urheberrechtlich geschützt. Die dadurch begründeten Rechte, insbesondere die der
Übersetzung, des Nachdrucks, des Vortrags, der Entnahme von Abbildungen und Tabellen, der
Funksendung, der Mikroverfilmung oder der Vervielfältigung auf anderen Wegen und der Speicherung in
Datenverarbeitungsanlagen, bleiben, bei auch nur auszugsweiser Verwertung, vorbehalten. Eine
Vervielfältigung dieses Werkes oder von Teilen dieses Werkes ist auch im Einzelfall nur in den Grenzen
der gesetzlichen Bestimmungen des Urheberrechtes der Bundesrepublik Deutschland vom 9. September
1965 in der jeweils geltenden Fassung zulässig. Sie ist grundsätzlich vergütungspflichtig. Zuwiderhandlungen unterliegen den Strafbestimmungen des Urheberrechtsgesetzes.
©2004
1. Überblick
Die in einer Datenbanktabelle gespeicherten Datenzeilen können in Oracle Database 10g so
zusammengeschoben werden, daß sie physisch möglichst nebeneinander liegen. Danach kann der
restliche, nicht mehr benötigte Speicherplatz freigegeben werden und die Highwatermark wird
gesenkt.
2. Motivation
Der von einer Tabelle allokierte Speicherplatz wird von Oracle normalerweise automatisch
verwaltet. Wenn Datenzeilen gelöscht werden, wird dieser freiwerdende Speicher zur
Speicherung neuer Zeilen verwendet.
Angenommen eine Tabelle wird mit 1 Mio. Zeilen gefüllt, und die Tabelle nimmt eine Größe von
100MB ein. Wenn nun aus dieser Tabelle 500000 Zeilen gelöscht werden, können die folgenden
Probleme entstehen:
Wenn keine neuen Zeilen in der Tabelle gespeichert werden sollen, belegt die Tabelle weiterhin
100MB, obwohl evtl. effektiv nur 50MB benötigt werden.
Die Datenblöcke der Tabelle sind nicht mehr so gut gefüllt, wie es wünschenswert wäre. Bei
einem Full Table Scan müssen alle Blöcke bis zur High Water Mark gelesen werden (z.B.
100MB), obwohl bei effektiverer Speicherung nur weniger Blöcke (z.B. 50MB) gelesen werden
müssten.
Die Tabelle wird, auch wenn sie noch weniger Daten enthalten wird, weiterhin 100MB Speicher
verbrauchen – sie wird nie mehr kleiner.
Abbildung 1
Abbildung 2
©2004
3.
Beschreibung
Es wird davon ausgegangen, daß die Speicherung der Datenzeilen innerhalb einer Tabelle
fragmentiert ist, wie es in der Abbildung1 dargestellt wird. Im Beispiel sind 5 Datenblöcke
gefüllt, drei davon enthalten aber nur wenig Daten. Die High Water Mark (HWM) zeigt an,
welche Blöcke jemals Daten gespeichert haben. Wenn nun ein Full Table Scan erfolgt, also das
Suchen von Daten durch Lesen der gesamten Tabelle, werden alle 5 Datenblöcke gelesen und
verarbeitet.
Die Shrink-Operation verschiebt die Datenzeilen derart, daß so viel Datenzeilen wie möglich in
den Datenblöcken gespeichert sind. Im Beispiel reichen 3 Datenblöcke zur Speicherung der
Tabellendaten aus. Die High Water Mark wird entsprechend zurückgesetzt und der nicht mehr
benötigte Speicherplatz wird freigegeben. Ein Full Table Scan wird jetzt nur noch 3 Datenblöcke
lesen.
Es gibt zwei Spielarten des Online Table Shrink:
Variante1:
Tabellenzeilen werden zusammengeschoben, die High Water Mark bleibt unverändert,
ungenutzter Speicher wird nicht freigegeben. (Abbildung2)
Variante2:
Tabellenzeilen werden zusammengeschoben, die High Water Mark wird zurückgesetzt,
ungenutzter Speicher wird freigegeben. (Abbildung1)
Genutzt wird dieses Feature durch die folgende Syntax:
Variante1:
ALTER TABLE <tabellenname> SHRINK SPACE COMPACT;
Variante2:
ALTER TABLE <tabellenname> SHRINK SPACE;
Mit der Syntax SHRINK CASCADE wird der Schrumpfungsprozess auf andere abhängige
Tabellen (zum Beispiel über referenzielle Constraints) ausgeweitet.
Das Feature arbeitet online. Sowohl lesender als auch schreibender Zugriff auf die betroffene
Tabelle ist während des Schrumpfungsvorgangs möglich.
Indices auf die betroffene Tabelle werden automatisch angepaßt, Trigger aber nicht gezündet.
Online Table Shrink kann auf folgende Objektarten angewandt werden:
•
Normale Tabellen (Heap Tabellen)
•
IOT´s
•
Materialized Views
©2004
Online Table Shrink wirkt sich auch aus auf:
•
LOB Segmente
•
Indices
4. Voraussetzungen
•
Dieses Feature gibt es ab Oracle Database 10g.
•
Die Tabelle muß ein einem Tablespace gespeichert sein, der mit dem Modus „Segment Management Auto“
arbeitet.
•
Für die Tabelle muss die Eigenschaft ROW MOVEMENT aktiviert sein, ggf. mit ALTER TABLE
<tabellenname> ENABLE ROW MOVEMENT;
5. Beispiel
SQL> drop table to_shrink;
Table dropped.
SQL> create table to_shrink (n1 number,n2 number,t1 varchar2(100),t2 varchar2(100));
Table created.
SQL> begin
2
for i in 1..100000 loop
3
insert into to_shrink values
(i,i,'siahdgfisgsiahdgfisgsiahdgfisgsiahdgfisgsiahdgfisg',
4
'siahdgfisgsiahdgfisgsiahdgfisgsiahdgfisgsiahdgfisg');
5
commit;
6
end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> analyze table to_shrink compute statistics;
Table analyzed.
SQL> select bytes,blocks,extents from dba_segments where segment_name='TO_SHRINK';
BYTES
BLOCKS
EXTENTS
---------- ---------- ---------13631488
1664
28
SQL> select empty_blocks,avg_space from dba_tables where table_name='TO_SHRINK';
EMPTY_BLOCKS AVG_SPACE
------------ ---------34
906
SQL> delete from to_shrink where n1 between 1000 and 9000;
8001 rows deleted.
SQL> delete from to_shrink where n1 between 11000 and 19000;
8001 rows deleted.
SQL> delete from to_shrink where n1 between 21000 and 29000;
8001 rows deleted.
SQL> delete from to_shrink where n1 between 31000 and 39000;
8001 rows deleted.
©2004
SQL> delete from to_shrink where n1 between 41000 and 49000;
8001 rows deleted.
SQL> delete from to_shrink where n1 between 51000 and 59000;
8001 rows deleted.
SQL> delete from to_shrink where n1 between 61000 and 69000;
8001 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table to_shrink compute statistics;
Table analyzed.
SQL> select bytes,blocks,extents from dba_segments where segment_name='TO_SHRINK';
BYTES
BLOCKS
EXTENTS
---------- ---------- ---------13631488
1664
28
SQL> select empty_blocks,avg_space from dba_tables where table_name='TO_SHRINK';
EMPTY_BLOCKS AVG_SPACE
------------ ---------34
4847
Man sieht hier, dass die Datenblöcke im Durchschnitt mehr Freispeicher haben (Spalte AVG_SPACE). Die Anzahl
der Extents hat sich nicht geändert.
SQL> ALTER TABLE to_shrink ENABLE ROW MOVEMENT;
Table altered.
SQL> ALTER TABLE to_shrink SHRINK SPACE;
ALTER TABLE to_shrink SHRINK SPACE
Table altered.
SQL> analyze table to_shrink compute statistics;
Table analyzed.
SQL> select bytes,blocks,extents from dba_segments where segment_name='TO_SHRINK';
BYTES
BLOCKS
EXTENTS
---------- ---------- ---------5767168
704
21
SQL> select empty_blocks,avg_space from dba_tables where table_name='TO_SHRINK';
EMPTY_BLOCKS AVG_SPACE
------------ ---------20
545
Die Anzahl der Extents ist gesunken – 7 Extents sind freigegeben worden. Der durchschnittlich freie Speicher in
einem Datenblock ist auch gesunken. Die Datenzeilen sind offenbar so beschaffen, dass der durchschnittliche
Freispeicher nicht weiter sinken kann.
©2004
6. Fazit
Es gibt Situationen, in denen eine Reoganisation der Tabellenzeilen sinnvoll ist. Oracle Database
10g bietet diese Möglichkeit in Form einer Online Reorganisation an. Es bleibt aber bei der
Aussage, dass eine regelmäßige Reorganisation von Datenbanktabellen im Allgemeinen nicht
erforderlich ist.
Kontakt:
Ralf Durben,
Oracle Deutschland GmbH
[email protected]
©2004