Dienstag, April 08, 2014

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.
      Weitere Artikel zur Plan-Interpretation sind angekündigt und werden hier ergänzt werden.

      Keine Kommentare:

      Kommentar veröffentlichen