Was sind Ausführungspläne? - linux
Download
Report
Transcript 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???