Datenbank-Ausführungspläne lesen und verstehen

© Gunnar Pippel, 123RF

Alles nach Plan

Will man das Optimierungspotenzial von SQL-Abfragen oder ihren zukünftigen Ressourcenbedarf abschätzen, braucht man einen Blick unter die Haube. Den erlauben die Ausführungspläne der Datenbank. Wer sie versteht, hält den Schlüssel für effektives SQL in der Hand.
RAID-Technologie verspricht höhere Performance und mehr Sicherheit beim permanenten Speichern von Daten. Die ADMIN-Redaktion gibt einen Überblick über ... (mehr)

Der sogenannte Ausführungsplan (execution plan, oft auch explain plan oder query execution plan) enthält die einzelnen Schritte, die eine Datenbank beim Abarbeiten einer SQL-Anweisung ausführt. Ausführungspläne geben zum Beispiel Aufschluss darüber, welche Indizes benutzt werden, in welcher Reihenfolge die Zugriffe auf die verschiedenen Tabellen erfolgen, und welche Algorithmen für Join-, Sortier- und Gruppier-Operationen zum Zuge kommen.

Im Vergleich mit anderen Programmiersprachen entspricht der Ausführungsplan ungefähr dem Bytecode von Scriptsprachen wie Perl oder Python – er wird intern bei der Ausführung der SQL-Anweisung verwendet. Aufgrund dieser Analogie wird das Erstellen eines Ausführungsplanes manchmal auch als Kompilieren bezeichnet. Gebräuchlicher ist aber die Bezeichnung Prepare für diese Phase (siehe Abbildung 1). Da die entsprechende Datenbank-Komponente Optimizer oder Query Planner heißt, sind auch die Begriffe Optimieren und Planen gebräuchlich.

Abbildung 1: Phasen der SQL-Ausführung.

Ausführungspläne sind in erster Linie ein internes Mittel zum Zweck. Der Admin kann sie aber dennoch einsehen. Weil der Ausführungsplan die Abläufe auf einer ähnlichen Abstraktionsebene darstellt wie SQL, kann man einen Ausführungsplan sehr schnell lesen – durch die einheitliche Formatierung oft sogar schneller als die ursprüngliche SQL-Anweisung.

Kein übergreifender Standard

Einheitlich ist die Formatierung von Ausführungsplänen aber nur innerhalb einer Datenbank. Einen herstellerübergreifenden Standard gibt es dafür nicht. Tatsächlich sehen die Ausführungspläne einer MySQL-Datenbank völlig anders aus als zum Beispiel bei Microsofts SQL Server. Genauso unterschiedlich sind die Methoden zur Anzeige eines Ausführungsplanes. Während es bei PostgreSQL und MySQL genügt, der SQL-Anweisung das Schlüsselwort »explain« voranzustellen, muss man bei Oracle das Kommando »xplain plan for« mit dem Funktionsaufruf »DBMS_XPLAN.DISPLAY« kombinieren (siehe Beispiel 1).

Beispiel 1: Oracle-Ausführungsplan erklärt

 

Das Kommando »explain plan for« speichert den Ausführungsplan lediglich in der »PLAN_TABLE« ab.

Das Package DBMS_XPLAN bietet einige Funktionen zum Darstellen eines Ausführungsplanes.

Die Bytes-Spalte zeigt die verarbeitete Datenmenge bei jeder Operation an. Dabei sind zwei Dinge zu beachten: Erstens handelt es sich dabei lediglich um eine Schätzung des Optimizers. Zweitens: Nur wenige Operationen müssen diese Daten zwischenspeichern. In diesem Beispiel muss der Hash-Join nur die kleinere Tabelle (566K) temporär im Arbeitsspeicher ablegen.

Die Cost-Werte werden für jeden Teil-Baum des Ausführungsplanes angezeigt. Die Gesamtkosten sind auf der obersten Ebene zu finden.

Das Filterprädikat zur Operation mit der Id 2 (»TABLE ACCESS BY INDEX ROWID« ) ist ein Hinweis, dass die Spalte »EUR_VALUE« nicht im Index »SALE_DATE« ist (Operation Id 3).

Das Zugriffsprädikat »access« beim Indexzugriff zeigt, dass der Index nur für die Bedingung auf SALE_DATE effizient genutzt wird.

Grafische Benutzeroberflächen bieten dafür entsprechende Schaltflächen oder Menüpunkte. Dieser Artikel legt den Schwerpunkt auf MySQL, Hinweise zu anderen Produkten finden sich hier [1]. Der Rest dieses Beitrags wird zeigen, wie der Admin die wichtigsten Informationen aus Ausführungsplänen herausliest und häufige Fehlinterpretationen vermeidet.

Der Cost-Wert

Der Cost-Wert ist ein Benchmark, der vom Optimizer benutzt wird, um den besten Ausführungsplan für eine SQL-Anweisung zu finden. Grob gesprochen könnte man sagen, dass der Cost-Wert ein Maßstab für die Ausführungsgeschwindigkeit ist. Genau betrachtet gilt das aber nur für jeweils eine SQL-Anweisung unter bestimmten Rahmenbedingungen, zu denen etwa die Tabellengröße gehört. Das heißt, der Cost-Wert ist grundsätzlich nicht geeignet, um die Performance verschiedener SQL-Anweisungen miteinander zu vergleichen.

Natürlich kann der Cost-Wert dennoch ein grobes Gefühl für die Ausführungsgeschwindigkeit liefern. So kann ein Wert in den Milliarden durchaus den Rückschluss zulassen, dass die Ausführung ewig dauern wird. Der Cost-Wert ist dafür aber lediglich der Indikator, nicht die Ursache, denn auf die Execute-Phase wirkt er sich nicht aus.

Etwas Ähnliches gilt mit Blick auf die Optimizer-Statistiken. Weil diese Statistiken die Grundlage für die Berechnung des Cost-Wertes sind, verändert ein Update auch den Cost-Wert. Dennoch wird sich die Geschwindigkeit der SQL-Abfrage nicht ändern, wenn der Ausführungsplan ansonsten unverändert bleibt – das heißt, wenn dieselben Operationen nach wie vor in derselben Reihenfolge abgearbeitet werden.

Ähnliche Artikel

comments powered by Disqus

Artikel der Woche

Eigene Registry für Docker-Images

Wer selber Docker-Images herstellt, braucht auch eine eigene Registry. Diese gibt es ebenfalls als Docker-Image, aber nur mit eingeschränkter Funktionalität. Mit einem Auth-Server wird daraus ein brauchbares Repository für Images. (mehr)
Einmal pro Woche aktuelle News, kostenlose Artikel und nützliche ADMIN-Tipps.
Ich habe die Datenschutzerklärung gelesen und bin einverstanden.

Konfigurationsmanagement

Ich konfiguriere meine Server

  • von Hand
  • mit eigenen Skripts
  • mit Puppet
  • mit Ansible
  • mit Saltstack
  • mit Chef
  • mit CFengine
  • mit dem Nix-System
  • mit Containern
  • mit anderer Konfigurationsmanagement-Software

Google+

Ausgabe /2019