Was sind Ausführungspläne? - linux

Datenbanken II
SQL - Ausführungspläne
Matthias Jauernig (03INB), Michael Lahl (03IND)
Inhalt
• Begriff der Ausführungspläne
• Der DBMS-Optimizer
• Ausführungspläne in Oracle:
– SQL+: EXPLAIN PLAN, AUTOTRACE
– SQL Analyze
• Beispiel
Was sind Ausführungspläne? (1)
Ausführungsplan (engl.: Execution Plan):
• Ist die Umwandlung einer SQL Query in
eine für das DBMS ausführbare Form,
• Beschreibt die Schritte zur Ausführung
einer SQL Query,
• gibt DBMS eine Abarbeitungsfolge für
das vom Benutzer eingegebene SQL
Statement vor
Was sind Ausführungspläne? (2)
• Es kann viele Ausführungspläne geben, die dasselbe SQL
Statement erfüllen, z.B. durch Variieren der Reihenfolge,
in der algebraische Operationen ausgeführt werden,
• Laufzeit und Antwortzeit eines SQL Statements
wesentlich vom Ausführungsplan bestimmt,
 Ziel: möglichst kostengünstiger Ausführungsplan,
• Optimierung von SQL–Statements durch DBMS Optimizer
oder manuell durch Anwender möglich,
• Interpretation von Ausführungsplänen versetzt
Anwender in die Lage, Performanceverbesserungen für
Anfragen zu erreichen.
Interne Umwandlung
• „Query Rewrite“
• SQL–Statement intern in prozedurale Form
•
•
umwandeln, d.h. für SQL–Befehle werden die
Operatoren der relationalen Algebra eingesetzt
Ausführungsplan = Operatorbaum – Knoten
stellen Operatoren zur Verknüpfung von
Zwischenergebnissen dar
Zur Abarbeitung des Baums müssen konkrete
Implementierungstechniken angewendet werden
(z.B. Nested Loop Join, Index Scan, …)
Der DBMS-Optimizer (1)
• Generiert eine Menge von möglichen
Ausführungsplänen, wählt den
kostengünstigsten aus,
• Berechnet für jeden Plan einen
„Kostenvoranschlag“
• Unterscheidung zwischen:
– RBO (rule-based optimizer)
– CBO (cost-based optimizer)
RBO – rule-based optimizer
• Ältere Form des Optimierers
• Ausführungsplan wird anhand eines festen
Regelwerks, Prioritäten zwischen
Operationen und Informationen des Data
Dictionary (Indexe, …) erstellt
• Nachteile: Keine Auswertung von
Statistiken, begrenzter Regelsatz
CBO – cost-based optimizer
• Verwendet Statistiken zur Schätzung der
Kosten (I/O, CPU) eines
Ausführungsplans,
• Statistiken beinhalten z.B. Informationen
zu Datenverteilung / Speicherung von
Tabellen und Indexes
• Vorteil: Genauere Kosten als bei RBO
• Nachteil: Statistiken regelmäßig zu
erstellen bzw. aktualisieren
Der DBMS-Optimizer (2)
1. Optimizer spannt zunächst Suchraum von
möglichen Plänen auf  Beschränkung
sinnvoll, z.B. breiter Einsatz von:
Heuristiken: (=Daumenregeln)
- Selektionen so früh wie möglich,
- Basisoperationen als 1 Berechnungsschritt,
- Nur Berechnungen ausführen, die Beitrag zu
Gesamtergebnis liefern.
Der DBMS-Optimizer (3)
2. Optimizer wählt dann Plan mit minimal
geschätzten Gesamtkosten aus 
Suchraum wird durchlaufen
 erschöpfend: NP-Problem, Algorithmen: Greedy,
dyn. Programmierung, Branch&Bound
 partiell: keine opt. Lösung garantiert, Verfahren:
