Statistikerfassung für Tabellen mit mehr als 255 Spalten mit dbms_stats

Nur eine kurze Notiz auf einen Artikel von Randolf Geist, der erklärt, dass Oracle 12c eine deutliche Verbesserung bei der Erfassung von Statistiken für Tabellen mit sehr vielen Spalten eingeführt hat: in älteren Releases mussten die Statistiken für solche Tabellen mit mehr als 255 Spalten, deren Datensätze intern auf mehrere row pieces verteilt werden müssen, in mehreren Leseoperationen ermittelt werden (multi pass). Diese Einschränkung ist mit 12c aufgehoben: auch für Tabellen mit mehreren row pieces pro Datensatz benötigt die Statistikerfassung jetzt nur noch einen einzigen Zugriff (single pass), was die Performance der Operation deutlich verbessern kann.

Umwandlung von LONG in CLOB mit SYS_DBURIGEN

Meine Standardantwort auf die Frage, wie man die Inhalte von LONG-Spalten auslesen kann, war seit vielen Jahren: ich hab's vergessen, aber Adrian Billington hat alles notiert, was man über diesen unerfreulichen Datentyp wissen muss. Diese Antwort kann ich jetzt modifizieren: der handlichste Weg, um LONGs in etwas weniger Häßliches zu verwandeln, ist die Verwendung der builtin-Funktion SYS_DBURIGEN, der von Marc Bleron (aka odi_63) in seinem Blog beschrieben wird. Intern erzeugt Oracle in diesem Zusammenhang ein DBURI Objekt und beim Aufruf der Funktion SYS_DBURIGEN(...).GETCLOB() wird eine SQL-Query generiert, die die erforderlichen Informationen abruft. Die Details des Aufrufs werde ich mir auch diesmal nicht merken, aber mit etwas Glück zumindest den Ort, an dem ich danach suchen kann.

Zerlegung von Strings

Stew Ashton hat in jüngerer Vergangenheit eine ganze Reihe interessanter Lösungen zum Problem der Zerlegung von Strings in einzelne Token in SQL vorgestellt. Die klassischen Lösungen dafür sind die (in der Regel auf Tom Kyte zurückgeführte) Kombination von instr und substr (die kurz vor 400 Bytes an ihre Grenzen stösst), sowie der Einsatz von regexp_substr, der - wie alle regexp-Operationen recht kostspielig im Hinblick auf die Nutzung der CPU-Ressourcen ist:

• New, Improved IN Lists!: zeigt ein - auf Grundlage einer Präsentation von Connor McDonald - entwickeltes Vorgehen, das eine Kombination des row-Generators (connect by level) mit der instr- und der analytischen lead-Funktion verwendet. Das Ergebnis ist bereits recht kompakt und lesbar.
• Splitting Strings: Surprise!: liefert einige zusätzliche Alternativen durch die Verwendung von XMLTABLE und insbesondere durch die zugehörige ora:tokenize Funktion.
• Splitting Strings: Proof!: zeigt, dass ora:tokenize nicht nur eine kompakte Lösung darstellt, sondern auch deutlich schneller arbeitet als regexp_substr (erwartungsgemäß sehr langsam) und substr+instr (deutlich langsamer als ora:tokenize, wenn die Anzahl der Substrings, die zerteilt werden sollen, relativ groß ist).
• Splitting Strings: PL/SQL: zeigt, dass die String-Zerlegung ein Fall ist, in dem PL/SQL schneller ist als SQL. Das kommt nicht besonders oft vor, ist aber auch nicht völlig unüblich.

Ich würde mich nicht wundern, wenn der der Herr Ashton weitere Artikel ergänzen würde - und mein Bestes versuchen, sie hier gegebenenfalls einzufügen.

Physical I/O-Optimierung für Nested Joops Joins

Vor einiger Zeit hatte ich hier eine Zusammenfassung der Zusammenfassung einer Artikelserie von Nikolay Savvinov untergebracht, die sich mit den physical IO Optimierungen für Nested Loops Joins beschäftigt. Nun hat Randolf Geist einen Artikel veröffentlicht, der - ausgehend auf Nikolays Ausführungen - den Versuch unternimmt, die in 12c vorkommenden Nested Loops Plan-Varianten mit den I/O Optimierungen zusammenzuführen.

