Datenbanken Online Lexikon TH Köln, Campus Gummersbach
Aktuelle Änderungen - Suchen:

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