Physische Optimierung - fbi.h

KAPITEL 5
ANFRAGEOPTIMIERUNG
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
1
Einordnung in die 5-Schichten-Architektur
Mengenorientierte Schnittstelle
Datensystem
Satzorientierte Schnittstelle
Zugriffssystem
Interne Satzschnittstelle
Speichersystem
Systempufferschnittstelle
Pufferverwaltung
Dateischnittstelle
Betriebssystem
Geräteschnittstelle
Externspeicher
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
2
Anfrageoptimierung
Inhalte des Kapitels
• Prinzipieller Ablauf der DB-Anfrageauswertung
• Standardisierung und Vereinfachung von DB-Anfragen
• Logische Optimierung
• Physische Optimierung und kostenbasierte Auswahl
• Beeinflussung der Ausführung von DB-Anfragen durch den DBA in
relationalen DBMS
Lernziele
• Kenntnis des prinzipiellen Ablaufs der DB-Anfrageauswertung
• Verstehen der Optimierungsziele und der Unterschiede zwischen
logischer und physischer Optimierung
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
3
Grundprinzipien
• Basissprachen
– SQL
– Relationenkalküle
– hier: Relationenalgebra
• Ziel der Optimierung
– möglichst schnelle Anfragebearbeitung
 möglichst wenig Seitenzugriffe bei der Anfragebearbeitung
 möglichst in allen Operationen so wenig wie möglich Seiten (Tupel)
berücksichtigen
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
4
Auswertung von DB-Anfragen – 1(4)
1. Übersetzung und Sichtauflösung
– Erstellung eines Anfragegraphens
– Überprüfung auf korrekte Syntax
– Auflösen von Sichtdefinitionen und externen Namen
– Feststellen der Existenz und Gültigkeit der referenzierten Tabellen
und Attribute
2. Standardisierung und Vereinfachung
– Überführung des Anfragegraphen in eine Normalform
– Elimination von Redundanzen
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
5
Auswertung von DB-Anfragen – 2(4)
3. Optimierung
• Logische Optimierung (auch algebraische Optimierung bzw.
regelbasierte Optimierung)
– Anfrageplan unabhängig von der konkreten Speicherungsform umformen
(z.B. Hineinziehen von Selektionen in andere Operationen)
• Physische Optimierung (auch interne Optimierung)
– konkrete Speicherungstechniken (Indexe, Cluster) berücksichtigen
– Algorithmen auswählen
– mehrere alternative interne Pläne erstellen
• Kostenbasierte Auswahl
– Statistikinformationen (Größe von Tabellen, Selektivität von Attributen) für
die Auswahl eines konkreten internen Planes nutzen
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
6
Auswertung von DB-Anfragen – 3(4)
4. Planparametrisierung
– bei vorkompilierten Anfragen (etwa Embedded-SQL,
PreparedStatement in JDBC etc.): Ersetzen der Platzhalter durch
Werte
5. Code-Erzeugung
– Umwandlung des Zugriffsplans in ausführbaren Code
• interne, binäre Repräsentation der Anfrage, die interpretiert wird (vgl.
Java Byte Code)
• JIT (just-in-time) Compiler
6. Ausführung
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
7
Auswertung von DB-Anfragen – 4(4)
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
8
1. Übersetzung und Sichtauflösung
• Zunächst Parsen der SQL-Anfrage (syntaktische Korrektheit)
 Parse-Baum
• danach Übersetzung in Relationenalgebra:
– Transformationsregeln
• Relationen der Tabellenliste hinter from untereinander durch
Kreuzprodukt verknüpfen
• Bedingung im where-Teil als Selektion übernehmen
• Spaltenliste hinter select als abschließende Projektion
– zusätzlich noch
• Berücksichtigung von SQL-Konstrukten wie order by, group by
• Auflösen von Unteranfragen
 Operatorbaum
