Platz sparen mit Deferred Segment Creation in 11.2.0.2
von Frank Schneede, ORACLE Deutschland B.V. & Co. KG

Mit dem Anlegen von Tabellen wurden bis zur Version Oracle 11g abhängig vom Objektattribut MINEXTENTS automatisch ein oder mehrere initiale Extents erzeugt. Durch die Installation von Anwendungssystemen wie zum Beispiel Standardsoftware (SAP, Oracle EBS, ...) werden sehr viele Objekte in der Datenbank angelegt. Für diese Objekte wird allein durch das Anlegen schon sehr viel Plattenplatz verbraucht, ohne dass bereits Daten im Schema geladen sind. Kunden, deren Systeme bei einem Dienstleister betrieben werden, sind von diesem Umstand in besonderer Weise betroffen. IT Dienstleister messen ihren Aufwand nämlich auch in der Menge des verbrauchten Plattenspeichers und stellen diesen dem Kunden in Rechnung. Aus diesem Grunde ist das Erzeugen vieler leerer Segmente für diese Kunden ein ärgerliches und vor allem teures Unterfangen!

Mit der Datenbankversion Oracle 11gR2 wurde die Funktionalität der deferred segment creation eingeführt. Das bedeutet, dass Tabellen bei ihrer Erzeugung zwar im Data Dictionary registriert werden, Segmente aber erst dann angelegt werden, wenn die Tabelle mit Daten gefüllt wird. Leider war diese Funktionalität noch sehr restriktiv implementiert, so konnten zum Beispiel partitionierte Objekte noch nicht von der deferred segment creation profitieren. Mit dem aktuellen Patchset, der Version 11.2.0.2, wurden einige Verbesserungen implementiert, die in diesem Tipp anhand von Praxisbeispielen vorgestellt werden.

Initialisierungsparameter DEFERRED_SEGMENT_CREATION

Projekte, die mit der aktuellen Datenbankversion 11.2.0.2 starten, können unmittelbar von der deferred segment creation profitieren. Standardmäßig ist die Datenbank nämlich so eingestellt, dass Segmente erst bei der Befüllung mit Daten angelegt werden. Der Initialisierungsparameter DEFERRED_SEGMENT_CREATION, der dynamisch änderbar ist, steuert das Verhalten. 

SQL> SELECT name, type, value, isses_modifiable, issys_modifiable
     FROM  v$parameter
     WHERE name LIKE 'defer%';

NAME                            TYPE VALUE  ISSES ISSYS_MOD
------------------------- ---------- ------ ----- ---------
deferred_segment_creation          1 TRUE   TRUE  IMMEDIATE

SQL>
Über ALTER SESSION oder ALTER SYSTEM Kommandos kann der Anwender, vorausgesetzt er hat die entsprechenden Berechtigungen, das System nach seinen Bedürfnissen einrichten. Ein Punkt, der dabei beachtet werden sollte, ist die Arbeitsweise der Anwendung, die mit nicht existierenden Segmenten zurecht kommen muss. Falls die Anwendung nämlich auf die Data Dictionary View DBA_SEGMENTS zugreift, um aus den Segmentnamen Aktionen abzuleiten, so müssen die Segmente vorhanden sein. Es besteht daher die Möglichkeit, die Segmente auch ohne Befüllen mit Daten anzulegen.

Erweiterung PL/SQL Package dbms_space_admin

Das Erzeugen ("Materialisieren") von Segmenten ohne das gleichzeitige Befüllen mit Daten geschieht mit dem PL/SQL Package DBMS_SPACE_ADMIN - nicht zu verwechseln mit dem PL/SQL Package DBMS_SPACE, das in erster Linie dazu gedacht ist, das Wachstum und den Platzbedarf eines spezifischen Segmentes auszuwerten.

