Funktionsweise des Optimizer

Um Explain-Pläne zu verstehen, muss zuerst die Funktionsweise des Optimizer erläutert werden. Der Optimizer ist eine Funktion der Oracle-Datenbank, mit der die als am effizientesten geltende Art der Ausführung einer SQL-Anweisung ausgewählt wird. Dieser Schritt ist bei der Verarbeitung von DML-Anweisungen (Data Manipulation Language): SELECT, INSERT, UPDATE oder DELETE grundlegend. Es gibt häufig viele verschiedene Arten zur Ausführung einer SQL-Anweisung, z.B. durch Variieren der Reihenfolge, in der auf Tabellen oder Indizes zugegriffen wird. Die Prozedur, die von Oracle zur Ausführung einer Anweisung verwendet wird, kann sich stark auf die Geschwindigkeit der Anweisungsausführung auswirken. Die Zugriffspfade, die der Optimizer verwendet, können durch Generieren von Explain-Plänen angezeigt werden.

Der Optimizer verfügt über vier Operationsmodi: Regel, Kosten First Rows, Kosten All Rows und Wählen. Der von Ihnen gewählte Modus steuert die Verfahrensweise des Optimizer:

vtc00090000.gif Regel wertet mögliche Ausführungspfade aus und beurteilt diese gemäß einer Reihe von Syntaxregeln.

vtc00090000.gif Kosten First Rows führt die Anweisung so aus, dass die erste Datenzeile am effizientesten abgerufen wird. Dabei liegt das Gewicht auf der Geschwindigkeit, mit der abgerufen wird.

vtc00090000.gif Kosten All Rows führt die Anweisung so aus, dass die Gesamtleistung am wenigsten beeinträchtigt wird. Dabei liegt das Gewicht auf der Geschwindigkeit des Gesamtdurchsatzes.

vtc00090000.gif Wählen ruft den Modus Kosten All Rows auf, wenn Tabellen analysiert wurden, bzw. den regelbasierten Modus, wenn keine Tabellen analysiert wurden.

Sie können ein Standardziel für den Optimizer festlegen, indem Sie den Parameter OPTIMIZER_MODE in der Datei init.ora Ihrer Datenbank angeben, oder Sie können das Standardziel auf Sitzungsebene durch einen ALTER SESSION-Befehl ändern. Außerdem können Sie den Optimizer für eine bestimmte SQL-Anweisung durch Hinzufügen von Hints festlegen. Die Unterschiede zwischen dem regelbasierten und dem kostenorientierten Verfahren werden unten aufgeführt.

Das regelbasierte Verfahren

Beim regelbasierten Verfahren wählt der Optimizer einen Ausführungsplan, der auf den verfügbaren Zugriffspfaden sowie den Rangfolgen dieser Zugriffspfade basiert. Die regelbasierte Optimierung kann für den Zugriff auf relationale Daten und Objekttypen verwendet werden. Die Rangfolge, die Oracle für die Zugriffspfade verwendet, ist heuristisch. Falls es mehrere Möglichkeiten zur Ausführung einer SQL-Anweisung gibt, wird beim regelbasierten Verfahren stets der Vorgang mit der niedrigeren Rangfolge verwendet. Vorgänge mit einer niedrigeren Rangfolge werden meist schneller ausgeführt als Vorgänge, denen Konstrukte mit höheren Rangfolgen zugeordnet sind.

Das kostenorientierte Verfahren

Beim kostenorientierten Verfahren legt der Optimizer den effizientesten Ausführungsplan fest, indem er die verfügbaren Zugriffspfade berücksichtigt und Informationen miteinbezieht, die auf Statistiken im Data Dictionary für Schemaobjekte (Tabellen, Cluster oder Indizes/Partitionen) basieren, auf die die Anweisung zugreift. Beim kostenorientierten Verfahren werden ebenfalls Hints oder Optimierungsvorschläge berücksichtigt, die in einem Kommentar in der Anweisung gespeichert werden.

