Ausführungspläne erzeugen und interpretieren

Bisher habe ich auf die Frage nach einer guten Erklärung für Ausführungspläne in der Regel auf Troubleshooting Oracle Performance von Christian Antognini verwiesen - oder auch auf die im Netz verfügbare (und hier verlinkte) gekürzte Version des sechsten Kapitels. Als Alternative oder Ergänzung dazu kommt inzwischen eine gerade von Jonathan Lewis für AllThingsOracle begonnene einführende Serie in Betracht:

• Execution Plans: Part 1 Finding plans: erklärt die verschiedenen Möglichkeiten, wie man Pläne anzeigen lassen kann (Explain Plan, Autotrace, dbms_xplan.display_cursor), und weist auf ihre besonderen Eigenschaften und Beschränkungen hin (Stichwort: Explain Plan und Bindevariablen).
• Execution Plans Part 2: Things to see: erläutert die Format-Optionen für dbms_xplan.display_cursor (Outline Data, Peeked Binds, Predicate Information). Außerdem werden die Möglichkeiten des Zugriffs auf historische Pläne im Automatic Workload Repository über dbms_xplan.display_awr erläutert. Neu war mir die Möglichkeit, eine Query über dbms_workload_repository. add_colored_sql({sql_id}) zu taggen, um dadurch sicher zu stellen, dass ein Statement im AWR nicht übersehen wird.
• Execution Plans Part 3: “The Rule”: erläutert die Grundregeln für die Interpretation von Plänen (deren Ausnahmen in folgenden Artikeln erklärt werden):
• Einrückungen in der Plandarstellung erklären die Semantik der Operationen.
• Jede Operation hat eine Id und nahezu jede eine ParentId.
• Der angebotene Merksatz für die Interpretation lautet "first child first, recursive descent" - was mir nicht allzu selbsterklärend erscheint; allerdings fällt mir auch nichts Besseres dazu ein. Entscheidend ist jedenfalls die Suche nach der am weitesten oben erscheinenden child-Operation ohne eigene Kinder, die den Start der Verarbeitung darstellt (was jetzt aber auch nicht griffiger formuliert ist).
• Pläne lassen sich in der Regel sehr gut in Teilbereiche zerlegen - "there are no hard plans, there are only long plans".
• Die Interpretation wird anhand eines übersichtlichen HASH JOIN Beispiels vorgeführt.
• Execution Plans Part 4: Precision and Timing: erklärt die Rolle blockierender Operationen: im Hash Join muss die in-memory hash table komplett aufgebaut sein, ehe die probe Zugriffe für die zweite Tabelle erfolgen können, die ihrerseits sukzessive ausgeführt werden können. Im Fall eines enstprechenden Merge Joins müssen beide Input-Mengen sortiert sein (sofern sie nicht schon sortiert sind - also etwa über einen entsprechenden Index eingelesen), es existieren also zwei blockierende Operationen. Im Fall des Merge-Joins zeigt der Plan mit rowsource statistics eine seltsame Angabe für die Starts der SORT JOIN Operation: die Sortierung findet natürlich nur einmal statt, aber der Schritt im Ausführungsplan wäre mit der sperrigeren Beschreibung "probe an in-memory sorted data set, but acquire it and sort it if it’s not already in memory" besser umschrieben. Es folgt eine kurze Erläuterung zu Access- und Filter-Prädikaten - erster dienen dem Zugriff, letztere führen eine Filterung der gelieferten Ergebnisse durch. Alle Beispiele profitieren von der Verwendung von Plänen mit rowsource statistcs.
• Execution Plans part 5: First Child Variations: zeigt einige Fälle, in denen die "first child first, recursive descent" Regel nicht greift. Betroffen sind unter anderem scalar subqueries (dort ist das letzte child des Select die treibende Operation) und subquery factoring.
• Execution Plans Part 6: Pushed Subqueries: zeigt - anhand von zwei sehr ähnlichen Beispielen -, dass die "first child first" Regel bei Verwendung von Pushed Subqueries nicht funktioniert, weil die zugehörige Filter-Bedingung nur in der predicate section des Plans repräsentiert ist. Dabei bedeutet das "Pushed" für die Subquery, dass sie zum frühestmöglichen Zeitpunkt ausgeführt wird. Dabei führt die Verschiebung der Subquery-Ausführung zu einer starken Veränderung der Reihenfolgen-Semantik im Plan. Der Aufbau der zugehörigen Test-Queries erfordert den Einsatz der Hints no_unnest (um zu verhindern, dass eine Subquery in einen Join umgeandelt wird) und push_subq (um das Pushen der Subquery zu erzwingen). Leider werden die Queries in den Filter-Prädikaten nicht korrekt dargestellt (sondern durch leere Klammern repräsentiert), wenn der Plan aus dem Speicher gezogen wird (dbms_xplan.display_cursor).
• Execution Plans Part 7: Query Blocks and Inline Views: erläutert die Rolle von Query Blocks, die man mit Hilfe des Hints qb_name explizit benennen kann und die in der Predicate Section sichtbar sind. Darüber hinaus wird auf die View-Elemente (VW_%), hingewiesen die als Ergebnisse von Transformationsprozessen im Plan erscheinen können (im Beispiel etwa durch die Umwandlung einer korrelierten Subquery in einen einfachen Join mit dem Subquery-Ergebnis). Außerdem weist Jonathan Lewis mal wieder darauf hin, dass der Optimizer kein Freund der ANSI-Join-Syntax ist - in gegebenen Fall funktioniert die Zuordnung der qb_name-Angaben nicht mehr so ganz, wenn ANSI-Joins verwendet werden (weil die interne Transformation von ANSI- in Native-Oracle-Syntax manchmal überraschende Dinge tut).
• Execution Plans Part 8: Cost, time, etc.: erläutert, wie man die Spalten des Execution Plans zu den Kosten und zum Timing interpretiert (also: Rows, Bytes, Cost und Time). Dabei entsprechen die Erklärungen denen, die der Autor an anderer Stelle gegeben hat, und die ich an anderer Stelle reproduziert habe - ich spare mir die Wiederholung. Insbesondere wird auf die diversen Ungenauigkeiten der Angaben (etwa bei den Bytes) hingewiesen.
• Execution Plans Part 9: Multiplication: zeigt die Schwierigkeiten, die sich bei der Interpretation von Plänen daraus ergeben können, dass child Operationen mehrfach ausgeführt werden. In diesem Fall ist die Berechnung der Kosten nicht folgerichtig - insbesondere, wenn nlj_batching bei einem Nested Loops im Spiel ist, weil die Kosten-Darstellung für Nested Loops diesen neuen Trick nicht berücksichtigt. Die Informationen sind folglich cum grano salis zu nehmen...
• Execution Plans Part 10: Guesswork: erläutert die Schwierigkeiten, die sich aus der Repräsentation von Filter-Steps im Plan ergeben - ein Aspekt ist dabei (wieder) die unbekannte Anzahl der Ausführungen (die im Plan ausgewiesenen cardinalities hängen dabei mit der Rolle von scalar subquery caching zusammen). In solchen Fällen muss die Analyse diverse Faktoren in den Plan hineinrechnen, die dort nicht explizit enthalten sind. Außerdem variieren die Schätzungen für cost und cardinality massiv, wenn unterschiedliche Zugriffsstrategien im Spiel sind (die im Beispiel über Hints hervorgerufen werden) - und das sollten sie natürlich nicht tun.
• Execution Plans Part 11: Actuals: beschäftigt sich mit rowsource statistics, workarea Informationen (samt einer Query zur Bestimmung potentiell interessanter Fälle) und der Interpretation der gelieferten Ausführungsstatistiken. Interessant ist dabei der Hinweis, dass der Zugriff auf v$sql_workarea einen Scan des gesamten library caches hervorruft und mit Bedacht durchgeführt werden sollte.
• Execution Plans Part 12: Cardinality Feedback: erklärt, wie man mit Hilfe von rowsource Statistiken und dem Vergeleich von E-rows und A-rows die entscheidenden Irrtümer der Cardinality-Schätzungen bestimmen kann. Insbesondere wird darauf hingewiesen, dass der erste größere Fehler bei der Abschätzungen alle folgenden Entscheidungen des Optimizers beeinflusst. In den zugehörigen Beispielen wird ein unglücklichen NL-Join analysiert, daneben werden Subquery-Ergebnis-Caching-Probleme beleuchtet (die sich ergeben, wenn die Runtime Engine nicht dazu in der Lage ist alle distinkten Ergebnisse einer Subquery im Speicher zu halten, so dass sich wiederholte Zugriffe ergeben). In einem Nebensatz wird das 11er Feature Cardinality Feedback angesprochen, das im Beispiel zu einer Reoptimierung und der Generierung ergänzender Hints führt, was im hier verlinkten Artikel und den zugehörigen Kommentaren genauer untersucht wurde.
• Execution Plans Part 13: SQL Trace: erläutert die Möglichkeiten der Aktivierung der Trace-Erfassung (Level, Optionen, Scope: Session oder Statement) und gibt knappe Hinweise zur Deutung der Ergebnisse im erzeugten File und seiner via tkprof formatierten Version.
• Execution Plans Part 14: SQL Monitoring: liefert Informationen zur Verfügbarkeit des Monitorings und zur Auswertung der Resultate in HTML und textueller Form.

Ein Index zu den Artikeln der Serie, den man als Antwort auf entsprechende Anfragen in Datenbank-Foren verwenden kann, findet sich hier.