Advanced Compression mit Oracle 11g in der Praxis
von Frank Schneede, ORACLE Deutschland GmbH

Untersuchungen haben gezeigt, dass eine der größten Herausforderungen im Betrieb von Datawarehouses der Umgang mit stark wachsenden Datenmengen ist. Das Speichern großer Datenmengen hat dabei nicht nur einen Kosten-, sondern auch einen Performanceaspekt. Daher ist es bereits seit der Oracle Datenbankversion 9.2 in der Enterprise Edition möglich, das Datenvolumen durch den Einsatz von Komprimierung zu reduzieren. Da der Komprimierungsalgorithmus nur einen geringen Overhead erzeugt, war es bereits mit dieser Version grundsätzlich leicht umsetzbar, Plattenspeicher einzusparen und gleichzeitig bei I/O-intensiven Abfragen durch die Minimierung der Anzahl zu lesender Blöcke die Auswerteperformance zu steigern.

Eine Komprimierung der Daten war bis dahin allerdings nur bei folgenden Operationen nutzbar:

  • BULK-Insert Operationen (auch DIRECT Load Operationen genannt)
  • Manuelles Umkopieren der Tabelle
Diese Restriktionen haben dazu geführt, dass Komprimierung nicht in dem gewünschten Umfang eingesetzt wurde, weil z. B. der Ladevorgang in das Datawarehouse keine BULK-Operationen erlaubte oder eine nachträgliche Komprimierung durch Umkopieren der Daten zu zeitaufwändig erschien. Diese Situation hat sich durch die Advanced Compression Option in Oracle 11g entscheidend gebessert, indem nun eine Komprimierung unabhängig vom Anwendungs-Workload erfolgen kann und zusätzlich auch auf die Bereiche andere Daten wie
  • unstrukturierte Daten (mit SECUREFILE Datentyp)
  • Backup-Daten (bei Nutzung des RMAN- und Datapump-Werkzeug)
  • Netzwerk-Komprimierung für Data Guard
erweitert wurde.

In folgendem Tipp wird die Einfachheit des Einsatzes der Oracle 11g Advanced Compression Option für die Tabellenkomprimierung anhand zweier kleiner Beispiele demonstriert.

In den hier vorliegenden Beispielen wird von einer Datenbank mit UTF-8 Characterset und einer Blockgrösse von 8192 Bytes, also in Datawarehouse-Implementierungen üblichen 8k, ausgegangen. Der Grad der möglichen Platzersparnis durch Komprimierung ist natürlich von der Art der gespeicherten Daten abhängig. In dem hier vorgestellten Beispiel wird eine kleine Testtabelle angelegt. Im Data Dictionary ist ersichtlich, dass diese Tabelle zur Zeit nicht komprimiert wird: 
SQL> create table city (c varchar2(20));

Tabelle wurde erstellt.

SQL> select table_name
     ,      compression
     ,      compress_for
     from   user_tables;

TABLE_NAME COMPRESSION     COMPRESS_FOR
---------- --------------- --------------------
CITY       DISABLED

1 Zeile wurde ausgewählt.

SQL>

Im ersten Schritt wird die angelegte Testtabelle nun mit Daten versorgt. Man sieht durch Abfrage des Data Dictionary, dass die gesamten eingefügten Datensätze in einen einzigen Block hinein passen: 
SQL> begin
       for i in 1..484 loop
         insert into city values('NUERNBERG');
         commit;
       end loop;
     end;
     /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select dbms_rowid.rowid_block_number(rowid) Block_No
     ,      count(c)                             Anzahl_Saetze
     from   city
     group by dbms_rowid.rowid_block_number(rowid);

BLOCK_NO ANZAHL_SAETZE
-------- -------------
    8350           484

1 Zeile wurde ausgewählt.

SQL>

