Sortieroperationen des Ausführungsplans

Diese Operationen gehören zu den Ausführungsplanoperationen ? des Ausführungsplans ? und optimieren ? die Art, wie eine Datensatzmenge effizient sortiert werden kann. Sortieren ist eine ressourcenaufwändige Operation, die deutlich einsparen kann, wenn sie spezielle Eigenschaften der Datensatzmenge geschickt ausnutzt.

Oracle bietet die folgenden Sortieroperationen an:

SORT ORDER BY

Die Operation SORT ORDER BY ist ein Sortiervorgang, der durchgeführt wird, wenn eine ORDER BY-Klausel ohne GROUP BY-Klausel in der SQL-Abfrage angegeben wird. Das Ergebnis der Abfrage wird entsprechend der ORDER BY-Klausel sortiert.

Da SORT ORDER BY keine Ausgabezeilen in einer Zwischentabelle schreibt, muss das Ergebnis erst materialisiert werden, bevor die Zeilen zurückgegeben werden. SORT ORDER BY benötigt viel Speicher zum Materialisieren des Ergebnisses, weil keine Pipelined Execution-Strategie verwendet wird. Das heißt, erst wenn das Ergebnis feststeht, wird es zur nächsten Operation weiterleitet. Bei Pipelined Execution wird jede Zeile des Ergebnisses wie am Fließband direkt zur nächsten Operation weitergeleitet.

Bei SQL-Abfragen mit einer ORDER BY-Klausel wird das Ergebnis sortiert. Es ist aber möglich diese Sortierung zu ersparen, wenn der verwendete Index die Einträge in der gewünschten Reihenfolge liefert. Das heißt, derselbe Index, der die WHERE-Klausel abdeckt, muss auch in der ORDER BY-Klausel vorkommen. Die Datenbank nutzt die Indexreihenfolge aus und führt daher keine explizite Sortierung durch.

SORT UNIQUE

Die Operation SORT UNIQUE sortiert und eliminiert doppelte Einträge innerhalb der Bildung einer Ergebnismenge. Sie wird vom ORACLE-Optimizer oft ausgewählt, wenn das Schlüsselwort DISTINCT in der SQL-Abfrage verwendet wird oder wenn eine Operation einen eindeutigen Wert für den nächsten Schritt benötigt. Auch bei Mengenoperationen kann SORT UNIQUE verwendet werden. Wenn der ORACLE-Optimizer durch eindeutige Schlüssel sicher ist, dass doppelte Werte nicht weitergegeben werden, dann kann eine Sortierung vermieden werden.

SORT UNIQUE NOSORT

Die Operation SORT UNIQUE NOSORT eliminiert doppelte Einträge innerhalb der Bildung einer Ergebnismenge. Sie wird vom ORACLE-Optimizer oft ausgewählt, wenn das Schlüsselwort DISTINCT in der SQL-Abfrage verwendet wird oder wenn eine Operation einen eindeutigen Wert für den nächsten Schritt benötigt.

Im Gegensatz zu der Operation SORT UNIQUE wählt der ORACLE-Optimizer die Operation SORT UNIQUE NOSORT dann aus, wenn das Ergebnis bereits vorsortiert ist und keine weitere Sortierung bei der Eliminierung von doppelten Einträgen benötigt wird.

Ab ORACLE 10g wird sowohl für die GROUP BY-Operationen als auch für die UNIQUE-Operationen der Hash-Algorithmus standardmäßig bevorzugt. Diese Operationen können die SORT GROUP BY- bzw. SORT UNIQUE-Operation ersetzen. Der Hash-Aggregation-Algorithmus (HASH GROUP BY und HASH UNIQUE) ist effizienter als der Sort-Algorithmus.

SORT JOIN

Die Operation SORT JOIN ist ein Sortiervorgang, der vor der Ausführung der Join-Operation MERGE JOIN durchgeführt wird. Bei MERGE JOIN werden zwei Tabellen gejoint. Die einzelnen Ergebnismengen der Tabellen müssen unabhängig voneinander und entsprechend der Join-Schlüssel sortiert werden, bevor sie miteinander verknüpft werden. Die Ausgabe von SORT JOIN wird als Eingabe für die MERGE JOIN-Operation verwendet.

