Datenbanken IIDatenbanken II

SQL - AusführungspläneSQL - Ausführungspläne

Matthias Jauernig (03INB), Michael Lahl Matthias Jauernig (03INB), Michael Lahl (03IND)(03IND)

InhaltInhalt

• Begriff der AusführungspläneBegriff der Ausführungspläne

• Der DBMS-OptimizerDer DBMS-Optimizer

• Ausführungspläne in Oracle:Ausführungspläne in Oracle:– SQL+: EXPLAIN PLAN, AUTOTRACESQL+: EXPLAIN PLAN, AUTOTRACE– SQL AnalyzeSQL Analyze

• BeispielBeispiel

Was sind Ausführungspläne? Was sind Ausführungspläne? (1)(1)

Ausführungsplan (engl.: Execution Plan):Ausführungsplan (engl.: Execution Plan):• Ist die Umwandlung einer SQL Query in Ist die Umwandlung einer SQL Query in

eine für das DBMS ausführbare Form,eine für das DBMS ausführbare Form,• Beschreibt die Schritte zur Ausführung Beschreibt die Schritte zur Ausführung

einer SQL Query,einer SQL Query,• gibt DBMS eine gibt DBMS eine AbarbeitungsfolgeAbarbeitungsfolge für für

das vom Benutzer eingegebene SQL das vom Benutzer eingegebene SQL Statement vorStatement vor

Was sind Was sind AusführungspläneAusführungspläne? ? (2)(2)• Es kann viele Ausführungspläne geben, die dasselbe Es kann viele Ausführungspläne geben, die dasselbe

SQL Statement erfüllen, z.B. durch Variieren der SQL Statement erfüllen, z.B. durch Variieren der Reihenfolge, in der algebraische Operationen Reihenfolge, in der algebraische Operationen ausgeführt werden,ausgeführt werden,

• Laufzeit und Antwortzeit eines SQL Statements Laufzeit und Antwortzeit eines SQL Statements wesentlich vom Ausführungsplan bestimmt,wesentlich vom Ausführungsplan bestimmt,

Ziel: möglichst kostengünstiger Ausführungsplan,Ziel: möglichst kostengünstiger Ausführungsplan,

• Optimierung von SQL–Statements durch DBMS Optimierung von SQL–Statements durch DBMS Optimizer oder manuell durch Anwender möglich,Optimizer oder manuell durch Anwender möglich,

• Interpretation von Ausführungsplänen versetzt Interpretation von Ausführungsplänen versetzt Anwender in die Lage, Performanceverbesserungen Anwender in die Lage, Performanceverbesserungen für Anfragen zu erreichen.für Anfragen zu erreichen.

Interne UmwandlungInterne Umwandlung

• „„Query Rewrite“Query Rewrite“• SQL–Statement intern in prozedurale Form SQL–Statement intern in prozedurale Form

umwandeln, d.h. für SQL–Befehle werden die umwandeln, d.h. für SQL–Befehle werden die Operatoren der relationalen Algebra Operatoren der relationalen Algebra eingesetzteingesetzt

• Ausführungsplan = Operatorbaum – Knoten Ausführungsplan = Operatorbaum – Knoten stellen Operatoren zur Verknüpfung von stellen Operatoren zur Verknüpfung von Zwischenergebnissen dar Zwischenergebnissen dar

• Zur Abarbeitung des Baums müssen konkrete Zur Abarbeitung des Baums müssen konkrete Implementierungstechniken angewendet Implementierungstechniken angewendet werden (z.B. Nested Loop Join, Index Scan, werden (z.B. Nested Loop Join, Index Scan, …)…)

Der DBMS-Optimizer (1)Der DBMS-Optimizer (1)

• Generiert eine Menge von möglichen Generiert eine Menge von möglichen Ausführungsplänen, wählt den Ausführungsplänen, wählt den kostengünstigsten aus,kostengünstigsten aus,

• Berechnet für jeden Plan einen Berechnet für jeden Plan einen „Kostenvoranschlag“„Kostenvoranschlag“

