Ausführungsplan einer SQL-Abfrage (Oracle)

Ein Ausführungsplan (Query Execute Plan) ist eine Zugriffsvorschrift auf die in der Datenbasis gespeicherten Daten. Er wird vom Oracle-Optimizer für SELECT-Anfragen sowie für DML-Anweisungen erstellt und dient zur detaillierten Bestimmung, wie auf die einzelnen Datensätze in den Tabellen?, Sichten, Indizes und andere Objekte in der Datenbank zugegriffen wird. Dieser Ausführungsplan ist eine prozedurale Darstellung der deklarativen SQL-Anfrage- bzw. -Manipulationsbefehle und gibt dem DBMS? (Datenbankmanagementsystem) eine Abarbeitungsfolge für den Zugriff auf die gespeicherten Datensätze vor.

Ein Ausführungsplan enthält Entscheidungen über die Join-Operatoren, die Zugriffsreihenfolge und die Zugriffsmethoden für jede Tabelle?, die in der SQL-Abfrage verwendet wird, sowie über den Zugriff auf den Index der Tabellen. Darüber hinaus kann der Ausführungsplan Views beinhalten, die nicht in der SQL-Abfrage referenziert werden, aber zur Berechnung von Teilen der Abfrage benutzt werden können. Wie auf die Daten zugegriffen wird, wird in einem Ausführungsplan durch Operationen? beschrieben.

Die Entscheidungen werden immer vor dem Hintergrund getroffen, einen möglichst kostengünstigen Plan zu erstellen. Ein Plan wird vom Oracle-Optimizer ausgewählt und gilt als kostengünstigster, wenn er das Ergebnis der SQL-Abfrage in kürzester Zeit und mit der geringsten Nutzung von Ressourcen liefert.

Standardmäßig berechnet der Oracle-Optimizer bei Oracle 9 bis zu 2000 unterschiedliche Ausführungspläne. Diese Anzahl kann aber durch den Parameter OPTIMIZER_MAX_PERMUTATIONS verändert werden. Je mehr Ausführungspläne erzeugt werden, desto länger dauert die Ausführung der SQL-Abfrage. Die Ausführungspläne werden aber nur erzeugt, wenn noch keine identische SQL-Abfrage ausgeführt wurde und noch keine Shared SQL-Area für die SQL-Abfrage vorhanden ist.

Beim SQL-Tuning? geht es darum, den ausgewählten Ausführungsplan zu analysieren und noch weiter zu optimieren. Eine Analyse des Ausführungsplans ist ohne weiteres nicht möglich, weil die Ausführungspläne intern erzeugt werden und nicht visualisiert werden können. Oracle bietet aber trotzdem eine Möglichkeit, den vom Optimizer ausgewählten Ausführungsplan anzuzeigen.

Durch den Befehl EXPLAIN PLAN FOR <SQL-Abfrage> wird die Ausführung einer SQL-Abfrage simuliert. Bei der Ausführung dieses Befehls berechnet der Oracle-Optimizer, wie gewöhnlich bei der Ausführung einer SQL-Abfrage, die Ausführungspläne und wählt den kostengünstigsten Ausführungsplan aus. Anstatt den ausgewählten Ausführungsplan an den Row Source Generator weiterleiten, wird er in der Tabelle PLAN_TABLE gespeichert, damit die Datenbankadministrator oder die Anwendungsentwickler ihn ausgeben und analysieren können.

Bevor der Befehl EXPLAIN PLAN FOR ausgeführt werden kann, muss die Tabelle PLAN_TABLE angelegt werden. Dafür gibt es das Skript utlxplan.sql, das sich normalerweise in dem Verzeichnis $ORACLE_HOME/rdbms/admin/ befindet. Ab Oracle 10g ist die Tabelle PLAN_TABLE schon bereits als globale temporäre Tabelle? vorhanden. Die Tabelle enthält die Spalten STATEMENT_ID und PLAN_ID, damit unterschiedliche Ausführungspläne von unterschiedlichen SQL-Abfragen gespeichert werden können. Man soll nicht versuchen, die Tabelle PLAN_TABLE selbst durch die CREATE-Anweisung erzeugen. Sonst können Fehler auftreten, die die Verwendung von EXPLAIN PLAN FOR beeinträchtigen können.