– Operatoren als Knoten
– Kanten repräsentieren Datenfluss
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
9
Parse-Baum
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
10
Übersetzung in Relationenalgebra (Operatorbaum)
select KNr, Nachname
from BESTELLUNG, KUNDE
where BESTELLUNG.KNr = KUNDE.KNr AND Datum > ’22.11.04’
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
11
Auflösung von Sichten
• Sichtexpansion: Einsetzen der Sichtdefinition in Anfrage im
Operatorbaum
– rekursiver Prozess: Sichten über Sichten möglich
• Beispiel:
select BestNr, KNr
from BESTELL_INFO
where Datum > '18.02.04' and Bezeichnung = ' Arabica Black'
– wobei BESTELL_INFO eine Sicht über die Tabellen Produkt,
Bestellung und Kunde ist, welche BestNr, KNr, Datum und
Bezeichnung für jede Bestellung liefert
– Operatorbaum vor Sichtauflösung?
– Operatorbaum nach Sichtauflösung?
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
12
auch so kann eine
SQL-Anfrage aussehen …
•
Generierte SQL-Anfrage - durch Tool zur
Entscheidungsunterstützung (Online
Analytical Processing, OLAP) und GUINutzung erzeugt
Quelle: Härder/Rahm:1999
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
13
2. Standardisierung und Vereinfachung
Vereinfachung der nachfolgenden Optimierungsschritte durch ein
einheitliches (kanonisches) Anfrageformat:
• auf Ausdrucksebene (Bedingungen):
– Normalformen, Entfernen redundanter Ausdrücke
• auf Anfrageebene:
– Entschachtelung (Unteranfragen im where-Teil) zur Vereinfachung:
Erkennen von gemeinsamen Teilen u.ä.
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
14
Standardisierung von Ausdrücken
• speziell für Selektions- und Verbundbedingungen
– konjunktive Normalform vs. disjunktive Normalform
– konjunktive Normalform (KNF) für einfache Prädikate pij :
(p11 V p12 V · · · V p1n) Λ · · · Λ (pm1 V pm2 V · · · V pmn)
– disjunktive Normalform (DNF):
(p11 Λ p12 Λ · · · Λ p1n) V · · · V (pm1 Λ pm2 Λ · · · Λ pmn)
– Überführung in KNF/DNF durch Anwendung von
Äquivalenzbeziehungen für logische Operationen
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
15
Normalisierung
• Äquivalenzbeziehungen
 p1 Λ p2  p2 Λ p1 und
p1 V p2  p2 V p1
 p1 Λ (p2 Λ p3)  (p1 Λ p2) Λ p3 und
p1 V (p2 V p3)  (p1 V p2) V p3
 p1 Λ (p2 V p3)  (p1 Λ p2) V (p1 Λ p3) und
p1 V (p2 Λ p3)  (p1 V p2) Λ (p1 V p3)
 ¬(p1 Λ p2)  ¬p1 V ¬p2 und
¬(p1 V p2)  ¬p1 Λ ¬p2
 ¬(¬p1)  p1
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
16
Normalisierung: Beispiel
select *
from KUNDE K, BESTELLUNG B
where K.KNr = B.KNr and Menge > 10
and (LName = 'Coffeeshop' or LName = 'Kaffeebude');
• Selektionsbedingung in KNF:
(K.Nr = B.KNr) Λ (Menge > 10) Λ
(LName = ’Coffeeshop’ V LName = ’Kaffeebude’)
• Selektionsbedingung in DNF:
(K.KNr = B.KNr Λ Menge > 10 Λ LName = ’Coffeeshop’) V
(K.KNr = B.KNr Λ Menge > 10 Λ LName = ’Kaffeebude’)
• Üblicherweise wird KNF bevorzugt, da damit eine einfache Zerlegung
komplexer Bedingungen in Folgen von Selektionen möglich ist
 später: zuerst Konjunkt abarbeiten, welches mit Index-Scan ausgewertet
werden kann
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
17
Vereinfachung von Ausdrücken
• Idempotenzen
 AVA  A
 AΛA  A
 A V ¬A  true
 A Λ ¬A  false
• Konstantenpropagierung
 Ausnutzung von Transitivität
 Bsp.: A Θ B Λ B = c  A Θ c
• unerfüllbare Ausdrücke
 A > B Λ B >= C Λ C > A  A > A  false
• …
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
18
Phasen der Auswertung von DB-Anfragen
1. Übersetzung und Sichtauflösung
2. Standardisierung und Vereinfachung
3. Optimierung
– Logische Optimierung (algebraische Optimierung, regelbasierte
Optimierung)
– Physische Optimierung (interne Optimierung)
– Kostenbasierte Auswahl
4. Planparametrisierung
5. Code-Erzeugung
6. Ausführung
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
19
Logische (algebraische) Optimierung – 1(2)
• Termersetzung von Termen der Relationenalgebra anhand von
Algebraäquivalenzen
• heuristische Methode: Operationen verschieben, um kleinere
Zwischenergebnisse zu erhalten; Redundanzen erkennen
• Zur Erinnerung: Äquivalenzen der Relationenalgebra
 r  s = s  r
 (r  s)  t = r  (s  t)
 P(Q(r)) = P Λ Q(r)) = Q(P(r))
 B(A(r)) = B(r)