Der Sortiervorgang ist sehr aufwendig und braucht viel Speicherplatz. Der SORT MERGE JOIN-Algorithmus ist nur für Natural-Join und Equi-Join geeignet. Sortiervorgänge mit dem Hash-Algorithmus sind deutlich effizienter.

SORT AGGREGATE

Die Operation SORT AGGREGATE ist nicht an Sortiervorgängen beteiligt. Sie wird vom ORACLE-Optimizer oft verwendet, wenn Aggregierungsfunktionen in der SQL-Abfrage durchgeführt werden.

Aggregierungsfunktionen liefern nur einen Datensatz zurück. Sie greifen aber auf einer großen Menge von Daten zu, die sie zur Berechnung des Ergebnisses von der SQL-Abfrage brauchen. Beispiele von Aggregierungsfunktionen sind SUM(), MAX(), MIN(), AVG(), COUNT(), usw. Diese Funktionen werden häufig zusammen mit der GROUP BY-Klausel verwendet.

Ein Ausführungsplan mit der Operation SORT AGGREGATE kann aus einer SQL-Abfrage stammen, wie z.B.: SELECT COUNT(*), SUM(Gehalt) FROM Angestellte;

Die Operation SORT AGGREGATE wird dann ausgewählt, wenn nur ein Datensatz zurückgegeben wird. Wenn mehrere Zeilen zurückgegeben werden sollen, wählt der ORACLE-Optimizer den Sortiervorgang SORT JOIN vor einem MERGE JOIN, oder den SORT GROUP BY bei einer SQL-Abfrage mit einer GROUP BY-Klausel oder SORT ORDER BY bei einer SQL-Abfrage mit einer ORDER BY-Klausel aus.

Die Performance des Sortiervorgangs kann verbessert werden, wenn der Wert für den Parameter SORT_AREA_SIZE erhöht wird. Der Parameter SORT_AREA_SIZE wird aber nicht berücksichtigt, wenn der Parameter PGA_AGGREGATE_TARGET gesetzt ist und wenn der Parameter WORKAREA_SIZE_POLICY auf AUTO steht.

SORT GROUP BY

Der Sortiervorgang SORT GROUP BY kommt in einem Ausführungsplan vor, wenn eine GROUP BY-Klausel mit einer ORDER BY-Klausel in der SQL-Abfrage angegeben wird.

SORT GROUP BY wird verwendet, wenn Gruppen von den Datensätzen gebildet werden müssen. Eine Sortierung ist erforderlich, um die Zeilen in verschiedene Gruppen zu trennen.

Die Operation SORT GROUP BY verwendet die Pipelined Execution-Strategie. Bei der Durchführung leitet sie jede Zeile des Ergebnisses wie am Fließband direkt zur nächsten Operation weiter. Operationen, die keine Pipelined Execution-Strategie verwenden, leiten die Zeilen zur nächsten Operation weiter, erst wenn das gesamte Ergebnis feststeht. Solche Operationen benötigen viel Speicher zum Materialisieren des Ergebnisses.

SORT GROUP BY NOSORT

Die Operation SORT GROUP BY NOSORT kommt in einem Ausführungsplan vor, wenn eine GROUP BY-Klausel ohne eine ORDER BY-Klausell in der SQL-Abfrage angegeben wird. SORT GROUP BY NOSORT wird verwendet, wenn Gruppen von den Datensätzen entsprechend der GROUP BY-Klausel gebildet werden müssen. Im Gegensatz zu der Operation SORT GROUP BY wählt der ORACLE-Optimizer die Operation SORT GROUP BY NOSORT dann aus, wenn das Ergebnis bereits vorsortiert ist und keine weitere Sortierung beim Gruppieren benötigt wird.

