Komprimierung in der Oracle Datenbank

Komprimierung
in der Oracle Datenbank
Ulrike Schwinn
Oracle Deutschland GmbH
München
Schlüsselworte:
Index, unstrukturierte und strukturierte Daten, RMAN, Data Pump, 11g Release 2
Einleitung
Schon seit einigen Jahren ist die Komprimierung ein wichtiger Bestandteil der Oracle
Datenbank und wird beständig weiterentwickelt. Dies zeigt sich besonders auch im neuesten
Datenbankrelease 11g mit der Einführung der neuen Option Advanced Compression. Die
Einführung der neuen Techniken führt dazu, dass die Komprimierung nun unabhängig vom
Anwendungs-Workload ist und zusätzlich um die Bereiche unstrukturierte Daten, BackupDaten und Netzwerk-Komprimierung für Data Guard Installationen erweitert wurde. Dieser
Artikel gibt einen Überblick über alle zur Verfügung stehenden Komprimierungsverfahren für
Tabellen und Indizes und illustriert ihre Handhabung mit zusätzlichen Tipps und Tricks.
Komprimierung von Indizes
Komprimierung im Index findet seit jeher in den sogenannten Bitmap Indizes statt, die
speziell in Warehouse-Anwendungen bei Indizes mit geringer Kardinalität zum Einsatz
kommen. Bitmap Indizes speichern eine Bitmap für jeden Index Schlüssel statt einer Liste
von ROWIDs. Jedes Bit in dieser Bitmap adressiert eine ROWID und somit eine Zeile in der
Tabelle. Bitmap Indizes benötigen sehr geringen Speicherbedarf. Je nach Art der Anwendung,
speziell bei Warehouse-typischen Abfragen ist die Verwendung von Bitmap Indizes von
Vorteil. Unterstützung beim Auffindung der richtigen Indizes speziell auch Bitmap Indizes
kann der SQL Access Advisor liefern. Das Interface dazu liefert das Package
DBMS_ADVISOR oder die graphische Implementierung im Enterprise Managers.
Auch bei vermehrt schreibenden Zugriffen wie in OLTP Betrieben üblich ist, können
komprimierte Indizes verwendet werden. Die sogenannte Index Key Compression ist bereits
ab Oracle 8i verfügbar und steht für B*tree Indizes und IOT’s (Index Organized Table) zur
Verfügung. Das Prinzip der Index Key Compression beruht dabei auf der Eliminierung von
sich wiederholenden Schlüssel-Werten (auch Präfix genannt) eines „nonunique single
column“ Index bzw. eines „multicolumn“ Index. Zusammengesetzte Schlüssel in einem Index
werden dabei in einen Präfix- und einen Suffixanteil unterteilt, wobei der Suffixanteil den
eindeutigen Teil des Index-Schlüssels repräsentiert. Wenn sich Schlüsselwerte im Präfix-Teil
des Indizes wiederholen, so werden diese Werte nur einmal gespeichert und vom Suffix
referenziert. Präfix und Suffix befinden sich dabei grundsätzlich im gleichen Datenblock.
Dies bewirkt eine Reduzierung der „Index Leaf Pages“ und damit der Anzahl der I/O
Operationen bei einem Indexzugriff. Die Komprimierung kann beim Erzeugen des Index oder
mit einem ALTER INDEX REBUILD-Kommando eingestellt werden (siehe Listing 1). Die
Option COMPRESS gibt dabei die Anzahl der Präfixspalten an, die komprimiert werden.
CREATE INDEX t1_ci_1 ON t1(col1, col2, col3, col4) COMPRESS 2;
Listing 1: Erzeugen eines komprimierten Index mit Präfixspalten col1 und col2
Die Höhe der Komprimierungsrate des Index kann stark variieren und ist z.B. abhängig von
der richtigen Anzahl der komprimierten Spalten, den Werteausprägungen und der Anordnung
der Spalten im Index. Falls das Umsortieren der Spalten im Index möglich ist, kann dies zu
höheren Komprimierungsraten führen. Um zu beurteilen, ob und welche Indexkomprimierung
für die entsprechenden Indexkandidaten sinnvoll ist, eignet sich zum Beispiel die Analyse des
Index mit ANALYZE INDEX...VALIDATE STRUCTURE.
Komprimierung von Tabellen mit strukturierten Daten
Bereits mit Oracle Version 9.2 war es möglich relationale Tabellendaten zu komprimieren.
Bei der Komprimierung der Daten werden Mehrfacheinträge im Datenblock nur einmal
gespeichert. Die sich wiederholenden Werte werden in einer sogenannten „Symbol Table“ auf
Blockebene gespeichert und durch einen Pointer im Datenteil des Blocks adressiert. Dieser
Komprimierungsalgorithmus ist sehr effizient. Durch die Tabellenkomprimierung kann eine
größere Anzahl Zeilen in einem Block gespeichert werden, daher werden weniger „Buffer
Get“ -Operationen bzw. I/Os durchgeführt. Diese Tatsache führt dazu, dass nicht nur der
Speicherplatzbedarf auf der Platte und im Memory verringert wird, sondern auch die
Performance der Tabellenzugriffe erhöht werden kann. Des Weiteren existieren keine
funktionalen Einschränkungen bei Nutzung von Datenbankfunktionalitäten wie z.B. Oracle
Streams Replication.
Seit Oracle Version 9.2 kann diese Table Compression speziell im Data Warehouse-Bereich
für Bulk Load Operationen genutzt werden. Dazu gehören folgende Operationen:
• Direct Path Load beim SQL*Loader
• CREATE TABLE AS SELECT
• Paralleler INSERT
• Serieller INSERT mit APPEND Hint mit Subquery Klausel
Konventionelles DML wird dabei allerdings nicht komprimiert werden. Zusätzlich gibt es
einige Einschränkungen, die beachtet werden sollten: Operationen wie TABLE SHRINK,
ADD COLUMN mit Default Werten bzw. DROP COLUMN auf komprimierten Tabellen sind
nicht erlaubt.
Mit Oracle 11g steht diese Funktionalität weiterhin zur Verfügung und wurde um ein weiteres
Tabellen-Komprimierungsverfahren erweitert. Zusätzlich mit
der Oracle Advanced
Compression Option werden nun alle DML-Operationen sowie ADD und DROP COLUMNOperationen unterstützt. Dies bedeutet, dass die Einschränkung auf Bulk Load Operationen
wegfällt, und Komprimierung ohne Rücksicht auf die Art der Ladevorgänge garantiert werden
kann. Diese neue Komprimierungsmethode wird auch als OLTP- oder FOR ALL
OPERATIONS- Komprimierung genannt.
Zu welchem Zeitpunkt wird nun der Block mit OLTP Komprimierung komprimiert? Die
Blöcke werden nicht nach jeder Schreiboperation komprimiert, sondern im Batch-Mode. Dies
bedeutet, dass ein neuer Block so lange unkomprimiert bleibt bis die Daten die PCTFREEGrenze erreicht haben (siehe Abbildung 1).
Abbildung 1: Komprimierter Block nach Erreichen der PCTFREE-Grenze
Generell kann Table Compression entweder auf Tablespace-, Tabellen- oder Partitionsebene
mit dem entsprechendem CREATE-Kommando eingeschaltet werden. Auf Tablespaceebene
kann mit dem Keyword DEFAULT und der anschließenden Angabe der Komprimierungsart
festgelegt werden, dass alle generierten Tabellen ohne zusätzliche Angabe in diesem
Tablespace komprimiert abgelegt werden. Tabellen- und Partitionsklauseln können dieses
Verhalten überschreiben. Möchte man hingegen die Inhalte von existierenden
unkomprimierten Tabellen in komprimierte Tabellen umwandeln, kann man die Daten mit
ALTER TABLE MOVE COMPRESS FOR bzw. ALTER TABLE MOVE PARTITION
COMPRESS FOR Kommando in einer Einschritt-Operation umschichten und gleichzeitig
komprimieren. Allerdings erfordert dies eine exklusive Sperre auf der Tabelle. Falls dies
online ohne Sperren erfolgen soll, kann man mit dem Package DBMS_REDEFINITION
arbeiten. Die Komprimierungseinstellungen der einzelnen Datenbanktabellen und Partitionen
lassen sich über die zusätzliche Spalten COMPRESSION und COMPRESS_FOR in
DBA_TABLES und DBA_TAB_PARTITIONS überprüfen.
Je nach Art der Daten (z.B. Anteil an redundanten Informationen), Datenbank-Blockgröße
und Ladevorgang können die Komprimierungsraten stark variieren. So ist es vorteilhaft mit
einer großen Datenbank-Blockgröße bzw. mit vorsortierten Daten zu arbeiten, um die
Komprimierungsrate zu erhöhen. Die Praxis zeigt, dass im Data Warehouse-Bereich bei
Tabellen mit hohen Redundanzwerten Komprimierungsraten von 8 erreicht werden können.
Das bedeutet, dass die unkomprimierte Tabelle 8 mal größer als die komprimierte Tabelle ist.
Im White Paper der Winter Corporation (siehe "Oracle's Top Ten Features For Large Scale
Data Warehousing" vom März 2007) wird die Table Compression zu den Top 10 Features
gezählt und wird als „essential feature for a data warehousing platform“ bezeichnet. Dort
wird sogar von einer Beispielapplikation berichtet, die eine Komprimierungsrate von 12
erreichte.
Komprimierung von Tabellen mit unstrukturierten Daten
Unstrukturierte Daten vom Datentyp XML, CLOB und BLOB sind in der Regel sehr
speicherintensiv. Aus diesem Grunde ist eine Komprimierung dieser Daten sehr sinnvoll. Mit
Oracle Database 11g sind Oracle SecureFiles als eine optimierte Speicherform für
unstrukturierte Daten eingeführt worden. Eine Eigenschaft von Oracle SecureFiles ist die
Möglichkeit Komprimierung einzuschalten. Dies erfordert allerdings den Einsatz der
Advanced Compression Option. Dabei sind folgende Einstellungen bei der Komprimierung
möglich:
• DEDUPLICATE: LOBs mit identischem Inhalt werden physikalisch nur einmalig
gespeichert. Diese Einstellung ist besonders sinnvoll bei der Nutzung von großen LOBs,
die mit DISABLE STORAGE IN ROW abgespeichert werden.
• COMPRESS HIGH (bzw. MEDIUM): Reduzierung des Speicherbedarfs von LOBs durch
Komprimierung. Diese Komprimierung wird durch einen Standardalgorithmus
durchgeführt und kann wahlweise mit einer hohen bzw. mittleren Komprimierungsrate
durchgeführt werden. Die LOB-Komprimierung mit Parametereinstellung HIGH hat dabei
einen höheren CPU-Bedarf als die Komprimierung der LOBs mit der SecureFile
Standardkomprimierung MEDIUM.
Die LOB-Komprimierung ist dabei unabhängig von der Tabellenkomprimierung und wird
beim CREATE TABLE oder ALTER TABLE separat über die SecureFile LOB-StorageKlausel angegeben (siehe Listing 2). Je nach gespeichertem Format HTML, Text, ASCII,
PDF, GIF usw. sind die Komprimierungsraten unterschiedlich. Zweistellige Werte bei der
Komprimierungsrate sind dabei allerdings nichts Ungewöhnliches.
CREATE TABLE nachrichten_text (dok_id NUMBER,...,text_info CLOB)
LOB (text_info) STORE AS SECUREFILE
(DEDUPLICATE COMPRESS HIGH DISABLE STORAGE IN ROW)
Listing 2: LOB-Storage Klausel für DEDUPLICATE und COMPRESSION HIGH
Speziell bei I/O intensiven Applikationen kann der Einsatz von Komprimierung von großem
Vorteil sein. Ein Beispiel dafür liefert die 11g Installation bei der FIZ CHEMIE in Berlin, die
eine Performance-Steigerung und zusätzlich Speicherplatz-Einsparung erreichen konnten.
Dabei wurde mittels einer Markup-Abfrage, die summarisch betrachtet das
ressourcenintensivste Statement der getesteten Anwendung darstellt, das Laufzeitverhalten
anhand einer Tabelle mit ca. 200000 Zeilen analysiert. Allein durch Einsatz der I/O
optimierten SecureFiles wurde bereits eine Performance-Steigerung von 30% erzielt. In
Verbindung mit Advanced Compression ergab die Testreihe dann eine radikale Verbesserung
der Laufzeit. Diese wurde von ursprünglich 59 Minuten auf 12 Minuten reduziert – und das
bei einer gleichzeitigen Verringerung des Speicherplatzes auf 25% des ursprünglichen
Speicherbedarfs (siehe Quellen).
Um eine gute Komprimierungsrate zu erzielen, ist die richtige Wahl der Blockgröße
erforderlich. Bei Large Objects lassen sich mithilfe der GETLENGTH- Funktion
unkomprimierte LOB-Größen abfragen. Handelt es sich bei den Daten nicht schon um stark
komprimierte Formate wie beispielsweise das GIF-Format kann eine Halbierung der
durchschnittlichen LOB-Größe die ideale Blockgröße für die komprimierten Lobs darstellen.
Um das Ergebnis zu evaluieren, kann man entweder USER_SEGMENTS (siehe Listing 3) oder
eine blockgenaue detaillierte Speicheraufteilung mit dem Package DBMS_SPACE erhalten.
SELECT bytes/(1024*1024) groesse FROM user_segments
WHERE segment_name='SECURE_TABLE'
UNION
SELECT bytes/(1024*1024) FROM user_segments
WHERE segment_name IN
(SELECT segment_name FROM user_lobs WHERE table_name='SECURE_TABLE')
GROESSE
---------34
3463
SELECT bytes/(1024*1024) groesse FROM user_segments
WHERE segment_name='COMPRESS_TABLE'
UNION
SELECT bytes/(1024*1024) FROM user_segments
WHERE segment_name IN
(SELECT segment_name FROM user_lobs WHERE table_name='COMPRESS_TABLE');
GROESSE
---------19
472.25
Listing 3: Komprimierte (COMPRESS_TABLE) vs. unkomprimierte (SECURE_TABLE) LOB Speicherung
Wie migriert man diesen Datentyp? Da kein ALTER TABLE MODIFY-Kommando zur
Migration zur Verfügung steht, bietet sich entweder eine Online-Migration mit dem Paket
DBMS_REDEFINITION oder eine Neuanlage der Tabelle und Kopie der Daten an.
Backup Komprimierung mit Datapump und RMAN
Nicht nur die Größe des aktuellen Datenbestands in der Datenbank effizient abzuspeichern
muss ein Ziel bei der Speicherverwaltung sein, sondern auch die Backup-Größe optimal zu
verwalten. Aus diesem Grund wird in 11g eine Komprimierung von Tabellendaten beim
Datapump Export und eine verbesserte RMAN Komprimierung ermöglicht.
Der Oracle Recovery Manager (kurz RMAN) ist das Werkzeuge für Backup und Recovery
von Oracle Datenbanken, das optimale Performance und effizienten Platzverbrauch durch File
Multiplexing und zusätzlicher Komprimierung ermöglichen kann. Seit Oracle Database 10g
ist dabei eine Backup Compression mit dem BZIP2 Algorithmus möglich. So können
Backupsets bevor sie auf die Platte geschrieben werden komprimiert werden. Bei der Nutzung
dieses Backups ist keine zusätzlicher separater Dekomprimierungsschritt mehr notwendig.
Allerdings konnte sich dabei die Dauer des Backups um ein Vielfaches verlängern.
Aus diesem Grund wurde in 11g mit der Advanced Compression Option ein weiterer
Algorithmus, der ZLIP-Algorithmus verfügbar gemacht, um die Geschwindigkeit beim
Backup zu erhöhen. Veröffentlichte White Paper (siehe Quellen) versprechen 40%
Performance bei Einbußen von weniger als 20% Compression Ratio. Eigene Tests haben
beim Vergleich der beiden Algorithmen sogar 60% Performance Steigerung von
beispielsweise 56 auf 22 Minuten erzielt. Ein Trade-off wie bei der Komprimierung im White
Paper angedeutet wird, konnte im Testfall nicht beobachtet werden.
Auch der Datapump Export profitiert von neuen Komprimierungsmöglichkeiten. Bislang
wurden automatisch nur die Metadaten komprimiert; mit 11g und der Advanced Compression
Option können nun auch die Tabellendaten komprimiert werden. Beim Datapump Export
unter Angabe der Option compression=all werden automatisch die Tabellendaten
komprimiert; beim Import ist keine weitere Angabe bzw. kein weiterer
Dekomprimierungsschritt nötig. Dabei bleibt die Komprimierung vollständig Applikationstransparent und somit gibt es keine Einschränkungen bei der Datapump-Funktionalität. Auch
hier sind Komprimierungen von 75% keine Besonderheit und können mit Werkzeugen wie
GNU Zip verglichen werden. Folgendes Listing zeigt die Nutzung mit dem Datapump Export.
expdp compression=all directory=dumpdir dumpfile=back_comp1.dmp ....
Listing 4: Tabellenkomprimierung mit Datapump Export
Neuigkeiten in 11g Release 2
In Oracle Database 11g Release 2 sind zusätzlich einige hilfreiche Neuigkeiten und
Ergänzungen hinzugefügt worden. Der folgende Abschnitt zeigt einige neue Features im
Bereich Komprimierung auf.
Die Syntax zum Einstellen der Table Compression ist sinnvoll verkürzt worden. So zeigt das
Keyword COMPRESS die Bulk Load Komprimierung und COMPRESS FOR OLTP die
Komprimierung für FOR ALL OPERATIONS Komprimierung an. Dies gilt natürlich auch für
die Nutzung von Tablespace-Einstellungen wie folgendes Listing zeigt.
CREATE TABLESPACE ... DEFAULT COMPRESS FOR OLTP;
Listing 5: Default Tablespace Einstellung für 11g OLTP Komprimierung
Indirekte Auswirkung hat sicherlich auch die Erweiterung der Hints um den Wert
APPEND_VALUES. Mit dem neuen Hint können nun DIRECT LOAD Operationen auf die
VALUES Klausel ausgedehnt werden und somit können auch Zeilen mit der Hinterweiterung
komprimiert eingefügt werden. Besonders profitieren können zum Beispiel umfangreiche
Ladevorgänge in PL/SQL Anwendungen, die mit einer FORALL Schleife und einem
INSERT VALUES Kommando ausgestattet sind. Folgendes Listing zeigt ein Beispiel der
Nutzung:
INSERT /*+APPEND_VALUES*/ INTO comp_tab VALUES (col1,col2,...);
Listing 6: Nutzung des APPEND_VALUES Hint
Unterstützung beim Erstellen der Komprimierungsratio für Tabellen und Partitionen liefert
das neue Package DBMS_COMPRESSION, das standardmäßig in der Datenbank enthalten ist.
Automatisch werden bei der Nutzung temporäre Objekte erstellt und analysiert und nehmen
dem DBA die Aufgabe ab, eigene Testobjekte zur Analyse zu erstellen. So kann man zum
Beispiel durch Angabe der ROWID herausfinden, ob eine Zeile komprimiert oder nicht
abgespeichert worden ist (siehe Listing 7).
SELECT
dbms_compression.get_compression_type('SH','NOCOMP','AAASOXAAEAAAANIAAC')
AS komprimiert FROM dual;
KOMPRIMIERT
---------------------1
Listing 7: Nutzung des get_compression_type Funktion
Der Komprimierungstyp 1 gibt dabei an, dass diese Zeile nicht komprimiert abgespeichert
wurde. Weitere Informationen dazu findet sich im Oracle® Database PL/SQL Packages and
Types Reference 11g Release 2 (11.2). Möchte man die Komprimierungsratio von Tabellen
berechnen lassen, eignet sich die Prozedur compression_ratio. Sie gibt die Ratio von
strukturierten partitionierten oder nichtpartitionierten Tabellen aus. Der folgende Test ergibt
eine Komprimierungsratio von 1.5 für die Partition 'COSTS_Q1_1998' beim Einsatz von
OLTP Komprimierung (d.h. COMPTYPE=2).
SQL> declare
2
b_cmp
PLS_INTEGER;
3
b_uncmp
PLS_INTEGER;
4
row_cmp
PLS_INTEGER;
5
row_uncmp
PLS_INTEGER;
6
cmp_ratio
NUMBER;
7
cmp_str
varchar2(200);
8 begin
9
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (SCRATCHTBSNAME =>'USERS',
OWNNAME=>'SH',TABNAME=> 'COSTS',PARTNAME=>'COSTS_Q1_1998',COMPTYPE=>2,
BLKCNT_CMP=>b_cmp,BLKCNT_UNCMP=>b_uncmp,ROW_CMP=>row_cmp,ROW_UNCMP=>
row_uncmp,CMP_RATIO=>cmp_ratio,COMPTYPE_STR=>cmp_str);
10 dbms_output.put_line('ratio
=> '|| cmp_ratio);
11 dbms_output.put_line('Compressiontyp => '|| cmp_str);
12 end;
13 /
ratio
=> 1.53846153846153846153846153846153846154
Compressiontyp => "Compress For OLTP"
Listing 8: Nutzung des get_compression_ratio Prozedur
Die Redo Transport Komprimierung beim Data Guard, die ebenfalls eine Eigenschaft der
Advanced Compression Option ist und in diesem Artikel noch nicht angesprochen worden ist,
ist nicht länger limitiert auf die Redo Gap Komprimierung der Daten. Wenn in Oracle
Database Release 2 Komprimierung für eine Destination eingeschaltet ist, werden alle
Redodaten, die zu der Destination gesendet automatisch komprimiert.
Ein zusätzlicher Algorithmus im SECUREFILE Bereich, der mit LOW angezeigt wird, führt
nun zu schnellerer Komprimierung und Dekomprimierung. Beim RMAN BACKUP sind
ebenfalls zusätzliche Algorithmen hinzugefügt worden um je nach Systemumgebung die
Performance der Komprimierung zu erhöhen. Auskunft über die zur Verfügung stehenden
Algorithmen gibt die View V$RMAN_COMPRESSION_ALGORITHM (siehe Listing 9).
Ausser für den Algorithmus BASIC muss die Advanced Compression Option eingeschaltet
sein.
SELECT algorithm_name name, algorithm_description, algorithm_compatibility
compatibility
FROM v$rman_compression_algorithm;
NAME
ALGORITHM_DESCRIPTION
COMPATIBILITY
-------------- ------------------------------------------- ---------------BASIC
good compression ratio
9.2.0.0.0
LOW
maximum possible compression speed
11.2.0.0.0
MEDIUM
balance between speed and compression ratio
11.0.0.0.0
HIGH
maximum possible compression ratio
11.2.0.0.0
4 rows selected
Listing 9: Algorithmen für komprimierte RMAN Backups
Quellen
• DBA Community :
o http://www.oracle.com/global/de/community/dbadmin/index.html
• Tipp zu Securefile:
o http://apex.oracle.com/url/securefile
• Tipp zu Advanced Compression
o http://apex.oracle.com/url/adv_compress
• Tipp zur Funktionsweise von DBMS_REDEFINITION
o http://apex.oracle.com/url/dbms_redefinition
• FIZ Referenz Story
o http://apex.oracle.com/url/fiz_case
• White Paper zu Oracle Database 11g Data Repair Technologies
o http://www.oracle.com/technology/deploy/availability/pdf/twp_data_repair_11
gr1.pdf
Kontaktadresse:
Ulrike Schwinn
Riesstr. 25
D-80992 München
Telefon:
E-Mail
+49(0)8914301865
[email protected]