Bug: Basic Table Compression und lang laufende UPDATEs

Im Rahmen eines Projektes, in dem Basic Table Compression eingesetzt wird, bin ich offensichtlich in einen Bug gelaufen. Umgebung:

  • Oracle 11.2.0.3.0, Enterprise Edition
  • Solaris 10

Ziel war die Maskierung von Daten auf einer großen, partitionierten Tabelle, die mit dem Attribut COMPRESS erstellt wurde. Gegenüber einer unkomprimierten Version waren die Laufzeiten für ein Update allerdings zehn- bis zwanzigmal länger und mit 100% CPU-Last während der vollen Laufzeit verbunden.

Hier ein Testfall, der auch ohne Partitionierung (und im Prinzip auch ohne die hier verwendeten Regular Expressions) auskommt:

SQL> CREATE TABLE obj_comp COMPRESS AS SELECT * FROM dba_objects;
SQL> CREATE TABLE obj_uncomp AS SELECT * FROM dba_objects;
SQL> set timing on
SQL> UPDATE obj_uncomp SET owner = regexp_replace( owner, '[[:alpha:]]|[[:digit:]]', '*' );

142029 Zeilen wurden aktualisiert.

Abgelaufen: 00:00:24.36
SQL> COMMIT;

Transaktion mit COMMIT abgeschlossen.

Abgelaufen: 00:00:00.31
SQL> UPDATE obj_comp SET owner = regexp_replace( owner, '[[:alpha:]]|[[:digit:]]', '*' );

142028 Zeilen wurden aktualisiert.

Abgelaufen: 00:08:20.13
SQL> COMMIT;

Transaktion mit COMMIT abgeschlossen.

Abgelaufen: 00:00:00.00

Das sind also 24 s bei der unkomprimierten gegenüber 500 s bei der komprimierten Tabelle!

Der o.a. Testfall war auch bei Oracle reproduzierbar und lässt sich im Übrigen auch unter Version 10.2.0.4 reproduzieren. Es ist zwar bekannt und auch dokumentiert, daß DML auf komprimierten Tabellen deutlich mehr Ressourcen benötigt — immerhin wird ja zunächst dekomprimiert, dann das UPDATE durchgeführt, dann wieder komprimiert, wobei sich ggf. das Kompressionsverhältnis verschlechtert und daraufhin die Datenblöcke überlaufen können — aber Faktor 10-20 erschien dann auch Oracle Support zu hoch. Auf Basis meines Service Requests wurde daher Bug # 14608828 eröffnet, dessen Bearbeitung derzeit noch aussteht.

Bis zur Behebung des Bugs oder der Bestätigung eines „works as designed“ bleibt daher als möglicher Workaround, komprimierte Tabellen oder Partitionen zunächst zu dekomprimieren, dann das DML durchzuführen und danach wieder zu komprimieren:

SQL> ALTER TABLE obj_comp MOVE NOCOMPRESS;

Tabelle wurde geõndert.

Abgelaufen: 00:00:00.95
SQL> UPDATE obj_comp SET owner = regexp_replace( owner, '[[:alpha:]]|[[:digit:]]', '*' );

142028 Zeilen wurden aktualisiert.

Abgelaufen: 00:00:23.88
SQL> ALTER TABLE obj_comp MOVE COMPRESS;

Tabelle wurde geõndert.

Abgelaufen: 00:00:01.01

Dieser Workaround lief ca. 26 Sekunden gegenüber 8:20 Minuten im oberen Beispiel.

Eine verfeinerte Methode wäre ein CREATE TABLE … AS SELECT, danach ALTER … COMPRESS und RENAME bzw. EXCHANGE PARTITION, was die Zeit für das erste NOCOMPRESS einsparen würde.

Wenn es zu diesem Bug weitere Erkenntnisse gibt, werde ich sie hier posten.