Die Operation SORT GROUP BY NOSORT verwendet die Pipelined Execution-Strategie. Bei der Durchführung leitet sie jede Zeile des Ergebnisses wie am Fließband direkt zur nächsten Operation weiter. Operationen, die keine Pipelined Execution-Strategie verwenden, leiten die Zeilen zur nächsten Operation weiter, erst wenn das Ergebnis feststeht. Solche Operationen benötigen viel Speicher zum Materialisieren des Ergebnisses.

HASH GROUP BY

Durch die Operation HASH GROUP BY wird eine Gruppierung mit einer Hash-Tabelle durchgeführt. Die Hash-Tabelle enthält eine Zeile pro Gruppe. Die Eingabedaten werden gelesen und die zugeordneten Gruppen werden in der Hash-Tabelle gesucht. Die Aggregierungsfunktionen werden aktualisiert und die Gruppenzeile wird erneut in die Hash-Tabelle eingetragen. Wenn keine Gruppenzeile vorhanden ist, wird eine neue Gruppenzeile initialisiert und in die Hash-Tabelle eingetragen. Die Hash-Tabelle wird als Ergebnis zurückgegeben. Die Operation HASH GROUP BY kann auch parallel ausgeführt werden.

HASH GROUP BY muss das Ergebnis materialisieren, d.h. alle Zeilen des Ergebnisses werden berechnet, bevor er die erste Zeile zurückgibt. Dafür benötigt HASH GROUP BY viel Speicher, weil keine Pipelined Execution-Strategie verwendet wird. Das heißt, erst wenn das Ergebnis feststeht, wird es zur nächsten Operation weiterleitet. Bei Pipelined Execution wird jede Zeile des Ergebnisses wie am Fließband direkt zur nächsten Operation weitergeleitet. Die Ausgabe erfolgt in keiner bestimmten Reihenfolge.

Wenn es nicht genug Speicher zur Verfügung steht und die Hash-Tabelle nicht in den Speicher passt, wird die Eingabedaten in kleinere Zwischentabellen unterteilt, die dann rekursiv weiter unterteilt werden, bis sie in den Speicher passen.

Der ORACLE-Optimizer vermeidet die Erstellung von Ausführungsplänen mit dem Sortiervorgang HASH GROUP BY, wenn er feststellt, dass während der Abfrageausführung eventuell zu wenig Speicher zur Verfügung stehen könnte. Wenn es zu wenig Speicher für die Partitionen gibt, verwirft der Optimizer die Zwischenergebnisse von HASH GROUP BY und verwendet stattdessen eine interne Niedrig-Speicher-Methode.

HASH UNIQUE

Die Operation HASH UNIQUE sortiert und eliminiert doppelte Einträge innerhalb der Bildung einer Ergebnismenge. Sie wird vom ORACLE-Optimizer oft ausgewählt, wenn das Schlüsselwort DISTINCT in der SQL-Abfrage verwendet wird oder wenn eine Operation einen eindeutigen Wert für den nächsten Schritt braucht.

BUFFER SORT

Die Operation BUFFER SORT dient der Optimierung und Unterstützung bei der Ausführung einer SQL-Abfrage. Sie wird verwendet, um Daten im Speicher zu puffern und zu sortieren. ORACLE puffert die geschätzten Daten, um zu vermeiden, dass zusätzliche logische Input/Output-Zugriffe durchgeführt werden müssen, wenn die Schätzung des ORACLE-Optimizers falsch ist. Diese Sortierung ist effizienter als mit temporären Dateien. Die Zeilen der Ergebnismengen werden selbst nicht sortiert.

BUFFER SORT kommt häufig in SQL-Abfragen vor, die ein kartesisches Produkt oder eine CONNECT-BY-Klausel enthalten. Obwohl ein kartesisches Produkt durch die Operation MERGE JOIN CARTESIAN dargestellt wird, wird keine Sortierung SORT JOIN verwendet, was bei der Tabellenverknüpfung MERGE JOIN üblich wäre.

Siehe auch: Ausfuehrungsplanoperationen ?, SQL-Tuning ?, Optimizer, Ausführungsplan einer SQL-Abfrage, 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, S