• Unterscheidung zwischen:Unterscheidung zwischen:– RBO (rule-based optimizer)RBO (rule-based optimizer)– CBO (cost-based optimizer)CBO (cost-based optimizer)

RBO – rule-based optimizerRBO – rule-based optimizer

• Ältere Form des OptimierersÄltere Form des Optimierers

• Ausführungsplan wird anhand eines Ausführungsplan wird anhand eines festen Regelwerks, Prioritäten festen Regelwerks, Prioritäten zwischen Operationen und zwischen Operationen und Informationen des Data Dictionary Informationen des Data Dictionary (Indexe, …) erstellt(Indexe, …) erstellt

• Nachteile: Keine Auswertung von Nachteile: Keine Auswertung von Statistiken, begrenzter RegelsatzStatistiken, begrenzter Regelsatz

CBO – cost-based optimizerCBO – cost-based optimizer

• Verwendet Statistiken zur Schätzung Verwendet Statistiken zur Schätzung der Kosten (I/O, CPU) eines der Kosten (I/O, CPU) eines Ausführungsplans,Ausführungsplans,

• Statistiken beinhalten z.B. Statistiken beinhalten z.B. Informationen zu Datenverteilung / Informationen zu Datenverteilung / Speicherung von Tabellen und Speicherung von Tabellen und IndexesIndexes

• Vorteil: Genauere Kosten als bei RBOVorteil: Genauere Kosten als bei RBO• Nachteil: Statistiken regelmäßig zu Nachteil: Statistiken regelmäßig zu

erstellen bzw. aktualisierenerstellen bzw. aktualisieren

Der DBMS-Optimizer (2)Der DBMS-Optimizer (2)

1.1. Optimizer spannt zunächst Suchraum Optimizer spannt zunächst Suchraum von möglichen Plänen auf von möglichen Plänen auf Beschränkung sinnvoll, z.B. breiter Beschränkung sinnvoll, z.B. breiter Einsatz von:Einsatz von:

HeuristikenHeuristiken: (=Daumenregeln): (=Daumenregeln)- Selektionen so früh wie möglich,Selektionen so früh wie möglich,

- Basisoperationen als 1 Berechnungsschritt,Basisoperationen als 1 Berechnungsschritt,

- Nur Berechnungen ausführen, die Beitrag zu Nur Berechnungen ausführen, die Beitrag zu Gesamtergebnis liefern.Gesamtergebnis liefern.

Der DBMS-Optimizer (3)Der DBMS-Optimizer (3)

2.2. Optimizer wählt dann Plan mit minimal Optimizer wählt dann Plan mit minimal geschätzten Gesamtkosten aus geschätzten Gesamtkosten aus Suchraum wird durchlaufenSuchraum wird durchlaufen

erschöpfenderschöpfend: NP-Problem, Algorithmen: : NP-Problem, Algorithmen: Greedy, dyn. Programmierung, Greedy, dyn. Programmierung, Branch&BoundBranch&Bound

partiellpartiell: keine opt. Lösung garantiert, : keine opt. Lösung garantiert, Verfahren: simulated annealing, hill Verfahren: simulated annealing, hill climbing, genetische Algorithmenclimbing, genetische Algorithmen

Ausführungspläne in OracleAusführungspläne in Oracle

• mehrere Möglichkeiten zur Erzeugung von mehrere Möglichkeiten zur Erzeugung von Ausführungsplänen stehen zur Verfügung:Ausführungsplänen stehen zur Verfügung: – SQL*Plus SQL*Plus (textbasiert)(textbasiert)

•EXPLAIN PLAN – BefehlEXPLAIN PLAN – Befehl

•AUTOTRACE – FunktionalitätAUTOTRACE – Funktionalität

– Programm Programm SQL–AnalyzeSQL–Analyze (nur in der (nur in der Enterprise Version enthalten) -> Enterprise Version enthalten) -> Vereinigung von beiden textbasierten Vereinigung von beiden textbasierten VariantenVarianten