1. Nested Loops Join Batching: seit 11g die häufigste Variante. Im Plan erscheinen zwei Nested Loops steps: zunächst werden die rowids ermittelt und dann erfolgt der Tabellenzugriff über die rowid. Diese Plan-Form kann batched I/O ermöglichen ("db file parallel read" oder aynchronous I/O), aber die Entscheidung darüber, ob diese Optimierung verwendet wird, liegt bei der runtime engine (die auch auf die konventionellen "db file sequential read" Zugriffe zurückgreifen kann). Unter bestimmten Umständen können statt der "db file parallel read" Operationen (die mehrere I/O requests in einem einzelnen I/O submit call zusammenfassen) auch "db file scattered read" Zugriffe auftauchen - also multibock reads, die üblicherweise beim Full Table Scan (oder dem verwandten Index Fast Full Scan) auftreten; dies ergibt sich vor allem, wenn ein "cache warmup prefetching" verwendet wird (das aus unerfindlichen Gründen bei Verwendung von SQL Trace oder der Planerzeugung mit rowsource statistics deaktiviert wird; diese Plan-Form macht in 12c offenbar auch dem SQL Monitoring Probleme, das bei der Zählung von Iterationen durcheinander kommen kann).
2. Nested Loop Join Prefetch (mit batched rowid Zugriff - in 12c): Seit 12c tritt das in 9i eingeführte Nested Loop prefetching in neuer Form auf und enthält nun nach dem TABLE ACCESS BY INDEX ROWID das zusätzliche Schlüsselwort BATCHED. Für den Tabellenzugriff erfolgt dabei eine sehr intensive Zusammenfassung von Zugriffen in den "db file parallel read" Operationen, während der Index-Zugriff offenbar nicht zusammengefasst wird, sondern via single block calls erfolgt (also "db file sequential read") - zumindest kommen Randolf und Nikolay in ihren Test-Setups zu diesem Ergebnis. Der Plan tritt in der freien Wildbahn normalerweise nicht auf, kann aber durch (im Artikel aufgeführte) Hints oder durch Deaktivierung des Nested Loops Join Batching erzwungen werden.
3. Nested Loop Join Prefetch (seit 9i): auch dieser Plan tritt in aktuellen Releases nur auf, wenn man das Nested Loops Join Batching deaktiviert. Er verhält sich ähnlich wie die 12c Variante, verwendet aber ein weniger agressives prefetching: die Anzahl der in einem "db file parallel read" zusammengefassten requests scheint auf 39 beschränkt zu sein.
4. Klassischer Nested Loops Plan (mit batched rowid Zugriff - in 12c): in Randolfs Tests werden nur die Zugriffe einer Loop-Iteration zusammengefasst: "db file parallel read" Zugriffe treten also nur auf, wenn das Clustering der Tabellendaten in Hinsicht auf den Index nicht besonders gut ist. Für den Index-Zugriff im Index Range Scan erfolgen anscheinend keine Optimierungen (wie schon bei den Prefetch Varianten). Auch dieser Plan tritt in 12c nur unter bestimmten Umständen auf: hauptsächlich, wenn mehrere aufeinander folgende Nested Loops Operationen aufeinander folgen. Mit (den im Artikel aufgeführten) Hints kann man den Plan natürlich auch erzwingen.
5. Klassischer Nested Loops Plan: anders als die 12c-Variante mit dem batched rowid Zugriff erlaubt dieser Plan auch ein ein Batching über mehrere Loop-Iterationen hinweg (und ähnelt insofern recht stark der Implementierung des in 9i eingeführten "Nested Loop Join Prefetch" (aus 3.), als die Zusammenfassung von requests wiederum auf 39 beschränkt ist und kein Batching für die Index Range Scan Operation erfolgt.
   Nachtrag 21.08.2016: In seinem Kommentar hat mich Randolf darauf hingewiesen, dass ich an dieser Stelle falsch interpretiert habe: auch die pre-12c-Implementierung des klassischen NL-Plans unterstützt kein Batching über Loop-Grenzen hinweg. Die Symmetrie zu den vorherigen Beispielen ("Nested Loop Join Prefetch" in 2. und 3.) liegt darin, dass auch hier die ältere (pre-12c) Plan-Variante ohne das "BATCHED ROWID" ein weniger agressives Batching unterstützt: Fall 5 verhält sich demnach zu Fall 4. wie Fall 3. zu Fall 2.

Bei der Verknüpfung mehrerer verschachtelter Nested Loop Operationen tritt das Batching übrigens nur für die äußerste Schleife auf, was den Effekt der Optimierung reduziert, da NL Joins ja oft in Scharen auftreten. Da sich die unterschiedlichen Plan Varianten nicht durch das Costing unterscheiden, könnte die manuelle Beeinflussung der Join Reihenfolge für solche Fälle einen signifikanten Performance-Unterschied hervorrufen.

Spaltenvergleichen mit NULL-Werten

Randolf Geist hat vor kurzem einen interessanten Artikel zu einem Thema veröffentlicht, mit dem man sich beim Schreiben komplexerer SQL-Queries regelmäßig herumschlagen muss: dem Vergleichen von Spalten, in denen NULL-Werte auftauchen können. Für die Prüfung der Gleichheit von Werten bedarf die korrekte Behandlung von NULL-Werten bereits eines recht sperrigen Ausdrucks:

column1 = column2 or (column1 is null and column2 is null)

Und noch unhandlicher wird der Ausdruck, wenn man die Ungleichheit von Werten prüfen möchte:

column1 != column2 or (column1 is null and column2 is not null) or (column1 is not null and column2 is null)

Um solche Konstrukte vermeiden zu können, wird bisweilen ein NVL um die Vergleichswerte gesetzt, um den möglichen NULL-Wert durch eine Alternative zu ersetzen, von der man sicher ist, dass sie außerhalb des Wertebereichs der Spalte liegt - aber wann kann man sich in einem solchen Punkt wirklich sicher sein?

Eine andere beliebte Variante dazu ist die Verwendung der lange Zeit nicht dokumentierten Funktion SYS_OP_MAP_NONNULL, die in 12c schließlich in der Doku erscheint, aber immer noch nicht im SQL language manual. Diese Funktion hat allerdings einen Nachteil: sie ergänzt ein Byte zu jedem Input-Wert, was dazu führt, dass sie bei Verwendung von Spalten mit der maximalen Größe für den verwendeten Datentyp einen Fehler hervorruft (nämlich "ORA-01706: user function result value was too large"). Als Alternative dazu wurde gelegentlich die Verwendung von decode vorgeschlagen - etwa von Stew Ashton, dessen zugehörigen Artikel ich hier gelegentlich erwähnt hatte. Da decode NULL-Werte als vergleichbar ansieht, werden die erforderlichen Prüfungen deutlich übersichtlicher - für den Fall der Gleichheit:

decode(column1, column2, 0, 1) = 0

Und für die Ungleichheitsprüfung:

decode(column1, column2, 0, 1) = 1

Hier kommt aber seit Version 11.2.0.2 eine problematische Optimierung ins Spiel: der Fall der Gleichheitsprüfung (aber nicht der der Ungleichheitsprüfung) wird intern auf die Verwendung von SYS_OP_MAP_NONNULL umgeschrieben, was dann wiederum die Probleme mit den Maximallängen hervorruft. Zusätzlich kommt noch hinzu, dass die SYS_OP_MAP_NONNULL-Funktion in Randolfs Test langsamer ist als das nicht umgeschriebene decode und langsamer als die verbose Standard-Variante. Insofern sollte man SYS_OP_MAP_NONNULL und die implizite Umschreibung von decode seit 11.2.0.2 unter Umständen besser vermeiden und fix control 8551880 verwenden, um die implizite Umwandlung zu vermeiden. Zu hoffen ist, dass Oracle gelegentlich eine solidere Lösung für dieses Problem zur Verfügung stellen kann.