Dienstag, August 16, 2016

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.

Montag, August 01, 2016

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.

Mittwoch, Juli 27, 2016

Bloom Pruning im Ausführungsplan

Maria Colgan hat mal wieder einen Artikel geschrieben, der sich nicht unmittelbar mit ihrem aktuellen Tätigkeitsfeld In-Memory beschäftigt, sondern ihr altes Thema betrifft: die Strategien des Optimizers. Im Artikel zeigt sie einen Plan, der in der Name-Spalte zwei Bloom Filter Erzeugungen aufführt (JOIN FILTER CREATE, PART JOIN FILTER CREATE), aber nur die Verwendung eines der beiden Filter. Der zweite Bloom Filter erscheint in den Spalten Pstart und Pstop und wird zum Partition Pruning verwendet (also zum Überspringen nicht benötigter Partitionen) - und dieses Pruning mit dem Bloom Filter nennt man dann aus naheliegenden Gründen Bloom Pruning.

Donnerstag, Juli 21, 2016

Partial Indexes für partitionierte Tabellen

Dani Schnider hat zuletzt eine dreiteilige Serie zur Verwendung partieller Indizes veröffentlicht. Dabei stellt sich als erstes die Frage: was ist ein Partial Index überhaupt? Die Antwort lautet: Partielle Indizes werden nur auf einer Teilmenge der Partitionen einer partitionierten Tabelle erzeugt: entweder, um die Ladeprozesse für aktive Partitionen nicht zu beeinträchtigen - in diesem Fall verzichtet man auf eine Indizierung der aktuellen Daten; oder um umgekehrt die Zugriffe auf die aktuellen Daten durch Indizes zu unterstützen, während das für historische Daten vermeidbar ist:
  • Partial Indexes Trilogy – Part 1: Local Partial Indexes: erläutert, dass die partielle Indizierung über die Schlüsselwörter INDEXING ON/OFF gesteuert wird, die bestimmen, ob für eine Partition lokale Indizes angelegt werden. In dba_tab_partitions zeigt die Spalte INDEXING an, welche Definition für eine Partition gewählt wurde. Bei der Anlage eines lokalen Index kann man nun die Option INDEXING PARTIAL angeben, die dafür sorgt, dass für alle Partitionen mit indexing=off Index-Partitionen im Zustand UNUSABLE erzeugt werden, zu denen keine physikalischen Segmente angelegt werden. Ohne die INDEXING PARTIAL Option wird der Index in allen Partitionen erzeugt, die indexing Definition ist dann nicht wirksam, so dass man die partielle Indizierung Index-spezifisch einrichten kann. Ein Index rebuild für lokale Indizes ist nur auf Partitionsebene möglich, aber damit kann man dann auch die initial als unusable definierten Partitionen über einen rebuild usable machen - und damit den Index von einem partiellen in einen ganz normalen lokalen Index umwandeln. Eigentlich sind partielle Indizes erst in 12c verfügbar geworden, aber in 11g kann man das Verhalten leicht nachbauen, wenn man einen lokalen Index initial als unusable definiert und dann nur die Partitionen über rebuild verfügbar macht, für die man den Index tatsächlich verwenden möchte.
  • Partial Indexes Trilogy – Part 2: Global Partial Indexes: während sich das Verhalten partieller lokaler Indizes in 11g manuell nachbilden lässt, ist das im Fall globaler Indizes nicht der Fall. Bei diesen Indizes werden nur die rowids indiziert, die auf Partitionen verweisen, für die indexing=on gewählt wurde. Die häufigste Ursache dafür, dass man überhaupt einen global Index definiert, ist die Notwendigkeit, einen unique index zu erzeugen, der den partition key nicht enthält - und das ist dann mit einem partial index aus naheliegenden Gründen nicht möglich: wenn nicht alle Datensätze indiziert sind, lässt sich die Eindeutigkeit eindeutig nicht garantieren. Mit einem Befehl ALTER TABLE ... MODIFY PARTITION ... INDEXING ON; kann man zusätzliche Partitionen in den global partial index aufnehmen, was aber natürlich massive Index-Maintenance nach sich ziehen kann. Umgekehrt ist die Umstellung auf INDEXING OFF zunächst eine Metadaten-Operation: die Einträge werden nicht sofort aus der Index-Struktur gelöscht, stattdessen werden in dba_indizes orphaned_entries angezeigt, die darauf hin deuten, dass die Index-Struktur temporär nutzlose Elemente enthält. Die Beseitgung dieser Einträge erfolgt schließlich durch "asynchronous global index maintenance" und in diesem Zusammenhang verweist der Herrn Schnider auf einige Artikel von Richard Foote, die ich hier sicher auch schon mal verlinkt habe. Ein anderer interessanter Aspekt ist, dass die Löschung einer Partition mit indexing=off dazu führt, dass der global partial index unusable wird, sofern dabei nicht die UPDATE INDEXES Klausel verwendet wird.
  • Partial Indexes Trilogy – Part 3: Queries on Partial Indexes: erläutert, wie sich die Zugriffe über partial indexes verhalten. Im Fall lokaler partial indexes wird der Zugriff über UNION ALL verknüpft: für die indizierten Partitionen erscheint der Index-Zugriff, für die übrigen Partitionen ein Full Table Scan. Abhängig davon, ob indizierte und nicht-indizierte Partitionen oder nur die einen oder die anderen abgefragt werden, ergeben sich unterschiedliche Plan-Varianten, wobei das UNION ALL in allen Fällen erscheint und die irrelvanten Teile des Plans dann ggf. nicht ausgeführt werden müssen. Ähnlich sieht es für die global partial indexes aus, wobei sich weitere Varianten in der Plandarstellung ergeben. Auch im Rahmen der Star Transformation im Data Warehouse können partielle Indizes verwendet werden.

