Erläuterungen zum Nested Loops Join

Nikolay Savvinov hat zuletzt in seinem Blog eine interessante Artikelserie zum Verhalten des Nested Loops Join veröffentlicht und dankenswerterweise die Ergebnisse und die Links noch einmal in einem summary zusammengefasst. Und diese Zusammenfassung fasse ich jetzt hier zusammen:

• der Nested Loops Join (NL Join) ist der einfachste Join-Mechanismus in Oracle. Er liest die Daten einer (driving) row source und testet die Ergebnisse gegen die zweite row source (probe-Zugriff, üblicherweise via Index lookup). Da das Verfahren keine startup Kosten beinhaltet, ist es attraktiv für Zugriffe mit hoher Selektivität und für Abfragen, bei denen nur ein kleiner Teil der Daten tatsächlich benötigt wird (also etwa bei der Listendarstellung im Browser).
• das Costing für den NL Join addiert die Kosten des Zugriffs auf die driving row source mit den Kosten des einzelnen probe-Zugriffs, der mit der Anzahl der probe-Zugriffe multipliziert wird - was inhaltlich sehr einleuchtend erscheint. Durch einige überkommene Optimizer-Parameter kann man diese Kosten anpassen (optimizer_index_cost_adj und optimizer_index_caching) und leider trifft man ihre Verwendung noch immer recht häufig an.
• tatsächlich ist das physikalische I/O-Volumen beim NL-Join in der Regel niedriger, als das Modell es angibt, da dieses Join-Verfahren einen "self-caching" Effekt hervorruft: die relevanten Blöcke bleiben mit einiger Wahrscheinlichkeit im Cache, da immer wieder auf sie zugegriffen wird. Daher überschätzt der Optimizer die realen NL-Kosten häufig.
• durch das pinning von driving table blocks und den root/branch blocks des Index, der für die probe-Zugriffe verwendet wird, sinkt die Zahl der LIOs (da der Block nicht zwischenzeitlich freigegeben und dann wieder gepinnt wird).
• Zugriffe für einen non-unique index sind teurer als Zugriffe für einen unique index.
• es gibt diverse Optimierungen für die NL Join, die in verschiedenen Releases eingeführt wurden (10g: prefetching; 11g: batching; 12c: batch rowid access). Sie alle basieren darauf, dass die beim Zugriff auf die driving row source ermittelten rowids, zunächst sortiert werden, ehe der probe-Zugriff erfolgt: dadurch ist es nicht mehr notwendig, Blöcke der zweiten row source mehrmals zu besuchen.
• so lange ausreichende I/O bandwith zur Verfügung steht, ist eine höhere Anzahl von Blocks, die bei einer Leseoperation gelesen werden, nützlich. Anpassungen an den Parametern zur Bestimmung dieser Größe sind in der Regel nicht sinnvoll: viele der zugehörigen Parameter sind nicht dokumentiert und nach den Ergebnissen der Test sind die Performance-Unterschiede, die sich aus Veränderungen am multiblock read count ergeben, nicht dramatisch.
• einen größeren Einfluß auf die Performance der multiblock reads hat die Hardware und ihre Nutzung (SSDs oder HDDs; Striping; I/O Scheduler type; physical or virtual server; asynchonous I/O).
• Das Fazit des Artikels lautet: "Multiblock reads are a complete game changer for nested loop performance. However, extracting the full value from them requires properly configuring the storage, the OS and the database."

Die Analyse-Details der vorangehenden Artikel lohnen auf jeden Fall auch einen Blick, aber wenn man mir eine Zusammenfassung liefert, dann halte ich mich auch an diese...