Durch das Hinzufügen eines einzelnen weiteren Datensatzes muss ein neuer Block allokiert werden: 
SQL> insert into city values('STUTTGART');

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select dbms_rowid.rowid_block_number(rowid) Block_No
     ,      count(c)                             Anzahl_Saetze
     from   city
     group by dbms_rowid.rowid_block_number(rowid);

BLOCK_NO ANZAHL_SAETZE
-------- -------------
    8350           484
    8351             1

2 Zeilen ausgewählt.

SQL>

Im nächsten Schritt soll die Tabelle auf Komprimierung umgestellt werden, wobei eine Komprimierung bei jedem Workload erfolgen soll. Die Umstellung lässt sich sofort im Data Dictionary verifizieren: 
SQL> alter table city compress for all operations;

Tabelle wurde geändert.

SQL> select table_name
     ,      compression
     ,      compress_for
     from   user_tables;

TABLE_NAME COMPRESSION     COMPRESS_FOR
---------- --------------- --------------------
CITY       ENABLED         FOR ALL OPERATIONS

1 Zeile wurde ausgewählt.

SQL>

Der Oracle Enterprise Manager 11g Database Control verfügt ebenfalls über eine Anzeige, in der die Komprimierungseinstellungen für Datenbankobjekte kontrolliert und ggf. verändert werden können:

Für eine größere Ansicht auf das Bild klicken


Anschließend werden weitere Daten in die Tabelle eingefügt und die Auswirkung im Data Dictionary kontrolliert. Man erkennt, dass der bereits allokierte erste Block dadurch nicht verändert wird. Die Komprimierung erfolgt lediglich für den nach der Umstellung noch nicht vollständig gefüllten Block, der durch die Komprimierung allerdings wesentlich mehr Daten aufnehmen kann. Die hier erreichte Einsparung berechnet sich durch die Formel saving = round((uncompressed - compressed) * 100 / uncompressed), es ergibt sich also konkret round((640 - 480) * 100 / 640) = 24% Platzersparnis: 
SQL> begin
       for i in 1..639 loop
         insert into city values('STUTTGART');
         commit;
       end loop;
     end;
     /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select dbms_rowid.rowid_block_number(rowid) Block_No
     ,      count(c)                             Anzahl_Saetze
     from   city
     group by dbms_rowid.rowid_block_number(rowid);

BLOCK_NO ANZAHL_SAETZE
-------- -------------
    8350           484
    8351           640

2 Zeilen ausgewählt.

SQL>

Das Beispiel zeigt, dass eine Komprimierung nur beim Füllen eines Blockes erfolgt, einmal vollständig gefüllte Blöcke werden nicht mehr verändert. Es muss also durch eine DELETE-Operation Platz im Block geschaffen werden: 
SQL> delete city
     where  c='NUERNBERG'
     and    rownum <400;

399 Zeilen wurden gelöscht.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL>
SQL> select dbms_rowid.rowid_block_number(rowid) Block_No
     ,      count(c)                             Anzahl_Saetze
     from   city
     group by dbms_rowid.rowid_block_number(rowid);

BLOCK_NO ANZAHL_SAETZE
-------- -------------
    8350            85
    8351           640

2 Zeilen ausgewählt.

Durch das Einfügen von weiteren Daten ist es nun möglich, dass der erste Block ebenfalls komprimiert wird und ebenfalls mehr Daten aufnehmen kann: 
SQL> begin
       for i in 1..555 loop
         insert into city values('NUERNBERG');
         commit;
       end loop;
     end;
     /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select dbms_rowid.rowid_block_number(rowid) Block_No
     ,      count(c)                             Anzahl_Saetze
     from   city
     group by dbms_rowid.rowid_block_number(rowid);

BLOCK_NO ANZAHL_SAETZE
-------- -------------
    8350           640
    8351           640

2 Zeilen ausgewählt.

SQL>