Das Package wird mit SYS Privilegien ausgeführt, das Ausführungsrecht ist für jeden Benutzer (PUBLIC) freigegeben. Der aufrufende Benutzer muss lediglich das Recht besitzen, das betreffende Objekt zu analysieren. Das Package wird genutzt, um Segmente zu erzeugen ("materialisieren") und - was im Fall einer auf 11.2.0.2 aktualisierten Datenbank sicher eher Relevanz besitzt - Segmente zu entfernen. Im folgenden Syntaxbeispiel wird das Vorgehen gezeigt. 
SQL> CREATE TABLE fahrzeuge11g
        ( fgstnr              NUMBER       NOT NULL
        , marke               VARCHAR2(10) NOT NULL
        , modell              VARCHAR2(8)  NOT NULL
        , motor               VARCHAR2(8)  NOT NULL
        , getriebe            VARCHAR2(6)  NOT NULL
        , anz_tueren          NUMBER
        , farbe               VARCHAR2(10)
        , zubehoer            VARCHAR2(2800)
        , auslieferung_kunde  VARCHAR2(6)
        , land_kunde          VARCHAR2(3)
        , prodmonat           VARCHAR2(6)  NOT NULL
        , modelljahr          VARCHAR2(4)  NOT NULL
        )
       TABLESPACE  users
       PARTITION BY LIST (modelljahr)
       SUBPARTITION BY RANGE (land_kunde, marke)
       SUBPARTITION TEMPLATE (
       SUBPARTITION AUTBMW VALUES LESS THAN ('AUT', 'BMW'),
       SUBPARTITION AUTDAM VALUES LESS THAN ('AUT', 'DAIMLER'),
       SUBPARTITION AUTVW  VALUES LESS THAN ('AUT', 'VW'),
       SUBPARTITION AUT    VALUES LESS THAN ('AUT', MAXVALUE),
       SUBPARTITION CHBMW  VALUES LESS THAN ('CH',  'BMW'),
       SUBPARTITION CHDAM  VALUES LESS THAN ('CH',  'DAIMLER'),
       SUBPARTITION CHVW   VALUES LESS THAN ('CH',  'VW'),
       SUBPARTITION CH     VALUES LESS THAN ('CH',  MAXVALUE),
       SUBPARTITION DEUBMW VALUES LESS THAN ('DEU', 'BMW'),
       SUBPARTITION DEUDAM VALUES LESS THAN ('DEU', 'DAIMLER'),
       SUBPARTITION DEUVW  VALUES LESS THAN ('DEU', 'VW'),
       SUBPARTITION DEU    VALUES LESS THAN ('DEU', MAXVALUE),
       SUBPARTITION MAX    VALUES LESS THAN (MAXVALUE, MAXVALUE))
       (PARTITION p2007 VALUES ('2007'),
        PARTITION p2008 VALUES ('2008'),
        PARTITION p2009 VALUES ('2009'),
        PARTITION p2010 VALUES ('2010'),
        PARTITION other VALUES (DEFAULT));

Table created.

SQL> SELECT segment_name
     ,      segment_type
     ,      partition_name
     FROM  dba_segments
     WHERE segment_name LIKE 'FAHRZEUGE%';

no rows selected

SQL> BEGIN
       dbms_space_admin.materialize_deferred_segments
         ( schema_name    => 'SCOTT'
         , table_name     => 'FAHRZEUGE11G'
         , partition_name => 'P2007'
         );
     END;
     /

PL/SQL procedure successfully completed.

SQL> SELECT segment_name
     ,      segment_type
     ,      partition_name
     FROM  dba_segments
     WHERE segment_name LIKE 'FAHRZEUGE%';

SEGMENT_NAME  SEGMENT_TYPE       PARTITION_NAME
------------- ------------------ ------------------------------
FAHRZEUGE11G  TABLE SUBPARTITION P2007_AUT
FAHRZEUGE11G  TABLE SUBPARTITION P2007_AUTBMW
FAHRZEUGE11G  TABLE SUBPARTITION P2007_AUTDAM
FAHRZEUGE11G  TABLE SUBPARTITION P2007_AUTVW
FAHRZEUGE11G  TABLE SUBPARTITION P2007_CH
FAHRZEUGE11G  TABLE SUBPARTITION P2007_CHBMW
FAHRZEUGE11G  TABLE SUBPARTITION P2007_CHDAM
FAHRZEUGE11G  TABLE SUBPARTITION P2007_CHVW
FAHRZEUGE11G  TABLE SUBPARTITION P2007_DEU
FAHRZEUGE11G  TABLE SUBPARTITION P2007_DEUBMW
FAHRZEUGE11G  TABLE SUBPARTITION P2007_DEUDAM
FAHRZEUGE11G  TABLE SUBPARTITION P2007_DEUVW
FAHRZEUGE11G  TABLE SUBPARTITION P2007_MAX

13 rows selected.