falls B  A
 P(A(r)) = A(P(r))
mit attr(P)  A
 P(r  s) = P(r )  s
falls attr(P)  r
 P(r  s) = P1(r )  P2(s) falls attr(P1)  r und attr(P2)  s
und P = P1 Λ P2
 Weitere Regeln für Mengenoperatoren, Kombination von Operatoren, …
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
20
Logische (algebraische) Optimierung – 2(2)
• Anwenden der Äquivalenzregeln der Relationenalgebra:
 Wichtigste Regeln für Restrukturierung und Transformation:
– Frühzeitige Ausführung von Selektion (σ) und Projektion (π)
ohne Duplikateliminierung, d.h. Selektionen und Projektionen
möglichst weit in Richtung der Blätter verschieben
– Unäre Operatorfolgen (wie σ und π) zu einer Operation
zusammenzufassen
– Gleiche Teile im Operatorbaum nur einmal auswerten
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
21
Logische (algebraische) Optimierung - Beispiel
• Beispiel:
select BestNr, KNr
from BESTELL_INFO
where Datum > '18.02.04' and Bezeichnung = ' Arabica Black'
– wobei BESTELL_INFO eine Sicht über die Tabellen Produkt,
Bestellung und Kunde ist, welche BestNr, KNr, Datum und
Bezeichnung für jede Bestellung liefert
• Phase 1: Übersetzung und Sichtauflösung
+ Phase 2: Standardisierung und Vereinfachung

h_da Prof. Dr. Uta Störl
>
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
22
Beispiel: Anfrageplan ohne Optimierung
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
23
Beispiel: Anfrageplan mit Optimierung – 1(2)
• Verschieben der Selektion und Zusammenfassen der Projektionen
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
24
Beispiel: Anfrageplan mit Optimierung – 2(2)
• Zusätzliche Projektionen
… und was noch?
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
25
Physische Optimierung
•
Bis jetzt wurden nur logische Operationen betrachtet – ohne Berücksichtigung der
Realisierungsform und ohne Berücksichtigung von eventuell vorhandenen Zugriffspfaden
 Auswahl von Berechnungsalgorithmen
• Selektion
– Selektion durch Relationen-Scan
– Selektion über Index
• Projektion
– Projektion durch Relationen-Scan mit/ohne Duplikateleminierung über sortierte/unsortierte
Relation
• Verbund
– Verbund durch Nested-Loops
– Verbund durch Mischen
– Verbund durch Hash-Join
• Gruppierung
– Gruppierung durch Sortierung
– durch Hashing
• …
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
26
Beispiel: Ausführungsplan – 1(2)
select *
from BESTELLUNG
where ProdNr = 42
and (LName = 'Kaffeebude' or LName = 'CoffeeShop')
and Menge < 10
• Ausführungsplan Variante 1:
mit F = (ProdNr = 42) Λ (LName = ’Kaffeebude’ V LName = ’CoffeeShop’) Λ
Menge < 10
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
27
Beispiel: Ausführungsplan – 2(2)
Variante 1
Variante 2
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
28
Physische Optimierung: Kostenmodell
• Welchen Ausführungsplan auswählen?
 Kostenmodelle zur Abschätzung bzw. Berechnung der Kosten eines
Ausführungsplan
– Kostenfunktion: zur Abschätzung der Kosten für Ausführung von
Operationen bzw. Anfragen (siehe auch voriges Kapitel Basisalgorithmen
für Datenbankoperationen)
– Statistiken: über Größe der Relationen (Kardinalität, Tupelgröße),
Wertebereiche und -verteilungen
– Kostenformeln: zur Berechnung der Größen von
(Zwischen-)Ergebnissen auf der Basis der Statistiken
• Hier nur Darstellung der Grundprinzipien(!) – keine detaillierte Betrachtung
– für eine ausführliche Darstellung sei auf Saake/Heuer/Sattler:2005
verwiesen
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
29
Kostenfunktion
• Kostenarten:
– I/O-Kosten: verursacht durch das Lesen und Schreiben von Blöcken
vom bzw. auf den Externspeicher
– CPU-Kosten: für interne Berechnungen, Vergleiche etc.,
– Kommunikationskosten: im Fall verteilter Datenbanksysteme
• üblicherweise:
cost = costIO + W · costCPU
– Faktor W zur Kalibrierung bzgl. Hardware
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
30
Kostenformeln
• Idee:
– Gesamtaufwand durch Abschätzung der Kardinalitäten der
Zwischenergebnisse
– Kardinalität über Selektivität der Operatoren
– Selektivität sel = Erwartete Größe des Ergebnisses
Kardinalität der Eingangsrelation
– Annahmen: Gleichverteilung, Unabhängigkeit der Attribute
σF (r )  sel (F , r )  r
• Beispiel für Selektion:
– Abschätzung (für interpolierbare, arithmetische Werte):
sel ( A  v , r ) 
1
valA, r
v  Amin
sel ( A  v , r ) 
Amax  Amin
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
sel ( A  v , r ) 
Amax  v
Amax  Amin
sel ( A between v1 and v2, r ) 
v2  v1
Amax  Amin
Kapitel 5: Anfrageoptimierung
31
Statistiken
• Problem: Annahme der Gleichverteilung häufig nicht korrekt
 Verbesserung der Abschätzungen
