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:
Regel wertet mögliche Ausführungspfade aus und beurteilt diese gemäß einer Reihe von Syntaxregeln.
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.
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.
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:
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:
partitionierte Tabellen
partitionierte Views
Index-organisierte Tabellen
Schlüsselindizes in umgekehrter Reihenfolge
Bitmap-Indizes
Parallele Abfrage und parallele DML
Star-Transformation
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.