SQL>
Das Beispiel zeigt die Funktionsweise des neuen Package und die entscheidende Erweiterung der deferred segment creation. Das verzögerte Anlegen von Segmenten funktioniert mit der aktuellen Version 11.2.0.2 nämlich auch mit partitionierten Tabellen! Die Abfrage der Data Dictionary View DBA_TAB_SUBPARTITIONS zeigt genau das gewünschte Bild. 
SQL> SELECT DISTINCT table_name
     ,      partition_name
     ,      segment_created
     FROM  dba_tab_subpartitions
     WHERE table_name LIKE 'FAHRZEUG%';

TABLE_NAME                     PARTITION_NAME                 SEG
------------------------------ ------------------------------ ---
FAHRZEUGE11G                   P2007                          YES
FAHRZEUGE11G                   P2010                          NO
FAHRZEUGE11G                   OTHER                          NO
FAHRZEUGE11G                   P2009                          NO
FAHRZEUGE11G                   P2008                          NO

5 rows selected.

SQL>
Das Anlegen insbesondere zahlreicher großer Segmente kann viel Zeit kosten, die beispielsweise während eines Ladeprozesses in ein Datawarehouse nicht unbedingt zur Verfügung steht. In einem solchen Fall können für die gesamte Tabelle die Segmente vorbereitet werden. Im weiteren Verlauf des begonnenen Beispiels werden über das PL/SQL Package sämtliche Segmente erzeugt, die anschließend jedoch nicht alle mit Daten befüllt und daher durch den Aufruf des Package DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS entfernt werden können. Die Abfrage des Data Dictionary zeigt, welche Segmente Inhalte (Blöcke) haben und welche leeren Segmente entfernt wurden. 
SQL> BEGIN
       dbms_space_admin.materialize_deferred_segments
         ( schema_name => 'SCOTT'
         , table_name  => 'FAHRZEUGE11G'
         );
     END;
     /

PL/SQL procedure successfully completed.

SQL> SELECT distinct table_name
     ,      partition_name
     ,      segment_created
     FROM  dba_tab_subpartitions
     WHERE table_name LIKE 'FAHRZEUG%';

TABLE_NAME                     PARTITION_NAME                 SEG
------------------------------ ------------------------------ ---
FAHRZEUGE11G                   P2007                          YES
FAHRZEUGE11G                   P2010                          YES
FAHRZEUGE11G                   OTHER                          YES
FAHRZEUGE11G                   P2008                          YES
FAHRZEUGE11G                   P2009                          YES

5 rows selected.

< Tabelle mit Daten befüllen >

SQL> BEGIN
       dbms_space_admin.drop_empty_segments;
     END;
     /

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT table_name
     ,      partition_name
     ,      sum(blocks)
     ,      segment_created
     FROM  dba_tab_subpartitions
     WHERE table_name LIKE 'FAHRZEUG%'
     GROUP BY table_name, partition_name, segment_created
     ORDER BY table_name, segment_created;

TABLE_NAME                     PARTITION_NAME                 SUM(BLOCKS) SEG
------------------------------ ------------------------------ ----------- ---
FAHRZEUGE11G                   OTHER                                    0 NO
FAHRZEUGE11G                   P2007                                    0 NO
FAHRZEUGE11G                   P2008                                    0 NO
FAHRZEUGE11G                   P2009                                    0 NO
FAHRZEUGE11G                   P2010                                    0 NO
FAHRZEUGE11G                   P2007                                  990 YES
FAHRZEUGE11G                   P2008                                  990 YES
FAHRZEUGE11G                   P2010                                  954 YES

8 rows selected.

SQL>
Die nicht befüllten Partitionen sind in erster Linie Überlaufpartitionen, die zur Absicherung des Ladeprozesses vorhanden sein müssen, jedoch idealerweise nie mit Daten gefüllt werden. Durch den Aufruf des PL/SQL Package DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS können durch die Belegung der sprechenden Parameter leere Segmente eines Schemas, einer Tabelle oder einer einzelnen Partition entfernt werden. Wenn der Aufruf von DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS ohne Parameter erfolgt, werden alle leeren Segmente eines Systems mit einem Aufruf entfernt. Diese Methode empfiehlt sich bei Altsystemen mit vielen leeren Segmenten, deren Datenbankversion auf 11.2.0.2 angehoben worden ist.