– Parametrisierte Funktionen: Parameter einer Funktion zur
Annäherung der Datenverteilung (z.B. Normal- oder Zipf-Verteilung)
– Stichprobe: Selektivität anhand einer zufälligen Stichprobe
bestimmen
– Histogramme: Approximationen der tatsächlichen Verteilung
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
32
Histogramme: Prinzip
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
33
Arten von Histogrammen – 1(2)
• sehr viele verschiedene Arte von Histogrammen
• 2 der wichtigsten Histogramm-Arten:
Equi-width und Equi-depth (Equi-height) Histogramme
• Beispiel:
225
25
Equi-width Histogramm
Equi-depth / Equi-height Histogramm
Quelle: Saake/Heuer/Sattler:2005
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
34
Arten von Histogrammen – 2(2)
• Weitere wichtige Histogramm-Art: Compressed-Histogramm
– Prinzip: die k-höchsten Attributwerte werden getrennt von den
anderen in 1-elementigen Buckets gespeichert – die anderen in
equi-width (oder equi-height)
Häufigkeit
100
50
1
2
13
23
33
43
53
63
73
83
93
103
Wertbereich von Preis
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
35
Aufbau und Pflege von Histogrammen
• Ziel: Histogramm sollte tatsächliche Verteilung widerspiegeln – auch
nach Updates
• statischer Ansatz: expliziter Aufbau, keine Berücksichtigung von
Änderungen
• dynamischer Ansatz: Anpassung der Histogramme
– Verwaltung einer Stichprobe (sampling) der Relation und Abbildung
der Änderungen auf Stichprobe
– Query Feedback: Nutzung von Anfrageergebnissen zur Anpassung
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
36
Oracle: Optimizer und Statistiken
• bis Oracle 9: Auswahl zwischen regelbasierter (logischer) und
kostenbasierter (physischer) Optimierung
• ab Oracle 10: grundsätzlich kostenbasierte Optimierung
 führen von Statistiken notwendig
