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.