simulated annealing, hill climbing, genetische
Algorithmen
Ausführungspläne in Oracle
• mehrere Möglichkeiten zur Erzeugung von
Ausführungsplänen stehen zur Verfügung:
– SQL*Plus (textbasiert)
• EXPLAIN PLAN – Befehl
• AUTOTRACE – Funktionalität
– Programm SQL–Analyze (nur in der
Enterprise Version enthalten) -> Vereinigung
von beiden textbasierten Varianten
EXPLAIN PLAN – Befehl (1)
• Ausführung in der SQL*Plus Konsole
• Ausführungsplan der SQL – Anweisung wird in
•
•
•
plan_table gespeichert (muss erst angelegt werden)
Anweisung wird nicht ausgeführt!
Informationen können nach Ausführung ausgewertet
werden
Nachteil: manuelle Extrahierung der Informationen
EXPLAIN PLAN
SET statement_id = ‘<Bezeichner>‘
FOR
<SQL – Statement>
EXPLAIN PLAN – Befehl (2)
AUTOTRACE – Funktion (1)
• Ausführungsplan sowie Statistiken werden in lesbarer
•
•
Form an die Resultatmenge der SQL–Anweisung
angehangen
Nutzung durch Einschalten der Funktionalität mittels des
folgenden Befehls: SET AUTOTRACE ON;
Nachteil: SQL–Query wird immer erst ausgeführt, daher
hoher Zeitaufwand bei mehrfacher Nutzung der Query
AUTOTRACE – Funktion (2)
SQL – Analyze (1)
• grafische Darstellung von Ausführungsplänen
• spezielle Wizards und Assistenten stehen zur
•
Analyse von einzelnen SQL–Anweisungen zur
Verfügung
Auswertungen werden in grafischer und relativ
übersichtlicher Form dargestellt (z.B. mittels
Diagrammen, Tabellen)
SQL – Analyze (2)
Eigenschaften (1)
• TopSQL Funktionalität (suche nach SQL-Anweisung,
•
•
•
•
•
welche die meisten Ressourcen benötigen)
Zugriff auf eine SQL-Historie, in der Informationen über
vergangene Anweisungen gespeichert wurden
Ausführen von SQL-Anweisungen mit unterschiedlichen
Optimierungen, Angabe von Ausführungsplänen sowie
Statistiken dazu
grafische Anzeige von Ausführungen und Erklärungen zu
den einzelnen Schritten
Überprüfung von SQL-Anweisungen auf syntaktische
Korrektheit, zeigen von möglichen Fehlerquellen
Präsentiert relevante Objekteigenschaften, um Probleme
zu erkennen / zu beheben und so die Performance der
Anweisung zu beeinflussen
Eigenschaften (2)
• Zugang zu Initialisierungsparametern, haben direkten
•
•
•
•
Einfluss auf die Performance
Hinzufügen von Optimizer-Hints (s. Vortrag Gruppe 8)
mit Hilfe des Hint Wizards
Speicherung von Ausführungsplänen und Statistiken von
SQL-Anweisungen im Repository
Vorschläge zu Indexnutzung um die Performance zu
erhöhen
Virtuelle Indexerstellung (Testen von Indexen ohne diese
zu erstellen)
Interpretation
• gelesen wird ein Ausführungsplan von rechts
•
nach links (im Operatorbaum gesprochen von
den Blättern zur Wurzel)
d.h. es werden einfache Operationen zuerst
ausgeführt und die resultierenden Mengen mit
mächtigeren Operationen verknüpft  daraus
entsteht dann die Resultatmenge der SQL Query
 Bericht von SQL - Analyze
Optimieren (1)
• Optimierung manuell setzen mittels alter session
set optimizer_goal:
– optimizer_goal=rule: Einsatz von RBO (nicht
mehr in 10g – hier nur noch CBO, Erstellung von
Statistiken on-the-fly)
– optimizer_goal=choose: autom. Wahl von RBO
bzw. CBO (wenn Statistiken verfügbar)
– optimizer_goal=all_rows: Optimierung bzgl.
Bereitstellung des Gesamtergebnisses
– optimizer_goal=first_rows: Optimierung bzgl.
Bereitstellung des ersten Tupels (z.B. für Statistiken –
OLAP/Data Warehouses/…)
Beispiel (1)
Beispiel (2)
Beispiel (3)
• Durch Setzen des Indexes auf die Spalte
•
F_ABT_ID lässt sich die Kosten bei Ausführung
der SQL–Query um 44% verringern
bei großen kostenaufwendigeren Querys ist dies
eine enorme Performancesteigerung
ENDE
Noch Fragen???