– bis Oracle 9: manuelle Entscheidung, für welche Tabellen Statistiken
berechnet (compute) bzw. geschätzt (estimate) werden und für
welche Spalten Histogramme erstellt werden
– ab Oracle 10: wegen kostenbasierter Optimierung werden
Statistiken automatisch vom DBMS geführt (trotzdem noch
Möglichkeit, manuell einzugreifen – Parameter
statistics_level = { all | typical | basic } )
Aktualisierung der Statistiken: zu festgelegten Zeiten in
„Wartungsfenstern“
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
37
Oracle: Statistiken
kostenrelevante Informationen
• für Tabellen: (in View user_tables bzw. user_tab_statistics)
– Anzahl der Tupel (num_rows)
– Anzahl der belegten Blöcke (num_blocks)
– durchschnittliche Tupellänge (avg_row_len)
• für Spalten (in View user_tab_col_statistics)
– Anzahl der verschiedenen Werte (num_distinct)
– Anzahl der Nullwerte (num_nulls)
– Art des Histogramms (histogram)
– Anzahl der Buckets im zugehörigen Histogramm (num_buckets)
• für Indexe (in View user_indexes)
– Anzahl der Blattseiten (leaf_blocks)
– Anzahl der Indexebenen (blevel)
– Cluster-Faktor (clustering_factor)
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
38
Oracle: Histogramme
Arten von Histogrammen
• Equi-depth (height-balanced genannt)
• Compressed-Variante (frequency genannt)
– Jeder(!) Wert der Spalte hat korrespondierendes Bucket mit
Häufigkeit des Wertes
– Anwendung, wenn Anzahl der verschiedenen Werte der Spalte
≤ Anzahl der Buckets
• Auswahl der Histogramm-Art in Abhängigkeit von Häufigkeit der Werte
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
39
Oracle: Planausgabe – 1(2)
Prinzip:
• Ausführungsplan für ein SQL-Statement kann mit explain plan erzeugt
(dabei wird das Statement NICHT ausgeführt):
explain plan [ set statement_id = 'test' ]
for SQL-Anweisung
• Erzeugter Ausführungsplan wird in Tabelle plan_table (> 30 Attribute)
gespeichert und kann angezeigt werden:
select lpad(' ',2*level) ||operation||' '||options||' '||object_name
as q_plan
from plan_table
where statement_id = 'test'
connect by prior id = parent_id and statement_id = 'test' start
with id = 1;
– Alternative 1: Hilfsprogramm utlxpls.sql (gibt letztes in plan_table
gespeichertes Statement aus)
– Alternative 2: grafische Ausgabe über Oracle SQL Developer
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
40
Oracle: Planausgabe – 2(2)
• Seit Oracle10 auch mit Kostenangaben
• Beispiel:
select buch.isbn, count(*)
from buch, autor
where buch.ISBN = autor.ISBN
group by buch.isbn
having count(*) > 2;
Planausgabe
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
41
Oracle: Optimierungsziele
• Angabe von Optimierungszielen möglich
– all_rows: Optimierung bezüglich der Zeit der Bereitstellung des
Gesamtergebnisses
– first_rows (n): Optimierung bezüglich der Zeit der Bereitstellung der
ersten n Tupel des Gesamtergebnisses
• Das Optimierungsziel kann direkt im Statement angegeben werden:
select /*+ first_rows(10) */ AngNr, Name
from Angestellte
where AbtNr = 20;
oder für die ganze Session gesetzt werden:
alter session set optimizer_goal = first_rows;
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
42
Oracle: Hints für den Optimizer
• Angabe von Hints für den Optimizer möglich:
select /*+ Hint-Liste */ Attribute
from ...
– Auswahl konkreter Algorithmen (z.B. use_nl für Nested-Loops-Verbund,
use_merge für Merge-Verbund, use_hash für Hash-Verbund)
– Verwendung oder Nichtverwendung von Indexen (z.B. index für einen
bestimmten Index, full für Erzwingung eines Full-Table-Scans)
– Reihenfolge der Berechnung z.B. von Mehrfachverbunden (ordered für die
Vorgabe der Verbundreihenfolge entsprechend der from-Klausel)
– … und vieles mehr …
• Beispiel:
h_da Prof. Dr. Uta Störl
select /*+ full(p) */ ProdNr, Bezeichnung
from PRODUKT p
where ProdNr = 47110815;
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
43
DB2: Optimizer und Statistiken
• Auswahl von 7 verschiedenen Optimierungsklassen (dadurch
Entscheidung, ob regelbasiert oder kostenbasiert und welche Regeln
bzw. welche Statistiken verwendet werden)
• Statistiken: manuelle Entscheidung, für welche Tabellen Statistiken
geführt werden und in welchem Umfang
– Informationen nur über Tabelle
– zusätzlich Histogramme (auf allen oder ausgewählten Spalten)
– zusätzlich (oder ausschließlich) Indexstatistiken (auf allen oder
ausgewählten Indexen)
runstats on table <tablename>
[ with distribution [ on columns <columlist> ] ]
[and [ [ sampled | unsampled ] detailed ] indexes |
for [ [ sampled | unsampled ] detailed ] indexes ]
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
44
DB2: Statistiken
• für Tabellen: (in View syscat.tables)
– Anzahl (Kardinalität) der Tupel (card)
– Anzahl der Seiten der Relation (fpages)
– Anzahl der tatsächlich genutzten Seiten der Relation (npages)
– Anzahl der Überlauf-Records der Relation (overflow)
• für Spalten (in View syscat.columns)
– Anzahl der verschiedenen Werte (colcard)
– Durchschnittsgröße der Spaltenwerte (avgcollen)
– Anzahl der Nullwerte (numnulls)
– Zweithöchster Wert (high2key)
– Zweitniedrigster Wert (low2key)
• für Indexe (in View syscat.indexes)
– Anzahl der Blattseiten (nleaf)
– Anzahl der Indexebenen (nlevels)
– Cluster-Faktor (clusterratio)
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
45
DB2: Histogramme
• Kombination aus Compressed- und Equi-depth-Histogramm
– k häufigsten Werte werden in Einzel-Buckets abgelegt
– die anderen Werte werden in Quantilen verwaltet: Speicherung der
kumulativen Häufigkeiten (dem Wert an der i-ten Position wird die
Anzahl aller Tupel, die kleiner oder gleich diesem Wert sind,
zugeordnet)
• Parameter
– num_freqvalues: Anzahl der Werte im Compressed-Histogramm
(Standardwert 10)
– num_quantiles: Anzahl der Quantile im Equi-depth-Histogramm
(Standardwert 20)
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
46
DB2: Sampling
runstats on table <tablename>
[ with distribution [ on columns <columlist> ] ]
[and [ [ sampled | unsampled ] detailed ] indexes |
for [ [ sampled | unsampled ] detailed ] indexes ]
• Beispiele
RUNSTATS ON TABLE employee
WITH DISTRIBUTION ON COLUMNS (empid, empname)
RUNSTATS ON TABLE employee AND DETAILED INDEXES ALL
– seit Version 10.1 ist für Indexe SAMPLED die Default-Variante
– Sampling kann genauer spezifiert werden (Menge und
Auswahlalgorithmus)
RUNSTATS ON TABLE employee WITH DISTRIBUTION AND DETAILED
INDEXES ALL TABLESAMPLE SYSTEM(20) INDEXSAMPLE SYSTEM(30)
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
47
DB2: Optimierungsklassen
• Auswahl einer Optimierungsklasse durch
– prep queryopt und bind queryopt für Embedded SQL bzw.
– set current query optimization = klasse für interaktive Anfragen
• 7 (früher 10) Klassen – Auswahl der wichtigsten Klassen:
– Klasse 0: minimale Optimierung
– Klasse 1: keine Auswertung von Attributwertverteilungen zur
Selektivitätsabschätzung
– Klasse 2: Auswertung von Attributwertverteilungen
– …
– Klasse 5: Standardeinstellung
– …
– Klasse 9: alle Optimierungstechniken (nur für komplexe, vorübersetzte
Anfragen in Embedded-SQL-Programmen)
• Außerdem möglich: optimize for-Klausel am Ende eines SFW-Blocks zur
Spezifikation der Anzahl der zu lesenden Tupel
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
48
DB2: Aktualisierung der Statistiken
Aktualisierung der Statistiken
• manuell (Ausführen des Kommandos runstats)
• ab Version 8.2 auch automatisch (abhängig von Veränderungen auf
dem Datenbestand und Server-Last) – was ist die Herausforderung?
• ab Version 8.2 außerdem LEO (Learning Optimizer)
– Vergleich der abgeschätzten Kardinalitäten mit den tatsächlichen
Ergebnissen (siehe nächste Folie)
 Speicherung eines Anpassungsfaktors
– Warum nicht direkt Aktualisierung der Statistiken?
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
49
DB2: LEO (Learning Optimizer)
Quelle: V. Markl, G. M. Lohman, V. Raman
LEO: An autonomic query optimizer for DB2,
IBM SYSTEMS JOURNAL, VOL 42, NO 1, 2003
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
50
DB2: Planausgabe – 1(2)
• Planerstellung aktivieren:
set current explain mode = yes | explain
– explain: nur Planerstellung nicht Ausführung der Anfrage
• Plan für SQL-Anfrage erstellen:
explain plan
for SQL-Anweisung
• Grafische (db2expln) und textuelle (db2exfmt) Ausgabe möglich
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
51
DB2: Planausgabe – 2(2)
Beispiel:
select produkt.pid, produktname
from produkt, bestellung
where produkt.pid = bestellung.pid
group by produkt.pid, produktname
having count(*) > 1000;
Planausgabe
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
52
Zusammenfassung
• Ablauf der Anfrageverarbeitung
• Vereinfachung und Normalisierung von Anfragen
• Logische (algebraische) Optimierung - query rewriting
• Physische Optimierung mit Kostenmodell
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
53
Architektur von Datenbanksystemen
 Architektur von Datenbanksystemen
 Verwaltung des Hintergrundspeichers
 Dateiorganisation und Zugriffsstrukturen
 Basisalgorithmen für Datenbank-Operationen
 Anfrageoptimierung
• Transaktionsverwaltung und Recovery
• Verteilte Datenbankarchitekturen
h_da Prof. Dr. Uta Störl
Architektur von DBMS WS 2015/16
Kapitel 5: Anfrageoptimierung
54