Die Wanne ist voll – Shrink Table in Oracle Datenbanken

Ich gebe es zu, manchmal sind die Regeln einer Oracle DB verwirrend. Darunter fällt wohl auch die Regel der High Water Mark und der Extent-Allokierung.  Es klingt nicht gerade einleuchtend, dass gelöschte Datensätze den vorher benötigten Platz in einem Tablespace nicht wieder freigeben. Das liegt daran, dass ein Segment (wie eine Tabelle) bei Bedarf sogenannte Extents in einem Tablespace allokiert. Ein Extent ist dabei nichts anderes als eine bestimmte Menge an Datenbankblöcken. Bei dieser Allokation wird die High Water Mark des Segments nach oben gerückt. Wenn wir nun Datensätze aus einer Tabelle löschen, werden diese allokierten Extents nicht wieder an den Tablespace zurückgegeben. Das heißt also, es wird kein Freiplatz durch das Löschen von Datensätzen erzeugt.

Bis Oracle 10g war die einzige Möglichkeit die High Water Mark zu senken ein TRUNCATE auf die Tabelle, ein Export Import der Tabelle oder der Einsatz des ALTER TABLE MOVE Befehls. Diese Möglichkeiten sind aber denkbar ungeschickt wenn es sich um ein System handelt das keine Ausfallzeit erfahren darf.

Mit Oracle 10g kam die Möglichkeit des SHRINK Kommandos. Der Vorteil ist, dass die Tabelle dabei zugreifbar bleibt da keine Sperre auf die Tabelle gesetzt wird sondern nur auf einzelne Datensätze (ein sogenanntes Row Lock). Das klingt jetzt sehr verlockend, aber es sind gewisse Vorraussetzungen zu erfüllen um das SHRINK Kommando einsetzen zu können.

• Das Segment muss sich in einem Auto Managed Segment Space Tablespace befinden.
• Row Movement muss für die zu verkleinernde Tabelle aktiviert sein.
• Die Tabelle darf nicht komprimiert sein.
• Es darf kein Function Based Index auf der Tabelle liegen.

Nun wollen wir uns das Feature einmal genauer anschauen. Dazu nehmen wir uns eine Tabelle vor, die rund 2 Millionen Datensätze hält. Schauen wir uns an wieviele Blöcke, Extents und Speicherplatz die Tabelle benötigt.

SQL> select BLOCKS, to_char(BYTES/1024/1024), EXTENTS from dba_segments where SEGMENT_NAME='SHRINK_TABLE'; 
BLOCKS        TO_CHAR(BYTES/1024/1024)                 EXTENTS 
--------      ------------------------------------     --------------- 
65536         512                                      135

Nun schauen wir uns an wie stark die Blöcke gefüllt und wieviele Blöcke zwar allokiert aber noch garnicht genutzt sind. Dazu analysieren wir die Tabelle mit der Option compute statistics.

SQL> analyze table SHRINK_TABLE compute statistics;
TABLE ANALYZED

SQL> select EMPTY_BLOCKS, AVG_SPACE from dba_tables where table_name = 'SHRINK_TABLE'; 
EMPTY_BLOCKS  AVG_SPACE 
------------  --------- 
333           875

Nun löschen wir willkürlich Datensätze aus der Tabelle und lassen uns danach wieder Statistikinformationen erzeugen.

SQL> delete from shrink_table where object_id > 10000 and object_id < 15000; 
301632 Rows deleted. 

SQL> commit; 
SQL> analyze table SHRINK_TABLE compute statistics;
Table analyzed

An der Anzahl der Blöcke, Extents und des verbrauchten Speicherplatzes hat sich aber nichts geändert wie wir hier sehen.

SQL> select BLOCKS, to_char(BYTES/1024/1024), EXTENTS from dba_segments where SEGMENT_NAME='SHRINK_TABLE'; 

BLOCKS     TO_CHAR(BYTES/1024/1024)                 EXTENTS 
---------  ---------------------------------        ------------- 
65536      512                                      135

Die komplette Anzahl an leeren Blöcken blieb ebenso unverändert. Jedoch ist der durchschnittliche Freiplatz pro Block gestiegen.

SQL> analyze table SHRINK_TABLE compute statistics;
TABLE ANALYZED

SQL> select EMPTY_BLOCKS, AVG_SPACE from dba_tables where table_name = 'SHRINK_TABLE'; 
EMPTY_BLOCKS  AVG_SPACE 
------------  --------- 
333           1350

Also wenden wir einmal das SHRINK Kommando an. Dafür aktivieren wir für die Tabelle erst einmal das Row Movement und setzen anschließend den SHRINK ab.

SQL> alter table scott.shrink_table enable row movement; 
Table altered. 

SQL> alter table scott.shrink_table shrink space; 
Table altered. 

SQL> analyze table scott.shrink_table compute statistics; 
Table analyzed.

Nun schauen wir uns abschließend die Extents und die Anzahl der Blöcke an um festzustellen, dass beides abgenommen hat.

SQL> select BLOCKS, to_char(BYTES/1024/1024), EXTENTS from dba_segments where SEGMENT_NAME='SHRINK_TABLE';

BLOCKS     TO_CHAR(BYTES/1024/1024)                 EXTENTS 
--------   --------------------------------         ------------- 
61136      477,625                                  131 

SQL> select EMPTY_BLOCKS, AVG_SPACE from dba_tables where table_name = 'SHRINK_TABLE';

EMPTY_BLOCKS  AVG_SPACE 
------------  ----------          
344           874

Der Unterschied wäre noch größer wenn die Tabelle größer gewesen wäre. Aber ich denke das Prinzip ist klar geworden.

Leider ist immer dort wo es Licht gibt auch Schatten. Im Falle des Shrink Table Mechanismus liegt der Nachteil im aufwändigen Verschieben einzelner Rows in einen anderen Block. Das vorgehen kann nicht parallelisiert werden und es wird Row für Row, Block für Block vorgegangen. Das kann bei großen Segmenten oder bei hoch dynamischen Tabellen dazu führen, dass ein gestarteter Shrink Prozess nicht fertig wird da immer wieder neue Rows verschoben werden müssen. In solchen Fällen sollte im Rahmen einer Downtime weiterhin zum Export / Import oder zum Einsatz des ALTER TABLE MOVE Befehls gegriffen werden.

Philip