Das obige Beispiel zeigt sehr plastisch die Arbeitsweise der Advanced Compression Option und deren Effekt in Bezug auf die Platzersparnis. Sobald in einer Tabelle noch Bewegung ist, greift die Komprimierung - wenn die Tabelle oder Partitionen davon jedoch historischen Inhalt haben und damit statisch sind, so ist hier ein Administratoreingriff, zum Beispiel Umkopieren der Tabelle bzw. Partition, erforderlich, um die gewünschte Platzersparnis zu realisieren.

Im zweiten Beispiel wird der Effekt des Einsatzes der Advanced Compression Option auf I/O-Durchsatz und somit auf die Performance untersucht. Hierzu werden zwei in der Struktur identische Tabellen angelegt, die erste komprimiert, die zweite nicht: 
SQL> -- Create compressed test table
SQL> CREATE TABLE accounts_comp ( acc_no       INTEGER      NOT NULL
     ,                            first_name   VARCHAR2(30) NOT NULL
     ,                            last_name    VARCHAR2(30) NOT NULL
     ,                            acc_type     VARCHAR2(15)
     ,                            folio_id     NUMBER
     ,                            sub_acc_type VARCHAR2(1)
     ,                            acc_open_dt  DATE
     ,                            acc_mgr_id   NUMBER
     )
    COMPRESS FOR ALL operations;

Tabelle wurde erstellt.

SQL> -- Create regular test table
SQL> CREATE TABLE accounts_reg AS SELECT * FROM accounts_comp UNCOMPRESS;

Tabelle wurde erstellt.

SQL>

Die beiden Tabellen werden nacheinander über ein Skript mit Testdaten versorgt. Das Skript ist so gestaltet, dass der Name der Tabelle, bzw. hier die Erweiterung REG oder COMP durch eine Substitutionsvariable versogt wird: 
SQL> -- INSERT into regular table
SQL> @ins_acc.sql REG;
SQL> BEGIN
  2    FOR l_acc_no IN 1..1000000 LOOP
  3      INSERT INTO accounts_®_or_comp.
  4      VALUES ( l_acc_no
  5             , -- First Name
  6               DECODE ( FLOOR(DBMS_RANDOM.VALUE(1,21)), 1, 'Alan'
  7                                                      , 2, 'Alan'
  8                                                      , 3, 'Barbara'
  9                                                      , 4, 'Barbara'
 10                                                      , 5, 'Charles'
 11                                                      , 6, 'David'
 12                                                      , 7, 'Ellen'
 13                                                      , 8, 'Ellen'
 14                                                      , 9, 'Ellen'
 15                                                      , 10, 'Frank'
 16                                                      , 11, 'Frank'
 17                                                      , 12, 'Frank'
 18                                                      , 13, 'George'
 19                                                      , 14, 'George'
 20                                                      , 15, 'George'
 21                                                      , 16, 'Hillary'
 22                                                      , 17, 'Iris'
 23                                                      , 18, 'Iris'
 24                                                      , 19, 'Josh'
 25                                                      , 20, 'Josh'
 26                                                      , 'XXX'
 27                      )
 28             , -- Last Name
 29               DECODE ( FLOOR(DBMS_RANDOM.VALUE(1,5)), 1,'Smith'
 30                                                     , DBMS_RANDOM.STRING ('A',DBMS_RANDOM.VALUE(4,30))
 31                      )
 32             , -- Account Type
 33               DECODE ( FLOOR(DBMS_RANDOM.VALUE(1,5)), 1,'S'
 34                                                     , 2,'C'
 35                                                     , 3,'M'
 36                                                     , 4,'D'
 37                                                     ,'X'
 38                     )
 39             , -- Folio ID
 40               CASE
 41                 WHEN DBMS_RANDOM.VALUE (1,100) < 51 THEN
 42                   NULL
 43                 ELSE
 44                   l_acc_no + FLOOR(DBMS_RANDOM.VALUE(1,100))
 45               END
 46             , -- Sub Acc Type
 47               CASE
 48                 WHEN DBMS_RANDOM.VALUE (1,100) < 76 THEN
 49                   NULL
 50                 ELSE
 51                   DECODE (FLOOR(DBMS_RANDOM.VALUE (1,6)), 1,'S'
 52                                                         , 2,'C'
 53                                                         , 3,'C'
 54                                                         , 4,'C'
 55                                                         , 5,'C'
 56                                                         ,null
 57                          )
 58               END
 59             , -- Acc Opening Date
 60               SYSDATE - DBMS_RANDOM.VALUE(1,500)
 61             , -- Account Manager ID
 62               DECODE ( FLOOR(DBMS_RANDOM.VALUE (1,11)), 1, 1
 63                                                       , 2, 1
 64                                                       , 3, 1
 65                                                       , 4, 1
 66                                                       , 5, 2
 67                                                       , 6, 3
 68                                                       , 7, 4
 69                                                       , 8, 5
 70                                                       , 9, 5
 71                                                       ,10, 5
 72                                                       ,0
 73                      )
 74              );
 75    END LOOP;
 76    COMMIT;
 77  END;
 78  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL>
