table compression und Updates

Meiner Erinnerung nach habe ich gelegentlich (möglicherweise bei Randolf Geist) gelesen, dass auch die Option COMPRESS FOR ALL OPERATIONS nicht verhindert, dass ein UPDATE eines Satzes in einer komprimierten Tabelle zum Entpacken dieses Satzes führt, so dass dieser sich sogar dann vergrößert, wenn das Netto-Datenvolumen des Satzes kleiner wird (also ein Attribut mit einem kleineren Wert gefüllt wird). Da ich den Link dazu gerade nicht finde, hier ein kleiner Test dazu (mit 11.1.0.7 und einer Blocksize von 16K):

Zunächst das Test-Script:

-- test_compression.sql
-- Test-Tabellen löschen und anlegen
drop table t_nocompress purge;
drop table t_compress_direct purge;
drop table t_compress_oltp purge;

create table t_nocompress
as
select rownum id
     , 'blaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' bla 
  from dual 
connect by level <= 100000;

create table t_compress_direct
as
select *
  from t_nocompress;
  
create table t_compress_oltp
as
select *
  from t_nocompress;

-- unkomprimierte Größe der Segmente
select segment_name, blocks
  from user_segments
 where segment_name like 'T%COMPRESS%';

-- Komprimierung für zwei Tabellen
alter table t_compress_direct move compress;

alter table t_compress_oltp move compress for all operations;

-- komprimierte Größe der Segmente
select segment_name, blocks
  from user_segments
 where segment_name like 'T%COMPRESS%';

-- Auslesen des Komprimierungstyps
select table_name
     , compression
     , compress_for
  from user_tables
 where table_name like 'T%COMPRESS%';

-- Updates für alle Tabellen
update t_nocompress set bla = 'bla';

update t_compress_direct set bla = 'bla';

update t_compress_oltp set bla = 'bla';

-- Prüfung der Größe nach Durchführung des Updates
select segment_name, blocks
  from user_segments
 where segment_name like 'T%COMPRESS%';

Hier die relevanten Ergebnisse der Ausführung:

-- Größe nach der Anlage
SEGMENT_NAME                       BLOCKS
------------------------------ ----------
T_COMPRESS_DIRECT                     768
T_COMPRESS_OLTP                       768
T_NOCOMPRESS                          768

-- Größe nach Komprimierung
SEGMENT_NAME                       BLOCKS
------------------------------ ----------
T_COMPRESS_DIRECT                     256
T_COMPRESS_OLTP                       256
T_NOCOMPRESS                          768

-- Komprimierungstypen
TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
T_COMPRESS_DIRECT              ENABLED  DIRECT LOAD ONLY
T_COMPRESS_OLTP                ENABLED  FOR ALL OPERATIONS
T_NOCOMPRESS                   DISABLED

-- Update mit Laufzeiten
100000 Zeilen wurden aktualisiert.

Abgelaufen: 00:00:01.06

100000 Zeilen wurden aktualisiert.

Abgelaufen: 00:00:10.65

100000 Zeilen wurden aktualisiert.

Abgelaufen: 00:01:49.21

-- Größe nach dem Update
SEGMENT_NAME                       BLOCKS
------------------------------ ----------
T_COMPRESS_DIRECT                    1280
T_COMPRESS_OLTP                      1280
T_NOCOMPRESS                          768

Demnach gilt Folgendes:

• Nach der Anllage sind die Tabellen alle gleich groß (wie zu erwarten ist).
• Nach dem ALTER TABLE ... MOVE mit den unterschiedlichen COMPRESS-Varianten, reduziert sich die Größe der komprimierten Segmente von 768 auf 256 Blocks.
• Die Laufzeit der Updates ist für die komprimierten Tabellen deutlich erhöht. Dabei ist die OLTP compression (FOR ALL OPERATIONS) noch einmal sehr viel langsamer als die direct path compression.
• Im Ergebnis sind die komprimierten Segmente nach dem Update deutlich größer als das nicht komprimierte Segment (1280 zu 768 Blocks)

Anscheinend habe ich mich also korrekt erinnert, dass die OLTP compression noch nicht ganz das tut, was man (oder zumindest ich) von ihr erwarten würde. Sollte ich den Link auf Randolf Geists entsprechenden Hinweis wiederfinden (oder eine andere Quelle), dann liefere ich diese Information noch nach.

Aus Gründen der Vollständigkeit hier noch der Verweis auf Randolf Geists Artikel zu den Einschränkungen der verschiedenen compression Mechanismen, in dem ich den Hinweis auf die beschränkte Wirksamkeit der OLTP compression auf Anhieb allerdings nicht (wieder?)gefunden habe (aber ich habe ihn auch nicht noch einmal gründlich gelesen).

Noch ein paar Nachträge zum Thema:

• Für die OLTP compression benötigt man die Advanced Compression Option, während die Standard compression Teil der EE ist.
• Einen ausführlicheren Test inklusive einer Erklärung der technischen Hintergründe der Advanced Compression hat Mark Rittman vor einigen Jahren geliefert. Darin schreibt er: "According to the manuals, my understanding is that it’s not a new “decompression-safe” algorithm that used, what actually happens with advanced compression is this:
• 1) A table created using the advanced compression feature is initially actually uncompressed
• 2) New data is loaded using conventional path (and direct path) inserts, updated as neccessary
• 3) When the table’s PCTFREE limit is reached, the compression algorithm kicks in and compresses the data
• 4) Inserts and updates then carry on, with updates decompressing the table rows as before
• 5) When the table’s PCTFREE limit is again reached, compression kicks in again to pack down the rows."

So ganz bringe ich das mit meinem Test aber noch nicht zusammen ...

• Greg Rahn erklärt in einem Kommentar zu seinem Artikel The Core Performance Fundamentals Of Oracle Data Warehousing – Table Compression: "Updates [...]  don’t mix well with compression". Das immerhin scheint allein schon angesichts der Laufzeiten meiner Updates ein geeignetes Schlusswort zum Thema zu sein.

Nachtrag 02.05.2012: Randolf Geist hat die Links, die mir noch gefehlt hatte, in seinem Kommentar aufgeführt, aber da sie dort nur als Text erscheinen, ergänze ich sie hier noch mal als Links:

• ASSM bug reprise - part 1
• ASSM bug reprise - part 2

Vor allem Teil 2 beschäftigt sich ausführlich mit dem Verhalten der OLTP Compression beim Auftreten von UPDATEs. Das Fazit dort lautet: "Mixing compression with a significant number of updates is a bad idea in general."

Nachtrag 01.02.2013: Erst jetzt (im Anschluss an eine Wiederholung des Tests nach Lektüre der schönen Artikelserie, die Jonathan Lewis dieser Tage bei AllThingsOracle veröffentlicht hat) ist mir klar geworden, dass die extremen Laufzeiten meines Tests mit UPDATE und OLTP-compression (1:49 min) dem von Randolf Geist beschriebenen Bug zu verdanken sind. Wenn man den Test in 11.1.0.7 mit einem MSSM-Tablespace wiederholt, sind die Laufzeiten für die Updates in den Fällen T_COMPRESS_DIRECT und T_COMPRESS_OLTP relativ ähnlich (jeweils ca. 6 sec.; in 11.2 sollte der Bug keine Rolle mehr spielen und dort sehe ich auch für ASSM keine deutlichen Laufzeitunterschiede). OLTP compression wird dadurch nicht sehr viel interessanter, sollte aber zumindest keinen extrem negativen Effekt auf die Laufzeit von Updates haben