Montag, Juli 18, 2016

Hint-Verwendung zur Bestimmung der Join-Reihenfolge

Brendan Furey erläuert in seinem Blog die Möglichkeiten, die Oracle bietet, die Join-Reihenfolge über Hints zu beeinflussen. Darin weist er zunächst darauf hin, dass der USE_HASH Hint eigentlich nur einen Parameter benötigt und nicht die Angabe der beiden Aliase der Tabellen, die man miteinander verknüpfen will: bei der Angabe von zwei Aliasen betrachtet Oracle dies als zwei Hints und wird folglich einen der beiden übergehen. Welcher der Hints nicht berücksichtigt wird, hängt von der Reihenfolge ab, in der die Tabellen gejoint werden, und in diesem Zusammenhang wird auf einen klassischen "Quiz Night" Artikel von Jonathan Lewis verwiesen, der erläutert, dass im Fall eines 4-Tabellen-Joins, bei dem die Join-Reihenfolge über LEADING Hints und die Join Methode über USE_HASH Hints bestimmt ist, immer noch acht unterschiedliche Pläne entstehen können, weil dadurch noch nicht festgelegt ist, welche rowsource als "build table" und welche als "probe table" zu betrachten ist, was über die Hints SWAP_JOIN_INPUTS und NO_SWAP_JOIN_INPUTS gesteuert werden kann. An dieser Stelle ergänzt der Herr Furey seine Überlegung, dass in diesem Fall eigentlich zwei Ebenen von Sortierungen zu berücksichtigen sind, da man den gleichen Plan mit mehreren unterschiedlichen Hint-Kombinationen hervorrufen kann: eine Outer-level join order (die die Reihenfolge angibt, in der die Tabellen in einem komplexen Join verknüpft werden) und eine Inner-level join order (die anzeigt, auf welcher Seite eine Joins eine Tabelle bei der Verknüpfung mit einer anderen rowsource eingesetzt wird: also im Hash Join etwa als build oder probe table). Nach dieser Definition hat der LEADING Hint unterschiedliche Auswirkungen, abhängig davon, ob ein Hash Join oder ein anderer Join Typ im Spiel ist. Ich denke, das ist eine interessante Art, den Sachverhalt zu betrachten, obwohl ich mir noch nicht ganz sicher bin, ob diese Unterscheidung massive Vorteile bei der Beschreibung der Situation bringt.