Table OwnerTable NameColumn NameData Type
<OWNER>PLAN_TABLESTATEMENT_IDVARCHAR2
  PLAN_IDNUMBER
  TIMESTAMPDATE
  REMARKSVARCHAR2
  OPERATIONVARCHAR2
  OPTIONSVARCHAR2
  OBJECT_NODEVARCHAR2
  OBJECT_OWNERVARCHAR2
  OBJECT_NAMEVARCHAR2
  OBJECT_ALIASVARCHAR2
  OBJECT_INSTANCENUMBER
  OBJECT_TYPEVARCHAR2
  OPTIMIZERVARCHAR2
  SEARCH_COLUMNSNUMBER
  IDNUMBER
  PARENT_IDNUMBER
  DEPTHNUMBER
  POSITIONNUMBER
  COSTNUMBER
  CARDINALITYNUMBER
  BYTESNUMBER
  OTHER_TAGVARCHAR2
  PARTITION_STARTVARCHAR2
  PARTITION_STOPVARCHAR2
  PARTITION_IDNUMBER
  OTHERLONG
  DISTRIBUTIONVARCHAR2
  CPU_COSTNUMBER
  IO_COSTNUMBER
  TEMP_SPACENUMBER
  ACCESS_PREDICATESVARCHAR2
  FILTER_PREDICATESVARCHAR2
  PROJECTIONVARCHAR2
  TIMENUMBER
  QBLOCK_NAMEVARCHAR2
  OTHER_XMLCLOB

In dem folgenden Beispiel wird der Aufbau des Befehls EXPLAIN PLAN FOR mit einer SQL-Abfrage gezeigt:

 EXPLAIN PLAN FOR  SELECT L.Lief_Nr, L.Name, COUNT(*)
                   FROM Lieferanten L, Lieferprogramme P, Teile T
                   WHERE T.TNr   = P.TNr
                   AND L.Lief_Nr = P.Lief_Nr
                   AND T.Typ     = 'Material'
                   GROUP BY L.Lief_Nr, L.Name
                   HAVING COUNT(*) = (SELECT COUNT(*) 
                                      FROM Teile 
                                      WHERE Typ = 'Material');

Nachdem die Tabelle PLAN_TABLE angelegt wurde und der Befehl EXPLAIN PLAN FOR <SQL-Abfrage> ausgeführt wurde, kann der Ausführungsplan einfach mit einer SELECT-Anweisung ausgegeben werden, z.B.:

 SELECT ID, Parent_ID, Operation, Option, Object_Name, Object_Typ, Optimizer, Cost, Cardinality, Bytes, CPU_Cost, IO_Cost, Time
 FROM PLAN_TABLE
 ORDER BY ID;


Ausführungsplan der SQL-Abfrage

Es ist wichtig zu beachten, dass die SQL-Abfrage durch den Befehl EXPLAIN PLAN FOR <SQL-Abfrage> nicht tatsächlich ausgeführt wird. Aus diesem Grund kann es vorkommen, dass einige Werte der Tabelle PLAN_TABLE nicht mit den wirklichen Werten der ausgeführten SQL-Abfrage übereinstimmen. Alle Werte des Ausführungsplans sind nur geschätzt.

Siehe auch: Performance-Tuning, SQL-Tuning?, Optimizer, Ausführungsplanoperationen?, Statistiken, Hints, Werkzeuge zum SQL-Tuning

Quellen:

  • Alapati, Sam R./Kuhn, Darl/Padfield, Bill: "Oracle Database 11g Performance Tuning Recipes: A Problem-solution Approach", Apress, 2011, ISBN 978-1-4302-3662-7
  • Burleson, Donald.: "Oracle SQL Tuning", Rampant TechPress, 2004, ISBN 978-0-9823061-5-4
  • Dyke, Julian: "Optimizer Operations, Hints, Features"
  • Haas, Frank: "Oracle Tuning in der Praxis", 10/2009, ISBN 3-446-41907-1
  • Hotka, Dan: "Oracle SQL Tuning“, Createspace, 2007, ISBN 978-0-07-148474-9
  • Niemiec, Richard J.:"Oracle Database 11g Release 2 Performance Tuning Tips & Techniques", Oracle Press, Osborne/McGrawHill, 2012, ISBN 978-0-07-178026-1
  • Price, Jason: "Oracle Database 11g SQL – Master SQL and PL/SQL in the Oracle Database", Kap. 16, Oracle Press, MC Graw Hill, 2007, ISBN 978-0-07-149850-0
  • Oracle® Database Dokumentation: SQL Language Reference 11g Release 2, E26088-02, 07/2012
  • Oracle® Database Dokumentation: SQL Performance Guide, E16638-07, 08/2012
  • Sideris Courseware Corp.: "Oracle Database 11g R2 SQL Tuning", Sideris Courseware, 2011, ISBN
  • The Oracle FAQ Site: Oracle SQL Tuning Guide
  • TAKTUM Informatik GmbH: "Oracle Tuning und Optimierung Tutorial", Büren

Kategorie: Tuning, A