Vorlesung4 - Anfragebearbeitung (Teil 2)

WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
Vorlesung #4
Anfragebearbeitung (Teil 2)
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
„Fahrplan“
 Besprechung von Übungsaufgaben
 Widerholung Vorlesung #3
 Logische Optimierung
 Physische Optimierung
 Kostenmodelle
 Selektivitäten
 Kostenabschätzungen für Joins, Sortierung
 Tuning von Datenbankabfragen





Generieren von Statistiken
Kostenpläne (EXPLAIN PLAN)
„Optimizer Hints“
Kostenbasierte vs. regelbasierte Optimierung
Umschreiben von Abfragen, Materialisieren von Teilabfragen
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
2
Kostenmodelle
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
 Heuristische Methoden für Bestimmung der
Ausführungspläne sollen in kürzester Zeit nahezu
optimale Ergebnisse liefern. Oft entstehen durch
„Raten“ sehr schlechte Pläne
 Ein Kostenmodell ist notwendig, als Basis für
Vergleich von mehreren Ausführungsplänen
 Damit der Aufwand für die Kosten (d.h. Laufzeit)
eines Operatoren der physischen Algebra geschätzt
wird, benötigt man weitrechende Informationen über




Indexe
Ballung (Clustering)
Kardinalitäten (z.B. Anzahl der Zeilen in einer Tabelle)
Verteilungen der Werte (z.B. Normalverteilung)
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
3
Kostenmodelle (2)
© Bojan Milijaš, 10.11.2004
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
Vorlesung #4 - Anfragebearbeitung (Teil 2)
4
Selektivitäten
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
 Bei der Aufwandsabschätzungen spielt die Frage,
wie viele Tupel sich bei der Bedingung p
qualifizieren würden, die entscheidende Rolle
 Die Selektivität eines Suchprädikats p schätzt die
Anzahl der qualifizierenden Tupel relativ zur
Gesamtanzahl der Tupel in der Relation.
 Beispiele
 die Selektivität einer Anfrage, die das Schlüsselattribut einer
Relation R spezifiziert, ist 1/ #R, wobei #R die Kardinalität
der Relation R angibt.
 Wenn ein Attribut A spezifiziert wird, für das i verschiedene
Werte existieren, so kann die Selektivität als (#R/i) / #R oder
1/i abgeschätzt werden.
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
5
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
6
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
7
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
8
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
9
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
10
Tuning von
Datenbankabfragen
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
 Bei einer langsamen Abfrage schaut man sich den
Ausführungsplan an (Folie #13)
 Wenn es viele „full table scans“ und „nested loop
joins“ gibt, gibt es auch meistens
Optimierungspotential
 Vorher muss man Statistiken erstellen, denn sie
sind die Basis für den Kostenmodell des Optimierers
und müssen per Hand angelegt werden
 Es gibt keinen SQL Standard
ANALYZE TABLE t COMPUTE STATISTICS;
--(Oracle)
runstats on table ... --(DB2)
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
11
Tuning von
Datenbankabfragen (2)
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
 Auf gleicher Weise werden Index-Statistiken
erstellt
 Das ganze muss periodisch wiederholt
werden
 Die ermittelten Werte werden in Data
Dictionary mit anderen SchemaInformationen gespeichert
 Beispiel (Oracle):
SELECT *
FROM user_tables;
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
12
Tuning von
Datenbankabfragen (3)
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
 Nächster Schritt ist es, sich den Plan mit
EXPLAIN PLAN FOR
SELECT ...
FROM ...
WHERE ... ;
 anzuschauen, bzw. mit einem
Optimierungswerkzeug
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
13
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
14
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
15
Tuning von
Datenbankabfragen (4)
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
 Stellt man fest, dass der Optimierer nicht die
optimalen physischen Algebra-Operatoren
gewählt hat, so kann ihm mit Hilfe von
Optimizer Hints „den richtigen Weg weisen“
 Beispiel für die Index-Nutzung-Hint (Oracle)
SELECT /*+ index(Individuals Name)*/
Name, count(1)
FROM Individuals
WHERE Name LIKE `M%´
GROUP BY Name;
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
16
Tuning (5)
regel- vs. kostenbasiert
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
 Man muss den Optimierungslevel der eingesetzten
DBMS in Erfahrung bringen
 Oft werden aus Aufwandsgründen nur Heuristiken
verwendet (Pushing Selections, Nutzung von
Indexen wann immer möglich usw.) – man spricht
von regelbasierter Optimierung
 Kostenbasierte Optimierung bedeutet viel mehr
Aufwand (Statistiken, Modell, Kostenberechnung und
Vergleiche etc.), produziert aber meistens deutlich
bessere Ausführungspläne als regelbasierte
Optimierung
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
17
Tuning (6)
Umschreiben von Abfragen
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
 Oft ist aber das manuelle Eingreifen eines
Datenbank-Entwicklers nötig. Dabei werden
meistens
 korrelierte Abfragen aufgelöst,
 verschachtelte Schleifen in prozeduralen
Wirtssprachen (wie Java, C oder PL/SQL)
aufgelöst und in effizientere Datenbankabfragen
umgeschrieben
 Teilabfragen in Form von MATERIALIZED VIEWs
oder normalen Tabellen für temporäre Zwecke
materialisiert
© Bojan Milijaš, 10.11.2004
Vorlesung #4 - Anfragebearbeitung (Teil 2)
18
WS 2004/2005
Datenbanken II - 5W
Mi 17:00 – 18:30
G 3.18
Vorlesung #4
Ende