2 Gedanken zu „Bug: Basic Table Compression und lang laufende UPDATEs

  1. Randolf Geist

    Hallo Uwe,

    im Grunde könnte das wieder ein ähnliches Problem sein, wie ich es hier und hier beschrieben habe. Allerdings waren diese Testfälle damals unter 11.2 unauffällig.

    Auf jeden Fall wäre es interessant, die Ausgabe eines „Session Statistics“-Snappers für die UPDATE-Operation zu sehen (z.B. MYSTATS von Adrian Billington), das mag schon einen Hinweis auf aussergewöhnliche Aktivitäten geben, wenn man die beiden Varianten (unkomprimiert vs. komprimiert) vergleicht.

    Du könntest also einfach mal probieren, was passiert, wenn Du den Testfall mit einem MSSM-Tablespace wiederholst oder auch mit einer kleineren Blockgröße, sollte der Test mit einer größeren Blockgröße durchgeführt worden sein (z.B. 16K oder 32K).

    Ansonsten können die in den Blog Posts genannten Events hilfreich sein für die weitere Analyse, falls es ein Problem mit ASSM sein sollte.

    Bei Deinem Testfall sind die Rahmenbedingungen für den „alten“ ASSM-Bug eigentlich nicht gegeben (migrierte Zeilen sind zu lang), aber vielleicht hilfts ja weiter.

    Du schreibst oben im Text, dass die Daten nach dem UPDATE wieder komprimiert würden. Bei BASIC compression werden die Daten, die durch konventionelles DML modifiziert wurden, überhaupt nicht wieder komprimiert, und selbst die extra lizenzpflichtige „OLTP compression“ re-komprimiert nur bei INSERTs, zumindest bei den Tests bis 11.2, die ich damals durchgeführt habe (aber ich erinnere mich dunkel an Bug-Beschreibungen, die davon sprachen, das Verhalten in Zukunft zu ändern). Das solltest Du auch daran erkennen können, dass in Deinem Fall die komprimierte Tabelle nach dem Update größer ist (!) als die nicht komprimierte und ANALYZE TABLE … LIST CHAINED ROWS eine Menge Rows auswirft für die komprimierte Tabelle nach dem UPDATE.

    Dies bedeutet, dass DML auf komprimierten Daten zu massiven Row Migrations führen kann (auch bei der OLTP compression), was dann eben in meinen Fällen zu den Bugs mit ASSM führt, und grundsätzlich erklärt, warum DML auf komprimierten Daten einfach viel langsamer als erwartet sein kann. Ob es alleine ausreicht, Deinen Fall zu beschreiben, weiss ich nicht.

    Bei mir war Dein Testcase (11.2.0.3 Win32, 8K und 16K Blockgröße, ASSM und MSSM) übrigens „nur“ vier mal langsamer bei der komprimierten Tabelle – das wäre für mich noch im Rahmen des erwarteten Unterschieds.

    Die Laufzeiten für ein UPDATE (meine Tabelle hatte nur die Hälfte der Rows Deines Beispiels, also ca. 72K Rows) war aber eh komplett anders (1.5 secs unkomprimiert vs. 6 secs komprimiert), und das war ein Desktop-PC mit langsamer Festplatte, insofern wundern mich Deine Laufzeiten grundsätzlich, warum 24 Sekunden für ein Update von 140K Rows?

    Herzliche Grüße,
    Randolf

    Gefällt mir

    Antwort
    1. Uwe M. Küchler Autor

      Hallo Randolf,
      ein weiteres Mal herzlichen Dank für Deine Anregungen. Die Problematik mit dem „heimlichen Uncompress“ bei Updates war mir beim Durchgehen der Tests schon gar nicht mehr gegenwärtig — aber die Doku ist da ja eindeutig: Basic Compression komprimiert nur Direct-Path-Operationen.
      Insofern ist der jetzt eingesetzte Workaround ohnehin die geschicktere Variante, da am Ende wieder eine komprimierte Partition vorliegt.
      Noch besser wäre ein CTAS+Exchange Partition, denn — wie es mal auf einer Folie eines OakTablers stand — „Insert ist das bessere Update“.

      Die ASSM/MSSM-Thematik hatte ich auch schon mal im Hinterkopf, aber zunächst nicht weiter verfolgt. Heute habe ich es mal mit MSSM versucht, aber dieselben Laufzeiten beobachtet: diesmal 196 s für nur 54000 Rows! Allerdings waren nun auch noch 13 Bitmap- und 3 B-Tree-Indizes mit im Spiel.

      ASH und Tanel Poders „Session Snapper“ sind sich einig: 100% Wait on CPU für die Session.

      Oracle hat gerade einen weiteren Test Case von mir bekommen. Mal sehen, was die daraus machen können.

      Beste Grüße,
      Uwe

      Gefällt mir

      Antwort

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s