Cost Based Optimizer: Grundlagen – mit Update für Oracle 12c
Wenn es darum geht, effiziente Abfragen zu erzeugen, gibt es einige wenige Schlüsselkonzepte, die es zu verstehen gilt. Obwohl es "kostenbasierter Optimierer" (Cost Based Optimizer, CBO von hier an abgekürzt) genannt wird, sind es eben nicht primär die Kosten auf die wir schauen müssen, um zu verstehen, warum der CBO bestimmte Entscheidungen für die Erstellung von Ausführungsplänen getroffen hat. In diesem Artikel werden diese Schlüsselkonzepte beleuchtet. Wir werden sehen, warum die Mengen- und Selektivitätsabschätzungen des CBOs so elementar wichtig sind und welche Informationen der CBO derzeit (noch) nicht berücksichtigt. Dies bedeutet auch, dass wir manchmal wesentlich mehr über unsere Daten und Applikation wissen können als der CBO und ihn daher in die richtige Richtung lenken können und müssen, um effiziente Ausführungspläne zu generieren.
Drei entscheidende Fragen
Grundlegend gibt es bei der Suche nach einem effizienten Ausführungsplan drei entscheidende Fragen, die geklärt werden müssen:
- Wie viele Zeilen werden erzeugt / Welche Datenmenge wird erzeugt?
- Wie sind die zu durchsuchenden Daten organisiert – eher zusammenhängend oder durcheinander gemischt (gemäß dem Suchkriterium)?
- Wie groß ist die Wahrscheinlichkeit, dass die zu durchsuchenden Daten bereits im Cache sind?
Zwei Hauptstrategien
Warum sind es gerade diese Fragen, die so wichtig sind? Weil es vereinfacht ausgedrückt zwei Strategien gibt, wie die Daten verarbeitet werden können:
1. Ein großer "Job"
Hier ist vor allem die Frage nach der Datenmenge entscheidend für die Effizienz-Abschätzung der Operation.
2. Mehrere kleinere "Jobs"
Hier sind die Fragen nach der Anzahl der Zeilen ("Wie oft muss der kleine Job ausgeführt werden?"), wie sind die Daten organisiert ("Wie groß ist der Aufwand pro Ausführung?") und dem Caching ("Welcher Anteil der Daten liegt bereits im Cache?") entscheidend.
Werden obige Fragen in diesem Zusammenhang richtig beantwortet, kann die passende Strategie ausgewählt werden. Umgekehrt ist es wahrscheinlich, dass bei falscher Beantwortung der Fragen eine unpassende Strategie ausgewählt wird, die weniger effizient sein kann, zu längerer Ausführungszeit der Abfrage führen und damit auch andere, konkurrierende Prozesse beeinflussen kann.
Der Cost Based Optimizer
Es ist mit Sicherheit nicht überraschend, dass der CBO von Oracle auf der Suche nach dem effizientesten Ausführungsplan versucht, ganz ähnliche Fragestellungen zu beantworten. Interessanterweise adressiert der CBO aber nicht alle drei oben genannten Fragen im gleichen Umfang: Die erste Frage nach der Anzahl der Zeilen und des Datenvolumens wird ausführlich behandelt, wobei wir sehen werden, dass auch hier der CBO auf recht einfache Weise in die Irre geführt werden kann.
Schon bei der zweiten Frage, nach der Organisation der Daten, gibt es nur eine einzige Information die dem CBO zur Verfügung steht: Der sogenannte "Clustering Factor" von Indizes. Während der Clustering Factor eine in vielen Fällen ausreichende Information für die Entscheidung darstellt, wie auf eine einzelne Tabelle zugegriffen wird (Zugriff per vorsortiertem Index oder Full Table Scan über die gesamte Tabelle), gibt es je nach Ausführungsplan für Operationen, die verschiedene Datenquellen verknüpfen, überhaupt keine Information für den CBO, wie diese in Relation zueinander organisiert sind. Dies festzustellen wäre auch eine Aufgabe, die nach heutigem Stand der Technik wahrscheinlich zu viel Zeit und Ressourcen in Anspruch nehmen würde, da es so viele verschiedene Möglichkeiten gibt, wie Datenquellen verknüpft werden können: Die Reihenfolge der Datenquellen kann unterschiedlich sein und pro Datenquelle gibt es potentiell viele mögliche Zugriffsarten. Beides kann die Reihenfolge des Datenzugriffs verändern (zum Beispiel "Full Table Scan Tabelle C -> Index1 Tabelle A -> Index Tabelle B" gegenüber "Index2 Tabelle A -> Index Tabelle C -> Index Tabelle B"), was bedeutet, dass alle möglichen Kombinationen evaluiert werden müssten, um diese Information sinnvoll verarbeiten und gegeneinander abwägen zu können.
Die dritte Frage nach dem Caching von Daten wird derzeit vom CBO überhaupt nicht berücksichtigt – der CBO geht in seinen Berechnungen immer davon aus, dass die Daten nicht im Cache liegen.
Dies alles heisst aber, dass der CBO je nach Daten und Ausführungsplan nur über unzureichende Informationen verfügt, um die genannten Fragen korrekt beantworten zu können und daher leicht eine unpassende Strategie auswählen kann. Weiterhin bedeutet dies bei ausreichendem Wissen über Ihre Daten und die Abfragen auf diese Daten, dass Sie unter Umständen diese Fragen wesentlich besser als der CBO beantworten und ihm daher helfen können, die passende Strategie auszuwählen, bzw. auch besser beurteilen können, ob der CBO eine passende Strategie ausgewählt hat.
Statistiken
Der CBO wendet im Grunde ein mathematisches Modell auf die ihm zur Verfügung stehenden Eingangsdaten (Statistiken aller Art, wie zum Beispiel Objekt- und Systemstatistiken) an und erstellt als Ergebnis einen Ausführungsplan.
Eins der grundlegenden Probleme dieser Herangehensweise ist die Tatsache, dass diese Eingangsdaten, nämlich die Statistiken, je nach Art der Abfrage unter Umständen die Daten nur unzureichend repräsentieren. Verwendet die Abfrage zum Beispiel Ausdrücke, die in den vorberechneten Objekt-Statistiken nicht abgebildet sind (zum Beispiel ein simples UPPER(T1.NAME) = 'MEIER'), dann hat der CBO unter Umständen es schon schwer, die im Grunde sehr einfache Frage zu beantworten, wie viele Zeilen der Tabelle T1 dieses Suchkriterium erfüllen.
Mengenabschätzungen
Wie bereits erwähnt, versucht der CBO seine Abschätzungen standardmäßig basierend auf den vorberechneten Objekte-Statistiken zu erzeugen. Der Grund dafür liegt einfach darin, dass der CBO darauf optimiert ist, den Ausführungsplan möglichst schnell zu erzeugen – für Abfragen, die nur den Bruchteil einer Sekunde dauern, würde das Untersuchen der tatsächlichen Daten ("Wie viele Zeilen entsprechen Filterkriterium X?") im Verhältnis viel zu lange dauern.
Der CBO verfügt aber über diese Funktionalität unter dem Namen "Dynamic Sampling". Diese kommt aber standardmäßig nur in bestimmten Fällen zum Einsatz, zum Beispiel wenn überhaupt keine Objekt-Statistiken vorliegen. Das hat aber zur Folge, dass Abfragen auf Informationen, die von den Objekt-Statistiken nicht oder nur unzureichend abgedeckt sind, sehr leicht den CBO in die Irre führen können.
Ein einfaches Beispiel
Zwei Tabellen T1 und T2 (jeweils 1.000.000 Zeilen) sollen miteinander verknüpft werden, T1 hat einen Fremdschlüssel auf T2 und T2 hat einen eindeutigen Index auf dem Primärschlüssel. Zusätzlich wird ein Filter auf Tabelle T1 angewendet. Die gefilterten Spalten ATTR1 und ATTR2 der Tabelle T1 haben folgende ungleichmäßige Datenverteilung:
ATTR1 ATTR2 COUNT ---------- ---------- ---------- 1 1 900000 90001 90001 10 90002 90002 10 90003 90003 10 90004 90004 10 90005 90005 10 90006 90006 10 90007 90007 10 90008 90008 10 90009 90009 10 . . .
Wird also auf ATTR1 oder ATTR2 gefiltert, scheint ein Histogramm von Vorteil zu sein, um den CBO über die ungleiche Verteilung der Daten in den Spalten zu informieren. Wird dies gemacht, kann man die zwei oben erwähnten Strategien leicht demonstrieren.
Führt man zum Beispiel folgende Abfrage aus:
select count(t2.attr2) from t1 , t2 where /*------------------*/ t1.attr1 = 1 and t1.attr2 = 1 /*------------------*/ and t1.fk = t2.id ;
und schaut auf obige Datenverteilung in T1.ATTR1 und T1.ATTR2, wird offensichtlich, dass 900.000 Zeilen von 1 Million in T1 dieses Suchkriterium erfüllen. Sofern der CBO dies erkennt, würde er wahrscheinlich die Strategie "ein großer Job" auswählen, da die "kleine Job"-Strategie hier bedeuten würde, 900.000-mal auf T2 per Index-Zugriff in einer Schleife zuzugreifen, was üblicherweise deutlich ineffizienter wäre.
So sieht der vom CBO automatisch ausgewählte Ausführungsplan aus:
---------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | HASH JOIN | | 1 | 819K| 900K|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 819K| 900K|
| 4 | TABLE ACCESS FULL| T2 | 1 | 1000K| 1000K|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."FK"="T2"."ID")
3 - filter(("T1"."ATTR1"=1 AND "T1"."ATTR2"=1))
Aufgrund der Histogramme, hat der CBO die Mengenabschätzung für den Filter auf T1 im richtigen Bereich gemacht (Operation ID 3: 819.000 geschätzte (E-Rows) anstatt 900.000 tatsächliche (A-Rows) Zeilen) und automatisch die "ein großer Job"-Strategie gewählt. Dies kann auch anhand der "Starts"-Spalte verifiziert werden – jede Operation des Ausführungsplans ist zur Laufzeit genau einmal ausgeführt worden.
Anmerkung: Die normalerweise im Ausführungsplan nicht angezeigten Spalten "Starts" und "A-Rows" können über einen speziellen Modus aktiviert werden, in dem man entweder den Hint "GATHER_PLAN_STATISTICS" verwendet, oder den Parameter "STATISTICS_LEVEL" auf "ALL" setzt (nur auf Session-Ebene zu empfehlen, da es einen deutlichen Overhead in der Ausführung mit sich bringt). Die zusätzlichen Spalten können dann über einen Aufruf von DBMS_XPLAN.DISPLAY_CURSOR mit der Formatierungsoption "ALLSTATS LAST" erzeugt werden.
Wird eine ähnliche Abfrage durchgeführt, bei der nur wenige Zeilen von T1 die Filterbedingung erfüllen:
select count(t2.attr2) from t1 , t2 where /*------------------*/ t1.attr1 = 90001 and t1.attr2 = 90001 /*------------------*/ and t1.fk = t2.id ;
kann man folgenden Ausführungsplan erhalten:
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | NESTED LOOPS | | 1 | | 10 |
| 3 | NESTED LOOPS | | 1 | 1 | 10 |
|* 4 | TABLE ACCESS FULL | T1 | 1 | 1 | 10 |
|* 5 | INDEX UNIQUE SCAN | T2_IDX | 10 | 1 | 10 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 1 | 10 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("T1"."ATTR1"=90001 AND "T1"."ATTR2"=90001))
5 - access("T1"."FK"="T2"."ID")
Der CBO hat hier für die Filterung der Tabelle T1 (Operation ID = 4) anstatt 819.000, wie im vorherigen Beispiel, nur eine Zeile geschätzt. Das ist zwar einerseits um Faktor 10 falsch, da in Wahrheit 10 Zeilen die Filterbedingung erfüllen, andererseits aber hier grundsätzlich im richtigen Bereich. Folgerichtig hat der CBO die "kleine Jobs"-Strategie gewählt: Auf die Tabelle T2 wird über den eindeutigen Index zugegriffen, und zwar zehnmal (Starts = 10) in einer Schleife (NESTED LOOP), da dies effizienter erschien, als die gesamte Tabelle T2 in einem großen Schritt zu verarbeiten.
Das interessante an dem Beispiel ist, dass sich an dem Zugriff auf Tabelle T1 nichts ändert – es ist in beiden Beispielen ein sogenannter Full Table Scan, da kein sinnvoller alternativer Zugriffsweg zur Verfügung gestellt wurde. Entgegen der häufig vorherrschenden Meinung, kann also die Mengenabschätzung für eine Tabelle nicht nur beeinflussen, wie auf die Tabelle selbst zugegriffen wird, sondern auch die Zugriffsart und Reihenfolge anderer Operationen des Ausführungsplans maßgeblich beeinflussen. Verwendet man eine einfache Abwandlung der ersten Abfrage, die den Großteil von T1 zurückliefert und besser mit der "ein großer Job"-Strategie verarbeitet wird, dann wird das noch klarer:
select count(t2.attr2) from t1 , t2 where /*------------------*/ trunc(t1.attr1) = 1 and trunc(t1.attr2) = 1 /*------------------*/ and t1.fk = t2.id ;
Durch die Verwendung der TRUNC-Funktion (Abschneiden von Nachkomma-Stellen) verändert sich das Ergebnis der Abfrage nicht, da es sich um Ganzzahlen in ATTR1 und ATTR2 handelt. Der CBO weiss dies aber alles nicht und sieht nur die Funktion TRUNC(…). Für diesen Ausdruck liegen aber keine Statistiken vor – vor allem kann das Histogramm auf den Spalten ATTR1 und ATTR2 nicht mehr verwendet werden – und der CBO fällt zurück auf vorgegebene Standardwerte, die nichts mit den eigentlichen Daten zu tun haben:
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | NESTED LOOPS | | 1 | | 900K|
| 3 | NESTED LOOPS | | 1 | 100 | 900K|
|* 4 | TABLE ACCESS FULL | T1 | 1 | 100 | 900K|
|* 5 | INDEX UNIQUE SCAN | T2_IDX | 900K| 1 | 900K|
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 900K| 1 | 900K|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter((TRUNC("T1"."ATTR1")=1 AND TRUNC("T1"."ATTR2")=1))
5 - access("T1"."FK"="T2"."ID")
Wie erwartet, hat sich am Zugriff auf T1 nichts geändert – wie sollte es auch – es ist immer noch ein Full Table Scan. Allerdings ist die Abschätzung bezüglich der resultierenden Zeilenanzahl von T1 grob falsch: 100 anstatt der bekannten 900.000.
In diesem Fall hat dies dazu geführt, dass der CBO sich für die falsche Strategie entschieden hat: Der Ausführungsplan greift 900.000 mal per "kleine Job"-Strategie auf die Tabelle T2 zu, was je nach Tabellen- und Cache-Größe sowie der Verteilung der Daten in der Tabelle T2 zu einer sehr langen Laufzeit führen kann. Der wichtigste Punkt hier ist also, dass auch einfache Mengenabschätzungen für Filteroperationen auf einzelne Tabellen maßgebliche Auswirkung auf den gesamten Ausführungsplan haben können.
Von daher ist es elementar wichtig, dass der CBO diese Abschätzungen im richtigen Bereich macht. Im Laufe der verschiedenen Datenbank-Versionen sind zu diesem Zweck verschiedene Funktionalitäten hinzugefügt worden. Angefangen von Function-Based Indizes in der Version 8.1, um Ausdrücke zu indizieren (und als Seiteneffekt der Indizierung auch Spaltenstatistiken für den Ausdruck), Dynamic Sampling seit Version 9.2, um dem CBO die Möglichkeit zu geben, einen Blick auf die tatsächlichen Daten zu werfen, bis hin zu Virtual Columns und Extended Statistics in 11.1, die beide ermöglichen, auf Ausdrücke und sogar Spaltengruppen Statistiken zu erzeugen, unabhängig von eventuell erzeugten Function-Based Indizes. Diese Mengenabschätzungen gilt es zu allererst zu überprüfen, da sie einfach nachvollzogen werden können (ein einfacher SELECT COUNT(*) mit der Filter-Bedingung auf der Tabelle genügt) und es ausreichend Möglichkeiten gibt, sie zu korrigieren.
Neuerungen in Oracle 12c
Was ändert sich in diesem Zusammenhang mit Oracle 12c? Oracle 12.1 führt eine Menge Neuerungen im CBO-Bereich ein, die interessanterweise mit der neuen Version 12.2 teilweise wieder abgeschaltet werden, da sich ein Teil der neuen Features offensichtlich nicht so positiv im realen Betrieb ausgewirkt haben, wie von Oracle erhofft.
In Bezug auf die oben genannten Beispiele, ist vor allem das neue Feature "Adaptive Joins" hervorzuheben, das es Oracle ermöglicht, bei nicht sicheren Mengenabschätzungen die Join-Methode zur Laufzeit anzupassen. Das heisst, Oracle kann zwischen Nested Loop Join ("kleine Jobs"-Strategie) und Hash Join ("ein großer Job"-Strategie) zur Laufzeit entscheiden, je nachdem, wie viele Zeilen in Operationen vor dem Join tatsächlich entstehen. Dieses Feature erweist sich als teilweise sehr nützlich, wie gleich zu sehen sein wird, kann aber manche Probleme nicht lösen, wenn zum Beispiel aufgrund der falschen Mengenabschätzungen eine ineffiziente Join-Reihenfolge gewählt wurde, bei der mehr Daten entstehen und verarbeitet werden müssen, als notwendig. Die Join-Reihenfolge kann also nicht adaptiv angepasst werden, sondern nur die Join-Methode (Nested Loop oder Hash Join).
Darüber hinaus kann Oracle 12c sich auch merken, wenn solche Fehleinschätzungen der Mengen passieren und entsprechende "Direktiven" persistieren, die dazu führen, dass Oracle erweiterte Statistiken pflegt, als auch zur Planerstellungszeit mehr Zeit auf sogenannte "Dynamische Statistiken" verwendet. Dies bedeutet, dass Oracle einen Teil der Abfrage ausführt, während der Ausführungsplan erstellt wird, um zu ermitteln, welche Datenmengen tatsächlich entstehen. Da hier nur ein Teil der Daten gelesen wird ("Sampling"), können auch diese Informationen irreführend sein – sind in vielen Fällen aber durchaus hilfreich. Da diese Zusatzaktivitäten ("Direktiven" und "Dynamische Statistiken") sich signifikant auf die Zeit auswirken können, die Oracle benötigt um Ausführungspläne zu erstellen, und viele Kunden einen zum Teil extremen Anstieg der Laufzeiten sowie Instabilitäten durch diese Zusatzaktivitäten beobachtet haben, wird dieser Teil standardmäßig in 12.2 wieder deaktiviert (und Oracle empfiehlt das auch für Oracle 12.1 zu machen).
Wie verhält sich nun Oracle 12.1 mit den neuen Features bei dem oben demonstrierten Beispiel? An den ersten beiden Abfragen (Beispiele für "große / kleine Job"-Strategie) ändert sich nichts, aber die Variante, bei der durch die Verwendung der TRUNC-Funktion die Histogramme auf den Basisspalten nicht mehr vom Optimizer berücksichtigt werden können, entsteht folgender Ausführungsplan:
select
count(t2.attr2)
from
t1
, t2
where
/*------------------*/
trunc(t1.attr1) = 1
and trunc(t1.attr2) = 1
/*------------------*/
and t1.fk = t2.id
;
---------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | HASH JOIN | | 1 | 100 | 900K|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 900K|
| 4 | TABLE ACCESS FULL| T2 | 1 | 1 | 1000K|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."FK"="T2"."ID")
3 - filter((TRUNC("T1"."ATTR1")=1 AND TRUNC("T1"."ATTR2")=1))
Note
-----
- this is an adaptive plan
Trotz der falschen Abschätzungen (E-Rows für den Zugriff auf T1 ist immer noch 100, wie bei Versionen vor 12c) schafft es Oracle 12c, die effizientere "großer Job"-Join-Strategie anzuwenden – wie ist das möglich? Die "Note" am Ende der Ausgabe gibt den Hinweis: Es handelt sich um das neue "Adaptive Join"-Feature. Fügt man dem DBMS_XPLAN.DISPLAY_CURSOR-Aufruf die in 12c neu eingeführte Formatierungsoption "ADAPTIVE" hinzu, ergibt sich folgende Ausgabe, die klarer macht, was zur Laufzeit passiert ist:
------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| * 2 | HASH JOIN | | 1 | 100 | 900K|
|- 3 | NESTED LOOPS | | 1 | 100 | 900K|
|- 4 | NESTED LOOPS | | 1 | 100 | 900K|
|- 5 | STATISTICS COLLECTOR | | 1 | | 900K|
| * 6 | TABLE ACCESS FULL | T1 | 1 | 100 | 900K|
|- * 7 | INDEX UNIQUE SCAN | T2_IDX | 0 | 1 | 0 |
|- 8 | TABLE ACCESS BY INDEX ROWID| T2 | 0 | 1 | 0 |
| 9 | TABLE ACCESS FULL | T2 | 1 | 1 | 1000K|
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."FK"="T2"."ID")
6 - filter((TRUNC("T1"."ATTR1")=1 AND TRUNC("T1"."ATTR2")=1))
7 - access("T1"."FK"="T2"."ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Oracle hat beide Join-Methoden vorgesehen. Mittels des "STATISTICS COLLECTOR"-Operator wird entschieden, welche der beiden Methoden tatsachlich ausgeführt wird, indem beim Erreichen eines vorher berechneten Schwellwertes die Umschaltung passiert. Man kann auch in der "Starts"-Spalte sehen, dass die Operationen 7 und 8 überhaupt nicht ausgeführt wurden und stattdessen die Operation 9 zusammen mit dem Hash Join in Operation 2 verwendet wurde.
Wie bereits erwähnt, passiert hinter den Kulissen sogar noch mehr in Oracle 12.1. So wird die Erzeugung sogenannter "SQL Plan Direktiven" durch die Fehleinschätzung getriggert, die dann bei wiederholtem Ausführen des Statements dazu führen, dass Oracle mittels "Dynamic Sampling" die Fehleinschätzung versuchen würde zu korrigieren (was in diesem Fall auch funktioniert) und außerdem bei nächster Gelegenheit und passendem Aufruf von DBMS_STATS erweiterte Statistiken für den verwendeten Ausdruck, in diesem Fall TRUNC(…), generieren würde, so dass die Oracle auch aufgrund dieser erweiterten Statistiken die Mengen wieder besser einschätzen kann und von vornherein den Hash Join favorisieren wird.
Wie aber bereits erwähnt, werden diese zusätzlichen Schritte in Oracle 12.2 wieder standardmäßig abgeschaltet, daher soll hier darauf nicht noch weiter eingegangen werden. Das "Adaptive Join"-Feature wird auch in Oracle 12.2 weiter zur Verfügung stehen.
Organisation der Daten
Für die Auswahl der richtigen Strategie ist also zum einen die Mengenabschätzung entscheidend. Um die Effizienz eines selektiven Zugriffs (zum Beispiel per Index) im Rahmen der "kleine Job"-Strategie zu bestimmen, ist aber noch ein anderes Kriterium elementar wichtig: Wie zusammenhängend oder durcheinander sind die Daten in der zu lesenden Tabelle organisiert, gemäß der Reihenfolge des jeweiligen Zugriffs?
Nehmen wir das Beispiel von oben: Bei der "kleine Job"-Strategie wird selektiv auf die Tabelle T2 zugegriffen, in dem für jede Zeile, die die Filterbedingung auf der Tabelle T1 erfüllt, per Index die passende Zeile in T2 gesucht wird. Für die Effizienz dieser Operation ist maßgeblich relevant, ob die jeweils passenden Zeilen in der Tabelle T2 gemäß der Reihenfolge der Daten aus T1 zusammenhängend im gleichen Bereich (in den gleichen Blöcken) gespeichert sind, oder ob zumeist für jede Zeile auf einen anderen Block zugegriffen werden muss. Bei diesem Zugriffsmuster ist es meistens diese Organisation der Tabellendaten, die die Effizienz der Operation bestimmt, da Indizes häufig viel kleiner sind als Tabellen und daher viel mehr vom Caching profitieren können. Das heisst, dass die Wahrscheinlichkeit für ein Caching der relevanten Index-Blöcke viel größer sein kann, als bei den entsprechenden Tabellen-Blöcken.
Die folgende Grafik stellt schematisch dar, wie der Zugriff von T1 auf T2 im Rahmen der "kleine Job"-Strategie aussieht:
Für jede Zeile, die in im Rahmen des Full Table Scan der Tabelle T1 dem Filterkriterium entspricht (repräsentiert im linken Teil der Grafik), wird der Index T2_IDX auf einen passenden Eintrag durchsucht. Dafür müssen je nach Höhe des Index eine bestimmte Anzahl von sogenannten Root und Branch-Blöcken des Index durchsucht werden, bis der entsprechende Leaf-Block des Index gefunden wurde, in dem schließlich überprüft werden kann, ob ein passender Eintrag in T2 existiert oder nicht (mittlerer Teil der Grafik).
Falls ein passender Eintrag im Index identifiziert wurde, muss normalerweise der Rest der benötigten Daten für diese Zeile, die nicht im Index abgebildet sind, von der Tabelle selbst geholt werden (rechter Teil der Grafik).
Obwohl der Zugriff auf die im Index referenzierte Tabellenzeile in den meisten Fällen mit einem einzigen Blockzugriff möglich ist (Ausnahmen sind zum Beispiel sogenannte "Migrated Rows"), bestimmt eben genau dieser Zugriff auf den Tabellen-Block in den meisten Fällen über die Effizienz dieser Operation.
Warum ist das so? Weil die Index-Blöcke aufgrund der Größe des Index und der Frequenz der Zugriffe meistens im Cache verbleiben können. Die Tabelle ist normalerweise viel größer und es kann je nach Art des Zugriffsmusters viel wahrscheinlicher sein, dass der gesuchte Block nicht im Cache ist und von Platte gelesen werden muss. Es handelt sich bei dem gezielten Lesen eines einzelnen Tabellen-Blocks um einen sogenannten "Random Access", da für jeden Durchlauf der Schleife und damit jede einzelne Zeile die in T1 gefunden und eine passende Zeile in T2 gesucht wird, theoretisch auf ein anderen Block der Tabelle T2 zugegriffen werden muss. Im schlechtesten Fall kann dies sogar dazu führen, dass der gleiche Block von T2 mehrfach von Platte gelesen werden muss, da der vorherige Zugriff auf den gleichen Block bereits wieder von nachfolgenden Zugriffen aus dem Cache verdrängt wurde.
Wird aber beim Durchlauf der Schleife für eine bestimmte Anzahl an Iterationen immer wieder auf den gleichen Block (oder eine kleinere Anzahl von Blöcken) der Tabelle T2 zugegriffen, bleibt der Block nach dem ersten Zugriff im Cache und muss nicht mehr von Platte gelesen werden. Dies kann dramatische Unterschiede für die Effizienz solcher Operationen bedeuten, da ein typischer "Random Access" auch von sehr schnellen Festplatten heutzutage immer noch zwischen 3 und 5 ms (Millisekunden) dauert (Massenspeicher ohne rotierende Massen wie SSDs können hier deutlich schneller sein), während der Zugriff auf einen Block im Cache im unteren Mikrosekunden-Bereich liegt. Legt man diese Zahlen einer einfachen Berechnung zugrunde, so benötigt der Zugriff auf 1000 Zeilen der Tabelle T2 alleine für das Lesen der 1000 Blöcke von Festplatte zwischen 3 und 5 Sekunden, unter Umständen auch deutlich länger, während das Zugreifen auf die gleichen Anzahl Blöcke aus dem Cache immer noch im Millisekunden-Bereich liegt.
Allgemein wird das eine Zugriffs-Muster "für jede identifizierte Zeile von T2 muss auf einen anderen Tabellen-Block zugegriffen werden" als "Scattered" bezeichnet, während das andere "für jede identifizierte Zeile von T2 kann wiederholt auf die gleichen Tabellen-Blöcke zugegriffen werden" als "Clustered" bezeichnet wird. Wichtig ist in diesem Zusammenhang zu verstehen, dass je nach Zugriffsart die gleiche Tabelle "Scattered" für eine Art von Zugriff sein kann, aber "Clustered" für eine andere. Genauer gesagt, kann eine Tabelle normalerweise nur für genau eine Zugriffsart "Clustered" sein und für alle anderen "Scattered".
Proaktives Design
Mit diesem Wissen ist die Optimierung solcher Zugriffe, die als kritisch für eine Anwendung identifiziert werden, proaktiv möglich. Gerade bei OLTP-Anwendungen, die häufig gezielt kleinere Datenmengen suchen, kann eben genau diese Organisation von Daten einen großen Unterschied in Bezug auf die Effizienz beim Datenzugriff machen. Daher sollten idealerweise schon während des Designs der Applikation, die Daten und die wichtigsten Abfragen auf diese Daten bekannt sein. Mit Hilfe dieses Wissens kann dann evaluiert werden, welche Art der Indizierung oder die Verwendung von anderen Speicherungsmethoden den Zugriff auf die Daten entscheidend verbessern kann, ohne beim Modifizieren der Daten zu viel Zeit zu verlieren.
Insbesondere die Verwendung von Clustern (Index Cluster oder Hash Cluster) oder Index-Organized Tables (IOTs) bieten je nach Anwendungsfall die Möglichkeit, die Organisation der Daten proaktiv zu beeinflussen und den Zugriff auf die Daten entscheidend zu beschleunigen. Natürlich können diese alternativen Speicherungsmethoden nicht ungesehen eingesetzt werden. Die genauen Zugriffsmuster, sowohl beim Lesen als auch Schreiben der Daten, müssen bekannt sein, da ansonsten leicht das Gegenteil erreicht werden kann: Sowohl Lese- als auch Schreibzugriff können deutlich ineffizienter werden.
Weiterhin wird ein Wissen über die spezifischen Verhaltensweisen von Clustern und IOTs benötigt, um vorab abschätzen zu können, welche der alternativen Speicherungsmethoden welche Vorteile und Nachteile mit sich bringen. Werden zum Beispiel viele sekundäre Indizes nötig sein, kann eine IOT zu insgesamt schlechterer Performance führen, da sekundäre Indizes für IOTs grundsätzlich anders funktionieren als für normale Tabellen. Cluster können aus verschiedenen Gründen sehr ineffizient werden und erlauben bestimmte Operationen und Zugriffsmuster, je nach Variante, nicht. Darüber hinaus unterstützen derzeitige Versionen keine Partitionierung von Clustern.
Insgesamt erscheint es also als sehr wichtig, bereits während der Designphase einer Applikation Wissen bezüglich der genannten Kernfragen aufzubauen, um die Organisation der Daten in der Datenbank optimal darauf abstimmen zu können.
