Hier ein aktuelles Beispiel:
Abbildung siehe rechts
Der Tablespace hat eine Größe von 136 GByte und davon sind 38.7 GByte frei und 97,3 GByte belegt. Das bedeutet, der Tablespace ist zu 72% gefüllt. Somit ist doch alles in Ordnung, oder? Die Kollegen wundern sich allerdings, dass der Tablespace beziehungsweise die Datendatei immer wieder vergrößert wird und zwar scheinbar immer dann, wenn der freie Platz 25%
Fragmentierung
Der Tablespace wurde mit den Standardwerten (hier als Bigfile Tablespace) angelegt. Das bedeutet, dass die Extents mit "Autoallocate" angelegt werden. "Autoallocate" heißt, dass für neue Segmente zunächst kleine Extents (64kB) und dann in Schritten (1MB, 8MB, 64MB) größere Extents angelegt werden. Ausnahme sind Tabellen, die über Create Table As Select angelegt werden. Hier kann es sein, dass die Extents sofort mit größeren Einheiten erstellt werden.
Es ist also eine nähere Analyse erforderlich:
SQL> SELECT round(bytes/1024/1024)
FROM dba_free_space
WHERE tablespace_name = 'XXX' AND bytes > 1024*1024;
ROUND(BYTES/1024/1024)
----------------------
1067
2048
2048
14
Listing 1: Analyse des Tablespace
Das heißt, es gibt nur vier freie Bereiche in dem Tablespace, die größer als 1 MByte sind und insgesamt sind diese Bereiche nur 5 GByte groß. Wir haben 97,3 GByte belegten Platz plus 5 GByte freien Platz. Das sind zusammen 102,3 GByte. Es fehlen also ca. 34 GByte. Wo sind die geblieben?
SQL> SELECT count(*), round(sum(bytes)/1024/1024) MByte
FROM dba_free_space
WHERE tablespace_name = 'XXX' AND bytes < 1024*1024;
COUNT(*) MBYTE
---------- ----------
44132 34517
Listing 2: Fehlende Bereiche im Umfang von 34 GByte
Insgesamt gibt es in dem Tablespace 44132 freie Bereiche mit einer Durchschnittsgröße von weniger als 1 MB und das sind unsere fehlenden 34 GByte. Aber warum werden diese "Häppchen" nicht verwendet? Dafür sehen wir uns einmal die Segmente in dem Tablespace an:
SQL> SELECT owner, segment_name, partition_name, segment_type, max(bytes)/1024/1024 MByte
FROM dba_extents
WHERE Tablespace_name = 'XXX' AND bytes > 1024*1024
GROUP BY owner, segment_name, partition_name, segment_type
ORDER BY 5 desc,1,2,3
FETCH FIRST 10 ROWS ONLY;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE MBYTE
-------- ---------------------- -------------- ------------------ ------
USERABC ODS_AVDSFDDSFDSDFTEN TABLE 64
USERABC RAWSDFSDFSDFR_FK INDEX 64
USERABC SDFSDFSFFDDFSFDS INDEX 64
USERABC RASDFSDFDFSDSFIDX INDEX 64
USERABC RASDFDSFIBBUVWIDX2 INDEX 64
USERABC PARTTABLEABC SYS_P17212 TABLE PARTITION 64
USERABC PARTTABLEABC SYS_P18329 TABLE PARTITION 64
USERABC PARTTABLEABC SYS_P19704 TABLE PARTITION 64
USERABC PARTTABLEABC SYS_P21903 TABLE PARTITION 64
USERABC PARTTABLEABC SYS_P22932 TABLE PARTITION 64
10 rows selected.
Listing 3: Segmente im Tablespace
Die größten Objekte haben Extentgrößen von 64 MByte. Das bedeutet auch, dass diese Segmente keine kleineren Extents mehr anfordern werden. Nur wenn neue Objekte angelegt werden, können die kleinen "Häppchen" genutzt werden - also eher selten.
Lösung
In diesem Fall geht kein Weg an einer Reorganisation vorbei. Meine Empfehlung ist, zwei neue Tablespaces anzulegen. Eines für die großen Objekte mit einer einheitlichen Extentgröße von 10 MByte und einen weiteren für den Rest mit den Standardeinstellungen. Ich würde nicht empfehlen, nur einen Tablespace zu verwenden. Da dann entweder durch "AUTOALLOCATE" das Problem nach einer gewissen Zeit wieder auftritt oder bei "UNIFORM SIZE" zu große Extents für die kleinen Objekte verwendet werden. Dann hat man nichts gewonnen.
Hinweis
Nur damit wir uns nicht missverstehen: einen Einfluss auf die Performance haben die Lücken oder auch die Anzahl von Extents nicht, es geht hier nur um die Platzverschwendung.