...
SQL> -- INSERT into compressed table again
SQL> @ins_acc.sql COMP;
...
SQL>

Nach einem ersten Durchlauf kann man den Füllgrad der Tabellen leicht abfragen, aufgrund der Charakteristik der Daten erhält man hier allerdings nach der bereits oben beschriebenen Berechnungsformel (saving = round((6912 - 5632) * 100 / 6912)) nur eine Platzeinsparung von 19%, diese Rate erhöht sich nach einer weiteren Befüllung beider Tabellen leicht auf 21% (saving = round((14336 - 11264) * 100 / 14336)): 
SQL> -- check space usage
SQL> column segment_name  format a20
SQL> column blocks        format 99999
SQL>
SQL> SELECT segment_name
     ,      blocks
     FROM   user_segments
     WHERE  segment_name LIKE 'ACCOUNTS_%';

SEGMENT_NAME         BLOCKS
-------------------- ------
ACCOUNTS_COMP          5632
ACCOUNTS_REG           6912

SQL>
SQL> -- INSERT into regular table again
SQL> @ins_acc.sql REG;
...
SQL> -- INSERT into compressed table again
SQL> @ins_acc.sql COMP;
...
SQL> SELECT segment_name
     ,      blocks
     FROM   user_segments
     WHERE  segment_name LIKE 'ACCOUNTS_%';

SEGMENT_NAME         BLOCKS
-------------------- ------
ACCOUNTS_COMP         11264
ACCOUNTS_REG          14336

SQL>

An dieser Stelle kann jetzt die Auswirkung auf die Performance getestet werden. Dieses geschieht durch das folgende Testskript: 
set serveroutput on
set echo on
set pages 100
set lines 200

column sql_text       format a30
column cr_buffer_gets format 99999
column disk_reads     format 99999
column elapsed_time   format 9999999999
column io_cost        format 99999
column cpu_cost       format 9999999999
column time           format 999

-- Test performance on regular table
SET SERVEROUTPUT ON SIZE UNLIMITED
ALTER SYSTEM FLUSH BUFFER_CACHE
/

COL value NOPRINT NEW_VALUE start_cpu
SELECT DBMS_UTILITY.GET_CPU_TIME value FROM DUAL
/

SET TIMING ON
SELECT /* fs_test1 */ acc_mgr_id
,      acc_type
,      AVG((sysdate-acc_open_dt))
FROM  accounts_reg
GROUP BY acc_mgr_id, acc_type
ORDER BY acc_mgr_id, acc_type
/

SET TIMING OFF
SELECT DBMS_UTILITY.GET_CPU_TIME - &start_cpu cpu_consumed FROM DUAL
/

SELECT s.sql_text
,      p.cr_buffer_gets
,      p.disk_reads
,      p.elapsed_time
,      p.io_cost
,      p.cpu_cost
,      p.time
FROM   v$sql_plan_statistics_all p
,      v$sql                     s
WHERE s.sql_id = p.sql_id
AND   s.sql_text LIKE '%fs_test1%'
AND   p.id = 1
/

