Ausführungspläne und -planoperatoren relationaler Datenbankmanagementsysteme Christoph Koch Katharina Büchse Friedrich-Schiller-Universität Jena Friedrich-Schiller-Universität Jena Lehrstuhl für Datenbanken und Lehrstuhl für Datenbanken und Informationssysteme Informationssysteme Ernst-Abbe-Platz 2 Ernst-Abbe-Platz 2 07743 Jena 07743 Jena Christoph.Koch@uni-jena.de Katharina.Buechse@uni-jena.de KURZFASSUNG weise kritischen Bereichen wie etwa Sortierungen von umfang- Ausführungspläne sind ein Ergebnis der Anfrageoptimierung reichen Zwischenergebnissen. DBMS-übergreifende Werkzeuge relationaler Datenbankoptimierer. Sie umfassen eine Menge von zur Ausführungsplananalyse existieren dagegen kaum. Ausnah- Ausführungsplanoperatoren und unterscheiden sich je nach Daten- men davon beschränken sich auf Werkzeuge wie Toad [2] oder bankmanagementsystem auf verschiedene Weise. Dennoch reprä- Aqua Data Studio [3]. Diese können zwar DBMS-übergreifend sentieren sie auf abstrakter Ebene inhaltlich ähnliche Informatio- Ausführungspläne verarbeiten, verwenden dazu allerdings je nach nen, sodass es für grundlegende systemübergreifende Analysen DBMS separate Speziallogik, sodass es sich intern ebenfalls um oder auch Interoperabilitäten naheliegt einen einheitlichen Stan- quasi eigene „Werkzeuge“ handelt. dard für Ausführungspläne zu definieren. Der vorliegende Beitrag Sonstige dient dazu, die Grundlage für einen derartigen Standard zu bilden und stellt für die am Markt dominierenden Datenbankmanage- Oracle mentsysteme Oracle, MySQL, SQL-Server, PostgreSQL, DB2 15.25% DB2 (LUW und z/OS) ihre Ausführungspläne und -planoperatoren 28.57% anhand verschiedener Kriterien vergleichend gegenüber. PostgreSQL Kategorien und Themenbeschreibung Database Performance, Query Processing and Optimization 22.65% Allgemeine Bestimmungen 24.52% Documentation, Performance, Standardization, Languages Microsoft Schlüsselwörter SQL Server MySQL relationale DBMS, Ausführungsplan, Operator, Vergleich Abbildung 1: Top 5 der relationalen DBMS 1. EINLEITUNG Je nach DBMS unterscheiden sich Ausführungspläne und darin In der Praxis sind Ausführungspläne ein bewährtes Hilfsmittel befindliche Ausführungsplanoperatoren in verschiedenen Aspek- beim Tuning von SQL-Anfragen in relationalen Datenbank- ten wie Umfang und Format. Inhaltlich wiederum sind sie sich managementsystemen (DBMS). Sie werden vom Optimierer jedoch recht ähnlich, sodass die Idee einer DBMS-übergreifenden berechnet und bewertet, sodass abschließend nur der (vermeint- Standardisierung von Ausführungsplänen naheliegt. Der vor- lich) effizienteste Plan zur Bearbeitung einer SQL-Anfrage liegende Beitrag evaluiert diesen Ansatz. Dazu vergleicht er die benutzt wird. Jeder Ausführungsplan repräsentiert eine Menge Ausführungspläne und Ausführungsplanoperatoren für die Top 5 von miteinander verknüpften Operatoren. Zusätzlich umfasst er in der in [4] abhängig von ihrer Popularität gelisteten relationalen der Regel Informationen zu vom Optimierer für die Abarbeitung DBMS (siehe Abbildung 1) anhand unterschiedlicher Kriterien. geschätzten Kosten hinsichtlich CPU-Last und I/O-Zugriffen. Im Detail werden dabei Oracle 12c R1, MySQL 5.7 (First Release Auf Basis dieser Daten bewerten DBMS-spezifische Werkzeuge Candidate Version), Microsoft SQL-Server 2014, PostgreSQL 9.3 wie beispielsweise der für DB2 for z/OS im InfoSphere Optim sowie die bedeutendsten DBMS der DB2-Familie DB2 LUW 10.5 Query Workload Tuner verfügbare Access Path Advisor [1] die und DB2 z/OS 11 einander gegenübergestellt. Qualität von Zugriffspfaden und geben Hinweise zu möglicher- Der weitere Beitrag gliedert sich wie folgt: Kapitel 2 gibt einen Überblick über die für den Vergleich der Ausführungspläne und Ausführungsplanoperatoren gewählten Kriterien. Darauf aufbau- end vergleicht Kapitel 3 anschließend die Ausführungspläne der ausgewählten DBMS. Kapitel 4 befasst sich analog dazu mit den Ausführungsplanoperatoren. Abschließend fasst Kapitel 5 die 27th GI-Workshop on Foundations of Databases (Grundlagen von Daten- Ergebnisse zusammen und gibt einen Ausblick auf weitere mög- banken), 26.05.2015 - 29.05.2015, Magdeburg, Germany. liche Forschungsarbeiten. Copyright is held by the author/owner(s). 48 2. VERGLEICHSKRITERIEN Ergebniszeile), Aufwand (in unterstützten Metriken), Projektionen Ausführungspläne und –planoperatoren lassen sich anhand (Liste der Spalten der Ergebniszeile) und Aliase (eindeutige Ob- verschiedener, unterschiedlich DBMS-spezifischer Merkmale jektkennung). Es soll abgebildet werden, welche dieser Details miteinander vergleichen. Der folgende Beitrag trennt dabei strikt allgemein für Ausführungsplanoperatoren abhängig vom DBMS zwischen allgemeinen Plan-Charakteristiken und Operator-spezi- einsehbar sind. Für den Aufwand wird dabei nochmals unter- fischen Aspekten. Diese sollen nun näher erläutert werden. schieden in absoluten Aufwand des einzelnen Operators, kumula- tiven Aufwand aller im Plan vorangehenden Operatoren (ein- 2.1 Ausführungspläne schließlich dem aktuellen) und einem Startup-Aufwand. Letzterer Der Vergleich von Ausführungsplänen konzentriert sich auf bezeichnet den kumulativen Aufwand, der notwendig ist, um den externalisierte Ausführungspläne, wie sie vom Anwender über ersten „Treffer“ für einen Operator zu ermitteln. bereitgestellte DBMS-Mechanismen zur Performance-Analyse Zugriffsoperatoren sind Operatoren, über die auf gespeicherte erstellt werden können. DBMS-intern vorgehaltene Pläne und die Daten zugegriffen wird. Die Daten können dabei sowohl physisch Kompilierprozesse von Anfragen, bei denen sie erstellt werden, in Datenbankobjekten wie etwa Tabellen oder Indexen persistiert sollen vernachlässigt werden. Einerseits sind sie als inhaltlich sein, oder als virtuelle beziehungsweise temporäre Zwischener- äquivalent anzusehen, andererseits sind ihre internen gebnisse vorliegen. Ähnlich dazu erfolgt die Einteilung der Zu- Speicherstrukturen im Allgemeinen nicht veröffentlicht. Für den griffsoperatoren in tableAccess-, indexAccess-, generatedRow- Vergleich von Ausführungsplänen wurden im Rahmen des Access- und remoteAccess-Operatoren. TableAccess und index- Beitrags folgende Kriterien ausgewählt. Access bezeichnen jeweils den Tabellenzugriff und den Index- Erzeugung bezieht sich auf den Mechanismus, mit dem durch zugriff auf in den gleichnamigen Objekten vorgehaltenen Daten. den Anwender Zugriffpläne berechnet und externalisiert werden Unter generatedRowAccess sind Zugriffe zu verstehen, die im können. Dies sind spezielle Anfragen oder Kommandos. eigentlichen Sinn keine Daten lesen, sondern Datenzeilen erst generieren, beispielsweise um definierte Literale oder spezielle Speicherung ist die Art und Weise, wie und wo externalisierte Registerwerte wie etwa „USER“ zu verarbeiten. RemoteAccess Zugriffspläne DBMS-seitig persistiert werden. In der Regel han- repräsentiert Zugriffe, die Daten aus externen Quellen lesen. Bei delt es sich dabei um tabellarische Speicherformen. diesen Quellen handelt es sich in der Regel um gleichartige Ausgabeformate/Werkzeuge erfassen die Möglichkeit zur Aus- DBMS-Instanzen auf entfernten Servern. Für weitere spezielle gabe von Zugriffsplänen in unterschiedlichen Formaten. Dabei Zugriffsoperatoren, die sich nicht in eine der genannten Katego- wird zwischen rein textueller, XML- und JSON-basierter, sowie rien einteilen lassen, verbleibt die Gruppe otherAccess. visueller und anderweiter Ausgabe differenziert. Unterstützt ein Zwischenverarbeitungsoperatoren entsprechen Operatoren, die DBMS ein Ausgabeformat, erfolgt zusätzlich die Angabe des da- bereits gelesene Daten weiterverarbeiten. Dazu zählen Verbund- zu zu verwendenden, vom DBMS-Hersteller vorgesehenen Werk- (join), Mengen- (set), Sortier- (sort), Aggregations- (aggregate), zeugs. Drittanbieterlösungen werden nicht berücksichtigt. Filter- (filter) und Bitmap-Operatoren (bitmap). Spezielle Planumfang gibt Aufschluss über im Zugriffsplan verzeichnete Zwischenverarbeitungsoperatoren, die sich nicht einordnen lassen, generelle Verarbeitungsabläufe. Dahingehend wird verzeichnet, werden in einer separaten Kategorie otherIntermediate erfasst. ob Index- und „Materialized Query Table (MQT)“-Pflegeprozesse Manipulationsoperatoren sind Operatoren, die geänderte Daten sowie Prüfungen zur Erfüllung referentieller Integrität (RI) bei in physische oder auch temporäre Datenbankobjekte schreiben. Datenmanipulationen im Zugriffplan berücksichtigt werden. Mit Abhängig von in SQL standardisierten Manipulationsanfragen MQTs sind materialisierte Anfragetabellen gemeint, die je nach erfolgt die grundsätzliche Einteilung in die Operatoren insert, DBMS auch als materialisierte Sichten (MV) oder indexierte Sicht update, delete und merge. Zusätzlich werden auch remoteManipu- (IV) bezeichnet werden. lation-Operatoren unterschieden, die analog dem bereits betrach- Aufwandsabschätzung/Bewertungsmaße bezieht sich auf Me- teten remoteAccess eine Datenmanipulation auf einem fernen triken, mit denen Aufwandsabschätzungen im Zugriffsplan ausge- Server durchführen. Analog den vorangehenden Operatorklassen wiesen werden. Es wird unterschieden zwischen CPU-, I/O- und werden spezielle, nicht kategorisierbare Manipulationsoperatoren Gesamtaufwand. Sofern konkrete Bewertungsmaße des DBMS unter otherManipulation geführt. bekannt sind, wie etwa, dass CPU-Aufwand als Anzahl von Pro- Rückgabeoperatoren umfassen Operatoren, die die Wurzel eines zessorinstruktionen zu verstehen ist, werden diese mit erfasst. (hierarchischen) Ausführungsplans bilden. Oftmals repräsentieren 2.2 Ausführungsplanoperatoren diese nochmals den Typ des SQL-Statements im Sinne einer Un- Die Vergleichskriterien für die Ausführungsplanoperatoren gehen terscheidung in SELECT, INSERT, UPDATE, DELETE und über einen reinen Vergleich hinaus. Während einerseits anhand MERGE. verschiedener Aspekte eine generelle Gegenüberstellung von vor- handenen Operatordetails erfolgt, befasst sich ein überwiegender 3. AUSFÜHRUNGSPLÄNE IN RDBMS Teil der späteren Darstellung mit der Einordnung DBMS- Ausführungspläne sind grundlegende Elemente in der (relatio- spezifischer Operatoren in ein abstrahiertes, allgemeingültiges nalen) Datenbanktheorie. Damit sind sie zwar allgemein DBMS- Raster von Grundoperatoren. Dieses gliedert sich in Zugriffs-, übergreifend von Bedeutung, unterscheiden sich jedoch im Detail Zwischenverarbeitungs-, Manipulations- sowie Rückgabeopera- voneinander. So existiert beispielsweise für die im Beitrag ge- toren. Sowohl die Operatordetails, als auch die zuvor genannten wählten Vergleichskriterien und den daran bewerteten Systemen Operatorklassen werden nachfolgend näher beschrieben. kein Merkmal, in dem sich alle DBMS in ihren Ausführungs- plänen gleichen. Abbildung 2 veranschaulicht die Ergebnisse des Operatorendetails umfassen die Vergleichskriterien Rows (An- Vergleichs, auf die gegliedert nach System nun ausführlicher Be- zahl an Ergebniszeilen), Bytes (durchschnittliche Größe einer zug genommen wird. 49 Aufwandsabschätzung Ausgabeformate/Werkzeuge Planumfang Erzeu- /Bewertungsmaße DBMS Speicherung gung Index- MQT-/MV- RI- Ge- TEXT XML JSON andere grafisch CPU I/O pflege /IV-Pflege Prüfung samt DBMS_XPLAN. SQL Devel- proportional proportional DISPLAY, oper, zur Anzahl an zur Anzahl Oracle EXPLAIN DBMS_XPLAN. DBMS_XPLAN. Enterprise Maschinen- gelesener (12c R1) PLAN PLAN_TABLE DISPLAY_PLAN DISPLAY_PLAN - HTML Manager - - - zyklen Datenblöcke x MySQL MySQL (5.7) EXPLAIN (nur intern) TRADITIONAL - JSON - Work-bench - - - x x x SHOWPLAN_TEXT, Microsoft (stets mit STATISTICS PROFILE, SQL-Server Ausgabe SHOWPLAN_ALL SHOWPLAN_XML, Manage- (2014) verknüpft) (nur intern) (mehr Details) STATISTICS XML - - ment Studio x x x x x x EXPLAIN PostgreSQL [ANALYZE| (9.3) VERBOSE] (nur intern) TEXT XML JSON YAML pgAdmin - - - - - x DB2 LUW tabellarisch db2exfmt/ Data Studio, CPU- Datenseiten- Time- (V10.5) EXPLAIN (17 Tabellen) db2expln - - - OQWT - x x Instruktionen I/Os rons DB2 z/OS tabellarisch Data Studio, Millisekunden, (V11) EXPLAIN (20 Tabellen) - - - - OQWT - - - Service Units x x Abbildung 2: Vergleich der Ausführungspläne Oracle [5, 6]: Ausführungspläne werden in Oracle über die SQL- Sobald diese aktiviert sind, werden für nachfolgend ausgeführte Anfrage EXPLAIN_PLAN erstellt und daraufhin innerhalb einer SQL-Anfragen automatisch Pläne in den Formaten TEXT oder speziellen Tabelle mit der Bezeichnung PLAN_TABLE gespei- XML erstellt. Eine grafische Ausgabe bietet das Management Stu- chert. Jede Zeile dieser Tabelle enthält Informationen zu einem dio. Bezogen auf den Planumfang sind Informationen zu sämtli- Planoperator und bildet deren Kombination im Ausführungsplan chen betrachteten Prozessen enthalten. Dies gilt analog für die über die Spalten ID und PARENT_ID ab. Zusätzlich zur Möglich- Aufwandsabschätzung, für die CPU-, I/O- und Gesamtaufwand keit, per SQL-Anfrage Ausführungspläne aus der PLAN_TABLE ausgewiesen werden. Maßeinheiten sind allerdings nicht bekannt. auszulesen, unterstützt Oracle die Planausgabeformate TEXT, XML und HTML. Diese können jeweils über die im Package PostgreSQL [10]: Ausführungspläne werden in PostgreSQL mit- DBMS_XPLAN enthaltenen Tabellenfunktionen DISPLAY und tels SQL-Anfrage EXPLAIN erstellt und ohne explizite Spei- deren Erweiterung DISPLAY_PLAN erzeugt werden. Ebenfalls cherung direkt ausgegeben. Soll der Plan dabei zusätzlich ausge- möglich in Oracle ist die grafische Ausgabe von Ausführungs- führt werden, muss dies mittels Schlüsselwort ANALYZE prokla- plänen, entweder über das Werkzeug SQL-Developer oder den miert werden. Über ein weiteres Schlüsselwort VERBOSE lassen mächtigeren Enterprise Manager. Hinsichtlich des Planumfangs sich besonders ausführliche Informationen abrufen. Auch das sind Ausführungspläne in Oracle sehr beschränkt. Weder Index- Format, in welchem der Plan ausgegeben werden soll, lässt sich noch MV-Prozesse noch RI-Prüfungen werden im Ausführungs- mit entsprechendem Schlüsselwort (FORMAT {TEXT | XML | plan als Operatoren ausgewiesen und sind damit nur in einem JSON | YAML}) angeben. Eine grafische Ausgabe bietet das erhöhten Anfrageverarbeitungsaufwand ersichtlich. Aufwände Werkzeug pgAdmin. Zu beachten ist zudem, dass PostgreSQL die allgemein weist Oracle in allen betrachteten Metriken CPU, I/O Gültigkeit der Anfrage beim Erstellen des Plans (ohne Schlüs- und als Gesamtaufwand aus. Während der CPU-Aufwand pro- selwort ANALYZE) nicht überprüft. Bezüglich des Planumfangs portional zu den Maschineninstruktionen und der I/O-Aufwand macht PostgreSQL keine Angaben über Indexpflege oder die Prü- zur Anzahl gelesener Datenblöcke ausgewiesen werden, existiert fung referentieller Integrität. Es existieren zwar materialisierte für die sich daraus berechneten Gesamtkosten keine Maßeinheit. Sichten, diese können aber lediglich manuell über den Befehl REFRESH aktualisiert werden. Für die Aufwandsabschätzung MySQL [7]: Ausführungspläne werden in MySQL mittels SQL- liefert PostgreSQL nur „costs“, deren Einheit die Zeit darstellt, die Anfrage EXPLAIN erstellt und direkt in Tabellenform ausgege- für das Lesen eines 8KB-Blocks benötigt wird. ben. Eine explizite Speicherung der Pläne erfolgt nicht. Seit der Version 5.6 besteht auch die Möglichkeit, sich die Ausführungs- DB2 LUW [13]: Ausführungspläne werden in DB2 LUW über pläne in MySQL Workbench grafisch darstellen oder sie mit er- die SQL-Anfrage EXPLAIN erstellt und in einem Set von 20 weiterten Details im JSON-Format ausgeben zu lassen. Bei der Explain-Tabellen gespeichert. Für die Ausgabe der darin abge- Planerstellung wird in MySQL die Gültigkeit der Anfrage nicht legten Informationen besteht neben dem Auslesen per SQL-An- überprüft, es können also Pläne für Anfragen erstellt werden, die frage nur die Möglichkeit der textuellen Darstellung mithilfe der unzulässig sind (beispielsweise kann beim Einfügen der Datentyp mitgelieferten Werkzeuge db2exfmt und db2expln. Über das Data falsch sein). Auch bezüglich des Planumfangs zeigt sich MySQL Studio und den Infosphere Optim Query Workload Tuner können eher rudimentär. Es gibt keine materialisierten Sichten und über Ausführungspläne grafisch ausgegeben werden. In ihrem Umfang Indexpflege oder das Überprüfen referentieller Integrität macht berücksichtigen Pläne von DB2 LUW zwar die MQT-Pflege und der Plan keinerlei Angaben. Bezüglich der Aufwandsabschät- RI-Prüfungen, die Pflege von Indexen jedoch wird nicht darge- zung weist MySQL CPU-, I/O- und den Gesamtaufwand aus. stellt. Hinsichtlich der Aufwandsabschätzung deckt DB2 LUW als einziges der betrachteten Systeme nicht nur alle Metriken ab, Microsoft SQL-Server [7]: Im SQL-Server werden Pläne erst bei sondern liefert zu diesen auch konkrete Bewertungsmaße. CPU- ihrer Ausgabe externalisiert. Ein Mechanismus zur reinen Erzeu- Aufwand wird in CPU-Instruktionen, I/O-Aufwand in Daten- gung und spezielle Strukturen zur Speicherung sind damit nicht seiten-I/Os und der Gesamtaufwand in Timerons angegeben. Da nötig und folglich nicht vorhanden. Zur Ausgabe von Ausfüh- letztere eine IBM-interne Maßeinheit darstellen, entkräftet sich rungsplänen existieren verschiedene SHOWPLAN SET-Optionen. allerdings diese positive Sonderrolle wieder. 50 DB2 z/OS [16]: In der Erstellung und Speicherung von Ausfüh- DBMS, wie in Abbildung 4 auf Basis einer Matrix dargestellt, mit rungsplänen ähnelt DB2 z/OS dem zuvor betrachteten DB2 LUW. wenigen Ausnahmen die betrachteten Grundoperatoren funktional Es wird ebenfalls die EXPLAIN-Anfrage und eine tabellarische mit mindestens einem spezifischen Operator ab. Die weiteren Speicherung verwendet. Die Struktur der verwendeten Tabellen Ausführungen nehmen detaillierter Bezug auf die Matrix. Dabei ist jedoch grundverschieden. Neben der Variante, DB2-Zugriffs- werden aus Umfangsgründen nur ausgewählte Besonderheiten pläne direkt aus der tabellarischen Struktur auszulesen, ist behandelt, die einer zusätzlichen Erklärung bedürfen. lediglich die grafische Planausgabe über separate Werkzeuge wie Oracle [5, 6]: Ausführungsplanoperatoren in Oracle charakteri- Data Studio oder den Infosphere Optim Query Workload Tuner sieren sich durch eine Operation und zugehörige Optionen. Wäh- möglich. Der Umfang des Ausführungsplans in DB2 z/OS ist sehr rend erstere eine allgemeine Beschreibung zum Operator liefert, beschränkt. Es werden keine der betrachteten Prozesse dargestellt. beschreiben zweite den konkreten Zweck eines Operators näher. Die Aufwandsschätzung dagegen umfasst Angaben zu CPU-, Beispielsweise kann für eine SORT-Operation anhand ihrer I/O- und Gesamtaufwand. Erstere wird sowohl in CPU-Millise- Option unterschieden werden, ob es sich um eine reine Sortierung kunden als auch in sogenannten Service Units ausgewiesen. (ORDER BY) oder einen Aggregationsoperator (AGGREGATE) handelt. Allgemein fällt zu den Planoperatoren auf, dass in Oracle 4. PLANOPERATOREN IN RDBMS viele OLAP-Operatoren wie etwa CUBESCAN oder PIVOT aus- Ähnlich zu den Ausführungsplänen setzt sich die Verschiedenheit gewiesen werden, die sich keinem der Grundoperatoren zuordnen der betrachteten DBMS auch in der Beschreibung ihrer in weiten lassen und damit jeweils als otherIntermediate kategorisiert wur- Teilen gleichartig arbeitenden Planoperatoren fort. Diese werden den. Besonders ist auch die Tatsache, dass zwar ein REMOTE- in den folgenden Ausführungen gegenübergestellt. Abschnitt 4.1. Zugriffsoperator, jedoch kein entsprechender Manipulationsopera- vergleicht sie anhand der bereits beschriebenen Kriterien. Im tor existiert. Ein solcher wird jedoch nicht benötigt, da Oracle än- anschließenden Abschnitt 4.2. wird eine Kategorisierung der dernde Ausführungspläne stets von der DBMS-Instanz ausgehend einzelnen Operatoren nach ihrer Funktionalität beschrieben. erstellt, auf der die Manipulation stattfindet, und gleichzeitige Ma- 4.1 Operatordetails nipulationen auf mehreren Servern nicht möglich sind. Auf Basis der in Abschnitt 2.2 beschriebenen Vergleichskriterien MySQL [7]: MySQL unterstützt lediglich "Left-deep-linear" Plä- ergibt sich für die Operatordetails der miteinander verglichenen ne, in denen jedes zugegriffene Objekt direkt mit dem gesamten DBMS die in Abbildung 3 gezeigte Matrix. Auf eine Darstellung bisherigen Vorergebnis verknüpft wird. Dies wirkt sich auch auf der DBMS-spezifischen Bezeichnungen ist darin aus Kompakt- die tabellarische Planstruktur aus, die mit wenigen Ausnahmen heitsgründen verzichtet worden. Die in der Matrix ersichtlichen wie etwa UNION RESULT je involvierter Tabelle genau eine Auffälligkeiten sollen nun näher ausgeführt werden. Zeile enthält. Operatoren werden darin nicht immer explizit ver- Operatorendetails zeichnet, was ihre Kategorisierung sehr erschwert. Die Art des Datenzugriffs ergibt sich im Wesentlichen aus der „type“-Spalte, Projektion welche in der MySQL-Dokumentation mit „join type“ näher be- kumulativ DBMS Aufwand Aufwand Aufwand StartUp absolut Aliase schrieben wird. Als eigentlicher Verbund-Operator existiert nur Bytes Rows der „nested loop“-Join. Welche Art der Zwischenverarbeitung Oracle (12c R1) x x - x - x x zum Einsatz kommt, muss den Spalten „Extra“ und „select_type“ MySQL (5.7) x - x - x x x entnommen werden. Manipulationsoperatoren werden in Plänen Microsoft SQL-Server (2014) x x x x - x x erst seit Version 5.6.3 und dort unter „select_type“ ausgewiesen. PostgreSQL (9.3) x x - x x x x Microsoft SQL-Server [8, 9]: Im SQL-Server kennzeichnen sich DB2 LUW (V10.5) x - - x x x x Ausführungsplanoperatoren durch einen logischen und einen phy- DB2 z/OS (V11) x - - x - - x sischen Operator. Für die abgebildete Matrix sind letztere rele- vant, da, wie ihr Name bereits suggeriert, sie diejenigen sind, die Abbildung 3: Vergleich der Operatorendetails Aufschluss über die tatsächliche physische Abarbeitung von An- Eine der wesentlichen Aussagen der Matrix ist, dass ein überwie- fragen geben. Charakteristisch für den SQL-Server sind die ver- gender Teil an zentralen Details existiert, die von nahezu allen schiedenen Spool-Operatoren Table, Index, Row Count und Win- DBMS für Ausführungsplanoperatoren ausgewiesen werden. Dies dow Spool. All diesen ist gemein, dass sie Zwischenergebnisse in sind die Ergebniszeilenanzahl (Rows), der kumulative Aufwand, der sogenannten tempDB materialisieren („aufspulen“) und diese Projektionslisten und Aliase. Andere Details wie die Größe des im Anschluss wiederum nach bestimmten Werten durchsuchen. Zwischenergebnisses (Bytes), der absolute oder der Startup-Auf- Die Spool-Operatoren sind daher sowohl den Manipulations- als wand hingegen stehen nur bei einzelnen DBMS zur Verfügung. auch den Zugriffsoperatoren zugeordnet worden. Einzig der Row Bei horizontaler Betrachtung fällt auf, dass kein DBMS alle De- Count Spool wurde als otherIntermediate-Operator klassifiziert, tailinformationen bereithält. Stattdessen schwankt der Detailum- da sein Spulen ausschließlich für Existenzprüfungen genutzt wird fang vom lediglich nicht ausgewiesenen Startup-Aufwand beim und er dabei die Anzahl von Zwischenergebniszeilen zählt. SQL- SQL-Server oder dem einzig fehlenden absoluten Aufwand bei Server besitzt als einziges der betrachteten DBMS dedizierte re- PostgreSQL bis hin zum DB2 z/OS, das nur die zuvor als zentral moteManipulation-Operatoren, die zur Abarbeitung einer Manipu- betitelten Details mitführt. lation bezogen auf ein Objekt in einer fernen Quelle verwendet werden. Zuletzt sollen als Besonderheit die Operatoren Collapse 4.2 Operatoren-Raster und Split erwähnt werden. Diese treten nur im Zusammenhang Planoperatoren sind in den betrachteten DBMS verschieden gra- mit UPDATE-Operatoren auf und wurden daher ebenfalls in nular ausgeprägt. Damit gemeint ist die Anzahl von Operatoren, deren Gruppe eingeordnet. Funktional dienen sie dazu, das soge- die von knapp 60 im SQL-Server bis hin zu etwa halb so vielen nannte Halloween Problem zu lösen, welches beim Update von Operatoren in PostgreSQL reicht. Trotzdem decken die einzelnen aus einem Index gelesenen Daten aufkommen kann [10]. 51 Zugriffsoperatoren Zwischenverarbeitungsoperatoren Manipulationsoperatoren Rück- generatedRow remoteAccess Manipulation Manipulation gabe- Intermediate otherAccess indexAccess tableAccess DBMS Opera- aggregate remote update bitmap toren Access merge delete insert other other filter sort join set AND-EQUAL, CONNECT BY, CUBE FOR UPDATE, JOIN, INLIST ITERATOR, MAT_VIEW HASH CONCATE- PARTITION, PX (SELECT | ACCESS, JOIN, NATION, (COORDINATOR | INSERT | MAT_VIEW MERGE INTERSEC- COUNT, PARTITION | UPDATE | REWRITE BITMAP, JOIN, TION, CONCATE- FILTER, RECEIVE | SEND), UPDATE DELETE DELETE) Oracle ACCESS, TABLE DOMAIN RE- CUBE SCAN, NESTED MINUS, NATION, FIRST PIVOT, UNPIVOT, INSERT STATE- STATE- STATE- (12c R1) ACCESS INDEX, INDEX - MOTE SEQUENCE LOOPS UNION SORT HASH, SORT ROW BITMAP VIEW STATEMENT MENT MENT - - - MENT const, eq_ref, index_merge, Range fulltext, Using index checked index_sub- condition, for each ALL, const, query, range, Using join record, index, Full index, ref, DERI- buffer (Block [DEPEN- Using scan on NULL ref_or_null, VED - Nested Loop DENT | Using index where INSERT, key, system, system, no | Batched UNCACHE- Using for group- [with pu- REPLACE, MySQL Using unique_sub- tables Key Access), nested ABLE] file- by, Using shed con- Using (5.7) temporary query used - Using MRR loop UNION sort filesort dition] - - temporary UPDATE DELETE - - - - Batch Hash, Deleted Scan, Remote Clustered Inserted Scan, Index Index Insert, Clustered Parameter Scan, Assert, Compute Index Insert, Index Table Scan, Clustered Remote Scalar, Merge Index Spool, Collapse, Clustered 52 RID Lookup, Index Scan, Index Hash Interval, Online Index Update, Index Remote Table Scan, Clustered Seek, match, Parallelism, Row Insert, Table Index Delete, Clustered Delete, SELECT, Table Spool, Index Seek, Remote Merge Count Spool, Build, Table Update, Index Index Remote INSERT, Microsoft Table-valued Index Scan, Con- Query, Join, Concate- Hash Match, Switch, Segment, Insert, Table Split, Delete, Merge, Insert, UPDATE, SQL-Server Function, Index Seek, stant Remote Nested nation, Hash Stream Filter, Sequence Spool, Win- Table Table Table Remote Se- DELETE, (2014) Window Spool Index Spool Scan Scan Log Row Scan Loops Match Sort Aggre-gate Top Bitmap Project, UDX dow Spool Update Delete Merge Update quence MERGE Aggregate, Bitmap Func- HashAggre- (Index|Heap) tion Nested gate, Win- Scan, Index Scan on Func- CTE scan, Loop, HashAggre- dowAgg, Scan, Index gene- tion Subquery Hash Join, gate, GroupAg- Function PostgreSQL Materialize, Only Scan, rate_- Scan on Scan, Merge HashSetOp, gregate, Filter, Insert, Scan on (9.3) Seq Scan Materialize series dblink Values Scan Join Append Sort Sort, Unique Limit - - Materialize Update Delete - dblink - - HSJOIN, CMPEXP, EISCAN, MSJOIN, MGSTREAM, DB2 LUW FETCH, IXSCAN, GEN- RPD, RIDSCN, NLJOIN, IXAND, PIPE, REBAL, TQ, INSERT, (V10.5) TBSCAN XISCAN ROW SHIP XSCAN ZZJOIN UNION SORT GRPBY FILTER XANDOR UNIQUE TEMP UPDATE DELETE - - - RETURN BTBSCAN, CORSUB DIXSCAN, ACCESS, FFETCH, EXCEPT, FETCH, FIXSCAN, NLJOIN, EXCEPTA, DFETCH, IXSCAN, STARJOIN, INTERSECT, INLIST, HSSCAN, RGLIST, MSJOIN, INTERSECTA, SORT, IXAND, PARTITION, DB2 z/OS TBSCAN, SIXSCAN, SEMIJOIN, UNION, SORT- XIXAND, REPARTITION, INSERT, DELETE, QUERY, (V11) WFSCAN XIXSCAN - - MIXSCAN HBJOIN UNIONA RID - - XIXOR RID FETCH WKFILE UPDATE TRUNCATE MERGE - - QB Abbildung 4: Kategorisierung der Ausführungsplanoperatoren PostgreSQL [11]: Ausführungspläne in PostgreSQL sind gut unabhängigen Ausführungsplananalyse möglich wäre. Zusätzlich strukturiert und bieten zu jedem Operator die für ihn entscheiden- könnte es auch genutzt werden, um föderierte Pläne zwischen un- den Informationen, sei es der verwendete Schlüssel beim Sortie- terschiedlichen relationalen DBMS zu berechnen, die über bislang ren oder welcher Index auf welcher Tabelle bei einem Indexscan vorhandene abstrakte Remote-Operatoren hinausgehen. zum Tragen kam. Für die Sortierung wird zudem angegeben, welche Sortiermethode benutzt wurde (quicksort bei genügend 6. LITERATUR Hauptspeicherplatz, ansonsten external sort). Bei Unterabfragen [1] International Business Machines Corporation. Solution Brief wird der Plan zusätzlich unterteilt (sub plan, init plan). Soll auf – IBM InfoSphere Optim Query Workload Tuner, 2014. Daten einer anderen (PostgreSQL-) Datenbank zugegriffen wer- ftp://ftp.boulder.ibm.com/common/ssi/ecm/en/ims14099usen/IMS14 den, so kommt das zusätzliche Modul dblink zum Tragen. Dieses 099USEN.PDF leitet die entsprechende Anfrage einfach an die Zieldatenbank [2] Dell Software Inc. Toad World, 2015. weiter, sodass aus dem Anfrageplan nicht ersichtlich wird, ob es https://www.toadworld.com sich um lesenden oder schreibenden Zugriff handelt. Auf einen [3] AquaFold. Aqua Data Studio – SQL Queries & Analysis expliziten Ausgabeoperator wurde in PostgreSQL verzichtet. Tool, 2015. DB2 LUW [14]: Analog den zuvor betrachteten DBMS zeigen http://www.aquafold.com/aquadatastudio/query_analysis_tool.html sich auch für die Planoperatoren von DB2 LUW verschiedene Be- [4] DB-Engines. DB-Engines Ranking von Relational DBMS, sonderheiten. Mit XISCAN, XSCAN und XANDOR existieren 2015 spezielle Operatoren zur Verarbeitung von XML-Dokumenten. http://db-engines.com/de/ranking/relational+dbms Für den Zugriff auf Objekte in fernen Quellen verfügt DB2 LUW [5] Oracle Corporation. Oracle Database 12c Release 1 (12.1) – über die Operatoren RFD (nicht relationale Quelle) und SHIP (re- Database SQL Tuning Guide, 2014. lationale Quelle). Operatoren zur Manipulationen von Daten in https://docs.oracle.com/database/121/TGSQL.pdf fernen Quellen existieren nicht. Diese Prozesse werden im Aus- führungsplan gänzlich vernachlässigt. Die Operatoren UNIQUE [6] Oracle Corporation. Oracle Database 12c Release 1 (12.1) – (einfach) und MGSTREAM (mehrfach) dienen zur Duplikatele- PL/SQL Packages and Types Reference, 2013. minierung. Da sie dabei aber weder Sortier- noch Aggregations- https://docs.oracle.com/database/121/ARPLS.pdf funktionalität leisten, wurden sie als otherIntermediate eingeord- [7] Oracle Corporation. MySQL 5.7 Reference Manual, 2015. net. Dort finden sich auch die Operatoren CMPEXP und PIPE, die http://downloads.mysql.com/docs/refman-5.7-en.a4.pdf lediglich für Debugging-Zecke von Bedeutung sind. [8] Microsoft Corporation. SQL Server 2014 – Transact-SQL DB2 z/OS [15]: Obwohl DB2 z/OS und DB2 LUW gemeinsam Reference (Database Engine), 2015. zur DB2-Familie gehören, unterscheiden sich deren Ausführungs- https://msdn.microsoft.com/de-de/library/bb510741.aspx planoperatoren nicht unerheblich voneinander. Lediglich etwa ein [9] Microsoft Corporation. SQL Server 2014 – Showplan Logical Drittel der DB2 LUW Operatoren findet sich namentlich und and Physical Operators Reference, 2015. funktional annähernd identisch auch in DB2 z/OS wieder. Beson- https://technet.microsoft.com/de-de/library/ms191158.aspx ders für DB2 z/OS Planoperatoren sind vor allem die insgesamt 5 [10] PostgreSQL Global Development Group. PostgreSQL 9.3.6 verschiedenen Join-Operatoren, die vielfältigen Indexzugriffs- Documentation – EXPLAIN, 2015. operatoren und das Fehlen von Remote-, Aggregations- und Fil- http://www.postgresql.org/docs/9.3/static/sql-explain.html teroperatoren. Remote-Operatoren sind nicht notwendig, da DB2 z/OS ferne Anfragen nur dann unterstützt, wenn diese ausschließ- [11] EnterpriseDB. Explaining EXPLAIN, 2008. lich Objekte einer DBMS-Instanz (Subsystem) referenzieren. https://wiki.postgresql.org/images/4/45/Explaining_EXPLAIN.pdf Föderierte Anfragen sind nicht möglich. Aggregationen werden in [12] F. Amorim. Complete Showplan Operators, Simple Talk DB2 z/OS entweder unmittelbar beim Zugriff oder über Materiali- Publishing, 2014. sierung der Zwischenergebnisse in Form von temporären Work- https://www.simple-talk.com/simplepod/Complete_Showplan_ files (WKFILE) und anschließenden aggregierenden Workfile- Operators_Fabiano_Amorim (without video).pdf Scans (WFSCAN) realisiert. Ein dedizierter Aggregationsoperator [13] International Business Machines Corporation. DB2 10.5 for ist damit ebenfalls nicht nötig. Filteroperatoren existieren nicht, Linux, UNIX and Windows – Troubleshooting and Tuning weil sämtliche Filterungen direkt in die vorausgehenden Zugriffs- Database Performance, 2015. operatoren eingebettet sind. http://public.dhe.ibm.com/ps/products/db2/info/vr105/pdf/en_US/ DB2PerfTuneTroubleshoot-db2d3e1051.pdf 5. ZUSAMMENFASSUNG UND AUSBLICK [14] International Business Machines Corporation. Knowledge Der Beitrag verglich die Ausführungspläne und –planoperatoren Center – DB2 10.5 for Linux, UNIX and Windows – Explain der populärsten relationalen DBMS. Es konnte gezeigt werden, operators, 2014. dass der grundlegende Aufbau von Ausführungsplänen sowie den http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/ dazu verwendeten Operatoren zu weiten Teilen systemübergrei- com.ibm.db2.luw.admin.explain.doc/doc/r0052023.html fend sehr ähnlich sind. Auf abstrakterer Ebene ist es sogar mög- lich, Grundoperatoren zu definieren, die von jedem DBMS in Ab- [15] International Business Machines Corporation. Knowledge hängigkeit seines Funktionsumfangs gleichermaßen unterstützt Center – Nodes for DB2 for z/OS, 2014. https://www-304.ibm.com/support/knowledgecenter/SS7LB8_4.1.0/ werden. Der vorliegende Beitrag schlägt diesbezüglich eine Men- com.ibm.datatools.visualexplain.data.doc/topics/znodes.html ge von Grundoperatoren und eine passende Kategorisierung der existierenden DBMS-spezifischen Operatoren vor. Darauf basie- [16] International Business Machines Corporation. DB2 11 for rend ließe sich zukünftig ein Standardformat für Pläne definieren, z/OS – SQL Reference, 2014. mit dem die Entwicklung von Werkzeugen zur abstrakten DBMS- http://publib.boulder.ibm.com/epubs/pdf/dsnsqn05.pdf 53