Das kostenorientierte Verfahren setzt sich aus folgenden Schritten zusammen:

  1. Der Optimizer generiert basierend auf den verfügbaren Zugriffspfaden und Hints eine Reihe von potentiellen Ausführungsplänen für die Anweisung.

  2. Der Optimizer schätzt die Kosten für jeden Ausführungsplan basierend auf der Datenverteilung und der Speichereigenschaften-Statistik für die Tabellen, Cluster und Indizes im Data Dictionary.

  3. Die Kosten stellen einen geschätzten Wert dar, der sich proportional zur erwarteten Ressourcennutzung verhält, die für die Anweisungsausführung mit dem Ausführungsplan benötigt wird. Der Optimizer berechnet die Kosten basierend auf den geschätzten Computerressourcen, die für die Ausführung der Anweisung mit dem Plan erforderlich sind, einschließlich (jedoch nicht beschränkt auf) E/A, CPU-Zeit und -Speicher.

Die Ausführung serieller Ausführungspläne mit höheren Kosten nimmt mehr Zeit in Anspruch als solche mit geringeren Kosten. Bei Verwendung eines parallelen Ausführungsplans steht die Ressourcennutzung nicht direkt in Bezug zur abgelaufenen Zeit.

4. Der Optimizer vergleicht die Kosten der Ausführungspläne und wählt den kostengünstigsten Plan.

Hinweis: Sie müssen das Schema Ihrer Datenbank mit dem Befehl ANALYZE analysieren, um die Statistiken auf einem aktuellen Stand zu halten. Wenn das Datenbankschema nicht regelmäßig analysiert wird, verwendet der Optimizer möglicherweise ungültige Statistiken und wählt nicht die effektivste Zugriffsmethode.

Ziel des kostenorientierten Verfahrens

Das Ziel des kostenorientierten Verfahrens ist standardmäßig der beste Durchsatz oder die geringste Ressourcennutzung, die für die Verarbeitung aller Zeilen erforderlich ist, auf die die Anweisung zugreift.

Oracle kann eine Anweisung auch im Hinblick auf die beste Antwortzeit oder die geringste Ressourcennutzung optimieren, die für die Verarbeitung der ersten Zeile erforderlich ist, auf die die SQL-Anweisung zugreift.

Statistiken für das kostenorientierte Verfahren

Beim kostenorientierten Verfahren werden Statistiken zur Schätzung der Kosten jedes Ausführungsplans verwendet. In diesen Statistiken werden die Datenverteilung und die Speichereigenschaften von Tabellen, Spalten, Indizes und Partitionen gemessen. Sie können diese Statistiken mit dem Befehl ANALYZE generieren. Der Optimizer verwendet diese Statistiken, um zu schätzen, wieviel E/A, CPU-Zeit und Speicher für die Ausführung einer SQL-Anweisung mit einem bestimmten Ausführungsplan erforderlich ist.

Wann wird das kostenorientierte Verfahren verwendet

Mit Oracle SQL Analyze können Sie sowohl regelbasierte als auch kostenorientierte Verfahren testen. Im Allgemeinen sollten Sie jedoch das kostenorientierte Verfahren für alle neuen Anwendungen verwenden. Das regelbasierte Verfahren dient Anwendungen, die geschrieben wurden, bevor die kostenorientierte Optimierung zur Verfügung stand. Die kostenorientierte Optimierung kann effektiver sein, da ihre Auswertung auf den neuesten Informationen über die Leistung Ihrer Datenbank basiert. Sie wählt im Allgemeinen einen Ausführungsplan, der gleich gut oder besser als der beim regelbasierten Verfahren gewählte Plan ist. Dies gilt insbesondere bei umfangreichen Abfragen mit mehreren Joins oder Indizes.

Da der kostenorientierte Optimizer jedoch stark von Objektstatistiken abhängt, wird dringend empfohlen, diese Statistiken in regelmäßigen Abständen mit dem Befehl ANALYZE zu aktualisieren.

Für folgende Funktionen kann ausschließlich die kostenorientierte Optimierung verwendet werden:

vtc00090000.gif partitionierte Tabellen

vtc00090000.gif partitionierte Views

vtc00090000.gif Index-organisierte Tabellen

vtc00090000.gif Schlüsselindizes in umgekehrter Reihenfolge

vtc00090000.gif Bitmap-Indizes

vtc00090000.gif Parallele Abfrage und parallele DML

vtc00090000.gif Star-Transformation

vtc00090000.gif Star-Join

Die kostenorientierte Optimierung muss für eine effiziente Star-Abfrage-Leistung verwendet werden. Genauso muss sie für Hash-Joins und Histogramme verwendet werden.

Die kostenorientierte Optimierung wird stets für parallele Abfragen und partitionierte Tabellen verwendet.