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