EXPLAIN PLAN – Befehl (1)EXPLAIN PLAN – Befehl (1)

EXPLAIN PLANEXPLAIN PLAN

SET statement_id = ‘<Bezeichner>‘SET statement_id = ‘<Bezeichner>‘

FORFOR

<SQL – Statement><SQL – Statement>

• Ausführung in der SQL*Plus KonsoleAusführung in der SQL*Plus Konsole

• Ausführungsplan der SQL – Anweisung wird in Ausführungsplan der SQL – Anweisung wird in plan_table gespeichert (muss erst angelegt werden)plan_table gespeichert (muss erst angelegt werden)

• Anweisung wird Anweisung wird nichtnicht ausgeführt! ausgeführt!

• Informationen können nach Ausführung Informationen können nach Ausführung ausgewertet werdenausgewertet werden

• Nachteil:Nachteil: manuelle Extrahierung der Informationen manuelle Extrahierung der Informationen

EXPLAIN PLAN – Befehl (2)EXPLAIN PLAN – Befehl (2)

AUTOTRACE – Funktion (1)AUTOTRACE – Funktion (1)

• Ausführungsplan sowie Statistiken werden in Ausführungsplan sowie Statistiken werden in lesbarer Form an die Resultatmenge der SQL–lesbarer Form an die Resultatmenge der SQL–Anweisung angehangenAnweisung angehangen

• Nutzung durch Einschalten der Funktionalität Nutzung durch Einschalten der Funktionalität mittels des folgenden Befehls: mittels des folgenden Befehls: SET AUTOTRACE SET AUTOTRACE ON;ON;

• Nachteil:Nachteil: SQL–Query wird immer erst ausgeführt, SQL–Query wird immer erst ausgeführt, daher hoher Zeitaufwand bei mehrfacher Nutzung daher hoher Zeitaufwand bei mehrfacher Nutzung der Queryder Query

AUTOTRACE – Funktion (2)AUTOTRACE – Funktion (2)

SQL – Analyze (1)SQL – Analyze (1)

• grafische Darstellung von Ausführungsplänengrafische Darstellung von Ausführungsplänen

• spezielle Wizards und Assistenten stehen zur spezielle Wizards und Assistenten stehen zur Analyse von einzelnen SQL–Anweisungen zur Analyse von einzelnen SQL–Anweisungen zur VerfügungVerfügung

• Auswertungen werden in grafischer und Auswertungen werden in grafischer und relativ übersichtlicher Form dargestellt (z.B. relativ übersichtlicher Form dargestellt (z.B. mittels Diagrammen, Tabellen)mittels Diagrammen, Tabellen)

SQL – Analyze (2)SQL – Analyze (2)

Eigenschaften (1)Eigenschaften (1)

• TopSQL Funktionalität (suche nach SQL-Anweisung, TopSQL Funktionalität (suche nach SQL-Anweisung, welche die meisten Ressourcen benötigen)welche die meisten Ressourcen benötigen)

• Zugriff auf eine SQL-Historie, in der Informationen Zugriff auf eine SQL-Historie, in der Informationen über vergangene Anweisungen gespeichert wurdenüber vergangene Anweisungen gespeichert wurden

• Ausführen von SQL-Anweisungen mit Ausführen von SQL-Anweisungen mit unterschiedlichen Optimierungen, Angabe von unterschiedlichen Optimierungen, Angabe von Ausführungsplänen sowie Statistiken dazuAusführungsplänen sowie Statistiken dazu

• grafische Anzeige von Ausführungen und grafische Anzeige von Ausführungen und Erklärungen zu den einzelnen SchrittenErklärungen zu den einzelnen Schritten

• Überprüfung von SQL-Anweisungen auf Überprüfung von SQL-Anweisungen auf syntaktische Korrektheit, zeigen von möglichen syntaktische Korrektheit, zeigen von möglichen FehlerquellenFehlerquellen