-- Test performance on compressed table
ALTER SYSTEM FLUSH BUFFER_CACHE
/

COL value NOPRINT NEW_VALUE start_cpu
SELECT DBMS_UTILITY.GET_CPU_TIME value FROM DUAL
/

SET TIMING ON
SELECT /* fs_test2 */ acc_mgr_id
,      acc_type
,      AVG((sysdate-acc_open_dt))
FROM  accounts_comp
GROUP BY acc_mgr_id, acc_type
ORDER BY acc_mgr_id, acc_type
/

SET TIMING OFF
SELECT DBMS_UTILITY.GET_CPU_TIME - &start_cpu cpu_consumed FROM DUAL
/

SELECT s.sql_text
,      p.cr_buffer_gets
,      p.disk_reads
,      p.elapsed_time
,      p.io_cost
,      p.cpu_cost
,      p.time
FROM   v$sql_plan_statistics_all p
,      v$sql                     s
WHERE s.sql_id = p.sql_id
AND   s.sql_text LIKE '%fs_test2%'
AND   p.id = 1
/

Die Auswertung ergibt dann folgendes Bild: 
SQL> -- Test performance on regular table
...
Abgelaufen: 00:00:12.50
...
CPU_CONSUMED
------------
         883
...
SQL_TEXT                       CR_BUFFER_GETS DISK_READS ELAPSED_TIME IO_COST    CPU_COST TIME
------------------------------ -------------- ---------- ------------ ------- ----------- ----
SELECT /* fs_test1 */ acc_mgr_          22598      13413     10485584    3841  5253460351   50
id ,      acc_type ,      AVG(
(sysdate-acc_open_dt)) FROM  a
ccounts_reg GROUP BY acc_mgr_i
d, acc_type ORDER BY acc_mgr_i
d, acc_type


SQL>
SQL> -- Test performance on compressed table
...
Abgelaufen: 00:00:10.73
...
CPU_CONSUMED
------------
         869
...
SQL_TEXT                       CR_BUFFER_GETS DISK_READS ELAPSED_TIME IO_COST    CPU_COST TIME
------------------------------ -------------- ---------- ------------ ------- ----------- ----
SELECT /* fs_test2 */ acc_mgr_          22000      11118      8894898    3013  4877751081   39
id ,      acc_type ,      AVG(
(sysdate-acc_open_dt)) FROM  a
ccounts_comp GROUP BY acc_mgr_
id, acc_type ORDER BY acc_mgr_
id, acc_type


SQL>

Die Messungen des oben durchgespielten Testfalls ergeben in einer Tabelle zusammengefasst folgendes Bild:

 Reguläre TabelleKomprimierte TabelleEinsparung
Antwortzeit12,5010,7314,16%
CPU used8838691,59%
cr buffer gets22598220002,65%
disk reads134131111817,11%
elapsed time10485584889489815,17%
io cost (*)3841301321,56%
cpu cost (*)525346035148777510817,15%
time (*)503922,00%
Die mit (*) Statistiken sind erwartete Werte, d. h. sie beruhen auf Annahmen des cost-based Optimizers!


Man erkennt selbst bei diesem kleinen Testfall die positiven Auswirkungen des Einsatzes der Advanced Compression Option insbesondere im Bereich disk reads (das entspricht den physikalischen Plattenzugriffen)! Die in der Realität zu erzielenden Verbesserungen hängen natürlich sehr stark vom Aufbau der Daten und dem erreichten Einsparungsfaktor ab. Dieses kann je nach Anwendungsfall variieren und, z. B. mit einer Tabelle, die LOB-Daten enthält, bis zu 70% ausmachen!

Ein kleiner Hinweis noch zum Abschluss:
In einer der letzten Ausgaben der DOAG-News ist ein sehr interessanter weiterführender Artikel zu dem Themenkomplex Advanced Compression Option erschienen, den Sie hier herunterladen können.

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

Zurück zur Community-Seite