Neben den eigentlichen Tabellen- oder Partitionssegmenten werden beim Aufruf von DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS auch die zugehörigen Indexsegmente und LOB-Daten sowie deren Indexsegmente entfernt. Die folgenden Einschränkungen der deferred segment creation gelten weiterhin und werden bei der Package Ausführung berücksichtigt:
  • deferred segment creation wird nicht unterstützt für Objekte der Schemata SYS, SYSTEM, PUBLIC, OUTLN, oder XDB
  • deferred segment creation wird nicht unterstützt für index-organized tables, clustered tables, globale und Session-spezifische temporary tables, internal tables, typed tables, AQ tables, external tables
  • deferred segment creation wird nicht unterstützt für Bitmap Join und Domain Indizes
  • deferred segment creation wird nicht unterstützt für nicht-partitionierte Objekte in Dictionary-managed Tablespaces
  • INSERT-Operationen innerhalb von serialisierbaren Transaktionen laufen auf einen Fehler, wenn das Segment noch nicht vorhanden ist.

Entfernen Segmente mit TRUNCATE TABLE

Mit dem SQL-Befehl TRUNCATE TABLE wurden bislang auf sehr schnelle Weise sämliche Daten aus einer Tabelle entfernt und die Highwater Mark auf die Größe zurückgesetzt, die durch den Parameter MINEXTENTS in der Tabelledefiniton vorgegeben ist. Das leere Segment blieb erhalten. In Version 11.2.0.2 wurde die Syntax des Befehls TRUNCATE TABLE erweitert, um auch das letzte Segment freigeben zu können. Durch Angabe der Storage Klausel DROP ALL STORAGE werden die angelegten Segmente geleert und vom Speicher entfernt.

Interne Änderung der Standardgröße der inititalen Extents

Die Verwendung der deferred segment creation ist in der aktuellen Version 11.2.0.2 für partitionierte Tabellen besonders wichtig, denn für partitionierte Tabellen hat sich die Standard Größe der initialen Extents von 64kB auf 8MB geändert! Das folgende Rechenbeispiel unterstreicht das Gesagte. Die in dem oben gezeigten Beispiel angelegte Tabelle hat 65 Partitionen bzw. Subpartitionen. Bisher wäre die initiale Größe des gesamten Objektes ca. 4MB. In der Version 11.2.0.2 würden für das Objekt in Summe 520MB allokiert! Die nachfolgend zu allokierenden Extents haben dann standardmäßig die Größe 1MB.

Exportieren und Importieren von Objekten, die mit deferred segment creation angelegt sind

Das Utility Data Pump Import ist ebenfalls um die Funktionalität der deferred segment creation erweitert worden. Durch die Verwendung des Parameters TRANSFORM kann die SEGMENT CREATION Klausel zum CREATE TABLE Statement exportierter Tabellen hinzugefügt werden. Die Syntax sieht folgendermaßen aus: 
impdp scott TABLES=scott.fahrzeuge11g DIRECTORY=dpump_dir1 DUMPFILE=scott_fzg.dmp TRANSFORM=SEGMENT_CREATION:[y|n]:table
Wird die Transformation auf y gesetzt, so erscheint explizit SEGMENT CREATION DEFERRED oder SEGMENT CREATION IMMEDIATE im CREATE TABLE Statement, je nachdem, wie die Tabelle ursprünglich definiert worden ist. Dieses ist die Standardeinstellung. Wenn SEGMENT_CREATION:n gesetzt ist, so erscheint die SEGMENT CERATION Klausel nicht und die Tabelle wird mit den Standardeinstellungen der Datenbank angelegt. Die Transformation SEGMENT_CREATION ist nur für Tabellen-Objekte gültig!

Benutzer, die auch heute noch auf das mittlerweile abgekündigte Utility export zurückgreifen, sollten beachten, dass Objekte ohne Segmente mit dieser Methode nicht exportiert werden können! Daher sollte spätestens jetzt auf Data Pump umgestellt werden!

Fazit

Die deferred segment creation ist in der Oracle Version 11.2.0.2 sehr sinnvoll erweitert worden und bietet dem Datenbankadministrator viele Möglichkeiten, signifikant Platz und damit auch Kosten einzusparen. Detaillierte Informationen zu den neuen Funktionen rund um den Speicherplatz bietet der Database Administrator's Guide. In folgenden Community Tipps finden Sie weitere nützliche Hinweise, den Speicherbedarf Ihrer Datenbanken zu optimieren.



Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite