Oracle Optimizer

Die Aufgabe des Oracle-Optimizers besteht darin, einen effizienten Weg zum Ausführen von SQL-Anweisungen zu finden. Für jede auszuführende SQL-Anweisungen erstellt der Optimizer Ausführungspläne. Der Output des Optimizers ist der effizienteste Ausführungsplan. In einem Ausführungsplan werden die Schritte beschrieben, die der Optimizer folgen muss, um die vom Benutzer eingegebene SQL-Anweisung auszuführen. Dies ist erforderlich, weil SQL-Befehle nicht so ausgeführt werden können, wie diese vom Benutzer eingegeben wurden.


Architektur des Rule-Based-Optimizers (Quelle: www.if-dv.de)

Oracle bietet bzw. bot zwei Methoden der Optimierung: einen regelbasierten (Rule-Based-Optimizer) und kostenbasierten Optimizer (Cost-Based-Optimizer).

Rule-Based-Optimizer (RBO)

Der Rule-Based-Optimizer (RBO) verwendet festes Regelwerk zur Erstellung des Ausführungsplans anhand einer internen Rangliste von Zugriffsmethoden und wird hauptsächlich vom Aufbau der SQL-Anweisung beeinflusst, um den effizientesten Ausführungsplan zu schätzen. Der RBO wird nicht mehr von Oracle weiterentwickelt und steht ab Oracle 10g nicht mehr zur Verfügung.

Cost-Based-Optimizer (CBO)

Der Cost-Based-Optimizer (CBO) ist ein äußerst hoch entwickelter Bestandteil von Oracle und im Gegensatz zum Rule-Based-Optimizer errechnet er den Ausführungsplan anhand von Kosten. Zur Schätzung der Kosten jedes Ausführungsplans benötigt der Optimizer Statistiken. Statistiken beinhalten Datenverteilung und Speichereigenschaften von Tabellen, Spalten, Indizes, Partitionen und anderen Objekten der Datenbank. Seit Oracle 9 werden auch Systemdaten, wie I/O-Zugriff oder die CPU-Nutzung in den Statistiken mit einbezogen. Diese Informationen müssen erst gesammelt werden und werden anschließend im DATA DICTIONARY gespeichert.

Der Cost-Based-Optimizer umfasst insgesamt drei Komponenten, dem Query Transformer, dem Estimator und dem Plan Generator. Der Query Transformer erhält die geparste SQL-Abfrage und bestimmt, ob die SQL-Abfrage umgeschrieben bzw. verändert werden soll, um sie besser zu optimieren. Dafür gibt es verschiedene Techniken, die beim Query Transformer verwendet werden können, z.B. Join Elimination (JE), Filter Predicate(s) Generation from Constraints, Set to Join Conversion (SJC) oder Subquery Unnesting (SU). Die Join Elimination entfernt Joins in einer SQL-Abfrage, ohne das Funktionsverhalten zu verändern. Der Filter Predicate(s) Generation from Constraints erzeugt und fügt verschiedene Filter-Prädikate, wie Check, NOT NULL oder Constraints, in die SQL-Abfrage hinzu. Das Set to Join Conversion wandelt einen Operator mit zwei SQL-Abfragen, z.B. den Operator MINUS, in eine einzige SQL-Abfrage mit einem Join-Operator um. Die Subquery Unnesting bildet verschachtelte SQL-Unterabfragen? in einen Join um.


Architektur des Cost-Based-Optimizers (Quelle: docs.oracle.com)

Die zweite Komponente des kostenbasierten Optimizers ist der Estimator. Er erhält die veränderte SQL-Abfrage vom Query Transformer und greift auf die entsprechenden Statistiken aus dem DATA DICTIONARY zu, falls sie vorhanden sind. Der Estimator schätzt die gesamten Kosten eines gegebenen Ausführungsplans, dabei betrachtet er die Selektivität, die Kardinalität und die Ressourcennutzung der SQL-Abfrage. Die Statistiken verbessern die Genauigkeit der Schätzung.

Der Plan Generator versucht verschiedene Ausführungspläne für die erhaltene SQL-Abfrage zu bilden, indem er die Pläne mit verschiedenen Zugriffspfade, Join-Methoden und Anordnung der Tabellen? im Join untersucht. Für jede verschachtelte Unterabfrage? und nicht-zusammengeführte Views werden Teilpläne vom Optimizer generiert. Anschließend wählt der Plan Generator den Ausführungsplan mit den geringsten Kosten und leitet ihn an den Row Source Generator weiter. Der Optimizer verwaltet die Ausführungspläne automatisch und sorgt dafür, dass nur geprüfte Ausführungspläne verwendet werden. Mit dem SQL Plan Management (SPM) kann die Entwicklung von Ausführungspläne gesteuert werden, indem ein neuer Ausführungsplan nur verwendet wird, nachdem festgestellt wurde, dass er besser als der aktuelle Ausführungsplan ist.

Durch die Verwendung von Statistiken ist der Cost-Based-Optimizer dem Rule-Based-Optimizer im Allgemeinen deutlich überlegen und soll immer bevorzugt werden, damit der effizienteste Ausführungsplan berechnet werden kann und infolgedessen die SQL-Abfragen schneller ausgeführt werden können. Es ist aber wichtig zu beachten, dass die Entscheidungen des Optimizers über den Ausführungsplan nicht von einer Version von Oracle zu einer neueren Version übertragen werden können. In neueren Versionen kann der Optimizer unterschiedliche Entscheidungen treffen, weil bessere Informationen bzw. Statistiken zur Verfügung stehen.

Die Auswahl des Optimizers kann direkt in der Initialisierungsdatei init.ora durch den Parameter OPTIMIZER_MODE vorgenommen werden.

Tabelle: Einstellungen des Parameters OPTIMIZER_MODE

Modus des OptimizersBeschreibung
CHOOSEDer Modus CHOOSE verwendet entweder den Cost-Based-Optimizer (CBO), wenn Statistiken für die zugreifenden Tabellen und Indizes gesammelt wurden, oder den Rule-Based-Optimizer (RBO), wenn keine Statistiken vorhanden sind. Dieser Modus ist veraltet und steht ab Oracle 10g nicht mehr zur Verfügung. Das ist der Defaultwert bis Oracle 10g.
RULEBei RULE kommt nur der Rule-Based-Optimizer zum Einsatz. Dieser Modus ist veraltet und steht ab Oracle 10g nicht mehr zur Verfügung.
ALL_ROWSBei dem Modus ALL_ROWS wird der Cost-Based-Optimizer verwendet, der auf einmal alle Datensätze der Ergebnismenge zurückgibt. Das ist der Defaultwert ab Oracle 10g. Dieser Modus eignet sich sehr gut für Batchverarbeitung und Data-Warenhouse.
FIRST_ROWS oder FIRST_ROWS [n]Im Gegensatz zu ALL_ROWS können FIRST_ROWS und FIRST_ROWS [n] schon die ersten Datensätze (n Zeilen) zurückgeben, obwohl die Ausführung der SQL-Abfrage noch nicht zu Ende ist und Daten im Hintergrund immer noch abgerufen werden. Bei FIRST_ROWS erfolgen die Zugriffe normalerweise auf Indizes, aber es kann auch vorkommen, dass FULL TABLE SCANs bevorzugt werden, wenn Parallel Query verwendet wird. Dieser Modus eignet sich sehr gut für interaktive Client-Server-Umgebung (OLTP-Systeme). Bei FIRST_ROWS [n] kann die genaue Anzahl von Zeilen durch den Parameter n angegeben werden, die sofort zurückgeliefert werden sollen. Der Parameter n kann die Werte 1, 10, 100 oder 1000 annehmen.

Komponenten, die den Cost-Based-Optimizer beeinflussen (Quelle: jparnitzke.wordpress.com)

Der Cost-Based-Optimizer wird bei der Berechnung und der Auswahl des Ausführungsplans durch viele Konfigurationseinstellungen (Datenbank- und Cost-Based-Optimizer-Parameter) und die Hints (Hinweise) in der SQL-Abfrage beeinflusst.

Siehe auch: SQL-Tuning?, Ausführungsplan einer SQL-Abfrage, Ausführungsplanoperationen?, Statistiken, Hints, Werkzeuge zum SQL-Tuning

Quellen:

Kategorie: Tuning, O