• Präsentiert relevante Objekteigenschaften, um Präsentiert relevante Objekteigenschaften, um Probleme zu erkennen / zu beheben und so die Probleme zu erkennen / zu beheben und so die Performance der Anweisung zu beeinflussenPerformance der Anweisung zu beeinflussen

Eigenschaften (2)Eigenschaften (2)

• Zugang zu Initialisierungsparametern, haben Zugang zu Initialisierungsparametern, haben direkten Einfluss auf die Performancedirekten Einfluss auf die Performance

• Hinzufügen von Optimizer-Hints (s. Vortrag Hinzufügen von Optimizer-Hints (s. Vortrag Gruppe 8) mit Hilfe des Hint WizardsGruppe 8) mit Hilfe des Hint Wizards

• Speicherung von Ausführungsplänen und Speicherung von Ausführungsplänen und Statistiken von SQL-Anweisungen im RepositoryStatistiken von SQL-Anweisungen im Repository

• Vorschläge zu Indexnutzung um die Performance Vorschläge zu Indexnutzung um die Performance zu erhöhenzu erhöhen

• Virtuelle Indexerstellung (Testen von Indexen Virtuelle Indexerstellung (Testen von Indexen ohne diese zu erstellen)ohne diese zu erstellen)

InterpretationInterpretation

• gelesen wird ein Ausführungsplan von gelesen wird ein Ausführungsplan von rechts nach links (im Operatorbaum rechts nach links (im Operatorbaum gesprochen von den Blättern zur Wurzel)gesprochen von den Blättern zur Wurzel)

• d.h. es werden einfache Operationen zuerst d.h. es werden einfache Operationen zuerst ausgeführt und die resultierenden Mengen ausgeführt und die resultierenden Mengen mit mächtigeren Operationen verknüpft mit mächtigeren Operationen verknüpft daraus entsteht dann die Resultatmenge daraus entsteht dann die Resultatmenge der SQL Queryder SQL Query

Bericht von SQL - AnalyzeBericht von SQL - Analyze

Optimieren (1)Optimieren (1)

• Optimierung manuell setzen mittels Optimierung manuell setzen mittels alter alter session set optimizer_goalsession set optimizer_goal::– optimizer_goal=ruleoptimizer_goal=rule: Einsatz von RBO (nicht : Einsatz von RBO (nicht

mehr in 10g – hier nur noch CBO, Erstellung mehr in 10g – hier nur noch CBO, Erstellung von Statistiken on-the-fly)von Statistiken on-the-fly)

– optimizer_goal=chooseoptimizer_goal=choose: autom. Wahl von : autom. Wahl von RBO bzw. CBO (wenn Statistiken verfügbar)RBO bzw. CBO (wenn Statistiken verfügbar)

– optimizer_goal=all_rowsoptimizer_goal=all_rows: Optimierung bzgl. : Optimierung bzgl. Bereitstellung des GesamtergebnissesBereitstellung des Gesamtergebnisses

– optimizer_goal=first_rowsoptimizer_goal=first_rows: Optimierung : Optimierung bzgl. Bereitstellung des ersten Tupels (z.B. für bzgl. Bereitstellung des ersten Tupels (z.B. für Statistiken – OLAP/Data Warehouses/…)Statistiken – OLAP/Data Warehouses/…)

Beispiel (1)Beispiel (1)

Beispiel (2)Beispiel (2)

Beispiel (3)Beispiel (3)

• Durch Setzen des Indexes auf die Spalte Durch Setzen des Indexes auf die Spalte F_ABT_ID lässt sich die Kosten bei F_ABT_ID lässt sich die Kosten bei Ausführung der SQL–Query um 44% Ausführung der SQL–Query um 44% verringernverringern

• bei großen kostenaufwendigeren Querys bei großen kostenaufwendigeren Querys ist dies eine enorme ist dies eine enorme PerformancesteigerungPerformancesteigerung

ENDEENDE

Noch Fragen???Noch Fragen???