Friedrich-Schiller-Universität Jena „Autonomic Database Administration“ Self-Manageability in MS SQL Server (Teil 2) Johannes Sternatz Matrikelnummer 70228 Seminar im Wintersemester 2006/2007 Fakultät für Mathematik und Informatik Lehrstuhl für Datenbanken und Informationssysteme Prof. Dr. Klaus Küspert Betreuer: David Wiese Jena, den 22. Dezember 2006 Inhaltsverzeichnis 1 Index Selection Tool..................................................................................................... 1 1.1 Einleitung................................................................................................................ 1 1.2 Candidate Index Selection....................................................................................... 3 1.3 Configuration Enumeration..................................................................................... 4 1.4 Multi-Column Index Generation............................................................................. 5 1.5 Cost Evaluation ....................................................................................................... 5 1.6 What-If Index Creation ........................................................................................... 6 1.7 Experimentelle Studien ........................................................................................... 8 1.8 Fazit ........................................................................................................................ 9 2. Automated Statistic Management ........................................................................... 10 2.1 Einleitung.............................................................................................................. 10 2.2 Statistics on Intermediate Tables (SITs) ............................................................... 10 2.3 Self Tuning Histograms (ST-Histograms) ............................................................ 12 Literaturverzeichnis ..................................................................................................... IV -I- Abbildungsverzeichnis Abbildung 1: Architektur des Index Selection Tools........................................................ 2 Abbildung 2: Interfaces der HCA-Engine......................................................................... 7 Abbildung 3: Aufbau des TPCD-Benchmarks.................................................................. 8 Abbildung 4: Vergleich des Index Selection Tools mit einem Baseline-Algorithmus ..... 9 Abbildung 5: SITs vs. Statistiken auf Basistabellen ....................................................... 12 Abbildung 6: Beispiel für die Verfeinerung eines ST-Histograms. ................................ 13 Abbildung 7: Vergleich der Genauigkiet verschiedener Statistiken ............................... 14 - II - Abkürzungsverzeichnis DBMS Datenbankmanagementsystem HCA Hypothetical Configuration Analysis QEE Query Execution Engine SIT Statistics on Intermediate Tables ST-Histograms Self Tuning Histograms SQL Structured Query Language - III - 1 Index Selection Tool 1.1 Einleitung Das von Surajit Chaudhuri und Vivek Narasayya entwickelte und 1997 auf der 23. Konferenz über „Very Large Databases“ erstmalig vorgestellte Index Selection Tool ([CN97]) markierte den Auftakt des Auto-Admin-Projektes bei Microsoft Research. Basierend auf den Arbeitsweisen des Tools, welches bereits in Microsofts SQL Server 7.0 integriert wurde, konstruierte Microsoft den auch auf andere Fragestellungen des physischen Designs von Datenbanken (v.a. der Partitionierung sowie Materialisierung von Views) antwortenden Database Tuning Advisor, der bereits im ersten Teil dieser Seminararbeit vorgestellt worden ist. Die Zielsetzung des Index Selection Tools besteht darin, eine Menge nützlicher Indizes1 für eine bestehende Datenbank vorzuschlagen. Dabei ist nützlich das, was die Performance gemessen anhand einer Referenzworkload gegenüber der Ausgangslage verbessert. Der Nützlichkeit eines Index’ auf der einen Seite steht ihm zuzuordnende Aktualisierungs- und Speicherplatzkosten auf der anderen Seite entgegen, welches die Anzahl der vom Index Selection Tools vorzuschlagenden Indizes nach oben hin beschränkt. Die aus diesen Gründen zu treffende Auswahl nützlicher Indizes aus der Menge der möglichen Indizes ist ein NP-schweres Evaluationsproblem, bei dem der benötigte Rechenaufwand exponentiell mit der Anzahl der Attribute ansteigt. Zur Verdeutlichung sei als Beispiel eine Tabelle T mit s Spalten genannt, auf die maximal über s einspaltige Indizes zugegriffen werden kann. Die Menge möglicher einspaltiger Indexkombinationen ist mit der Potenzmenge aller Spalten der Tabelle T identisch und beinhaltet somit 2s mögliche einspaltige Indexkombinationen. Für mehrdimensionale Indizes ergeben sich semiäquivalente Aussagen. Ein naives Evaluieren aller möglichen Indexkombinationen ist somit nicht effizient durchführbar; vielmehr sind intelligente Strategien zur Reduktion des Suchbereiches nötig. So ist es zum einen denkbar, auf Basis semantischer Informationen wie Fremdschlüsselbeziehungen, Uniquenessinformationen oder Tabellengrößen gute Indizes von schlechten zu trennen. Ohne Berücksichtigung der Spezifika der Referenzworkload können unter Umständen mit Hilfe jener „Textbook Solution“ aber Indizes als gut separiert werden, welche nie in den Ausführungsplänen der Workload benutzt werden. Und auch wenn die Workload bei der Auswahl von Indizes im Sinne von DBMS-internen hardcoded rules mit Beachtung fände, ist keinesfalls gewährleistet, dass der kostenbasierende Optimizer, dem die Wahl des optimalen Zugriffspfades obliegt, eben jene Indizes zur Ausführung der Referenzworkload bestimmt. Folglich muss ein Index Selection Tool nicht nur semantische und workloadabhängige Informationen im Auswahlprozess mit einbeziehen, sondern er muss zugleich sicherstellen, dass die ausgewählten Indizes einen Workloadausführungskostenvorteil gegenüber den bisherigen Indizes haben. 1 Ein Index (Zugriffspfad) ist eine physische Datenstruktur, die es ermöglicht, effizient und tablescanverhindernd auf Datensätze zuzugreifen. -1- Das Index Selection Tool bietet ein Verfahren an, dass auf Basis einer einzugebenden Workload unter Verwendung heuristischer Komponenten neue Indizes enumeriert; diesbezüglich die Ausführungskosten der Workload berechnet und jene Menge an Indizes als Vorschlag ausgibt, für die die geringsten Kosten vom Optimizer prognostiziert wurden. i iv ii v iii Abbildung 1: Architektur des Index Selection Tools (Quelle: [CN97]) Die Architektur des Index Selection Tools besteht so aus fünf Komponenten, die der Autor in den folgenden Abschnitten einzeln vorstellen wird: i. Zur Reduktion des Evaluationsaufwandes wird der Enumerationskomponente eine Kandidatensuche (Candidate Index Selection) vorgeschaltet, welche schlechte Indizes heuristisch aussortiert. ii. Auf Basis der Indexkandidaten werden verschiedenen Indexkombinationen (sog. Konfigurationen) enumeriert. iii. Zur Auswahl der besten Indexmenge ist ein Kostenvergleich zwischen den Indexkombinationen durch den Optimizer im Rahmen des Cost-EvaluationModuls nötig. iv. Um die Kosten von Indexkombinationen zu berechnen, benötigt der Optimizer Kenntnis von den in der Evaluierung zugrunde gelegten Katalogdaten. Dies stellt die What-If-Index-Creation-Komponente sicher. -2- v. Das Index Selection Tool durchläuft die Komponenten römisch Eins bis Vier iterativ, wobei die Dimension der berechneten Indizes mit jedem Durchlauf steigt. Dieses Vorgehen wird von der Multi-Column-Index-GenerationKomponente gesteuert. 1.2 Candidate Index Selection Das Ziel der Candidate Index Selection besteht in der Reduktion des Enumerationsaufwandes durch Beschränkung der infragekommenden (zunächst einspaltigen) Indizes auf Indexkandidaten, einer Teilmenge aller Spalten. Dazu trifft das Index Selection Tool zwei heuristische Grundannahmen, die gute Indizes stets erfüllen sollten. Erstens werden Indizes nur dann zur Ausführung eines Queries benutzt, wenn sie Spalten enthielten, die in den WHERE-, GROUP-BY- oder ORDER-BY-Klauseln der Query vorkämen. Folglich kann die Menge der optimalen Indizes I* nur aus solchen, indexierwürdigen Spalten bestehen. Zum Zweiten muss sich ein potenzieller Indexkandidat gegenüber anderen Indizes im Sinne einer Tournament Selection in mindestens einer Query bewähren, das heißt, er muss für mindestens eine Query Q1 Element der besten Indexmenge I1* sein. Um diese Bewährungsprobe durchzuführen, wird das Index Selection Tool rekursiv mit dem Input Q1 als Workload aufgerufen. Innerhalb dieses Teilproblems wird die Menge der Indexkandidaten auf die Menge indexierwürdiger Spalten gesetzt. Zusammenfassend ergibt sich folgende Reihenfolgebeziehung: {Spalten} ⊃ {Indexierwürdige Spalten} ⊃ {Indexkandidaten} ⊃ I* Wie erwähnt ist die Auswahl der Indexkandidaten heuristischer Natur, dessen Nützlichkeit vor allem in Anbetracht der zweiten Annahme kritisch zu hinterfragen ist. So werden durch die Bewährungsprobe nur jene Indizes als Kandidaten gekürt und somit in den Enumerationsprozess mit einbezogen, die einmal Element der besten Indexmenge I1* für das Teilproblem einer Query Q1 sind. Indizes, die nie einen ersten Platz in der Tournament Selection behaupten konnten, jedoch in vielen anderen Queries der Workload zweite Plätze ergatterten, werden im Gegensatz zu einem Index, der sich in einem einzigen Query bewähren konnte und bei den restlichen Queries nur schlechte Plätze erreichte, verworfen. -3- Dieses Problem der Atomarität der Bewährungsprobe und der daraus resultierende Suboptimalität der Enumeration ließe sich verhindern, indem anstatt der Kür aller QueryErstplatzierten eine Nutzenermittlung auf Basis der (relativen) Ausführungskosten aller in den Teilproblemen enumerierten Konfigurationen über alle Queries der Workload hinweg statt fände.2 Die Vereinigung der Konfigurationen mit dem höchsten Nutzen (aka geringsten Ausführungskosten) für alle Queries der Workload bilde die Menge der Indexkandidaten. Da die heuristische Einschränkung auf Indexkandidaten einen maßgeblichen Einfluss auf den Grad der Optimalität der Enumerationskomponente hat, ist es zu überprüfen, ob solch eine nutzenbasierte Selektion das im Index Selection Tool hinterlegte Verfahren wie vom Autor vermutet - dominiert. 1.3 Configuration Enumeration Basierend auf der Indexkandidatenmenge wird in der Configuration Enumeration die Menge der zugriffszeitminimierenden Indizes I* berechnet. Da trotz der Beschränkung auf Indexkandidaten eine naive Enumeration ein, wenn auch abgemildertes, NPschweres Problem darstellt, dies aber auf der anderen Seite einen höheren Optimalitätsgrad gegenüber von Heuristiken garantiert, entschied sich [CN97] ein zweistufiges Verfahren anzuwenden. Dazu wird, neben der Workload W und der Indexkandidatenmenge Kand, vom Benutzer eine maximale Kardinalität k von I* in Form einer Speicherplatzbeschränkung eingelesen, die der Enumerationskomponente als Abbruchsbedingung dient. In der ersten Phase des Verfahrens werden m der k Indizes3 in I* als Optimalitätsgarant naiv enumeriert, das heißt, es werden alle m über k möglichen Konfigurationen (einspaltiger) Indizes aus Kand evaluiert, und jene Konfiguration in I* übernommen, die gemessen an der Workload die geringsten Ausführungskosten hat. Ist m identisch k bricht das Verfahren ab. Sonst wird in der zweiten Phase die bisherig beste Indexmenge I* solange um einen noch nicht aufgenommen Indexkandidaten i* erweitert, bis sich entweder die Ausführungskosten der Workload verschlechtern oder die obere Grenze von I* k erreicht ist. Dieser Heuristik liegt zugrunde, dass der hinzuzufügende Indexkandidat i* sich als bester, verbleibender Kandidat im Sinne von höchster Ausführungskostensenkung gegenüber den anderen Kandidaten auszeichnet. Der Enumerationsalgorithmus stellt somit ein m-k-Greedy-Verfahren dar, dessen Optimalitätsgüte für die ersten m Indizes garantiert und dessen Laufzeit durch ein HillClimbing-Ansatz in der zweiten Stufe auf O(c + k*#Kand) limitiert ist. 4 2 Der Rechenaufwand der Candidate Index Selection steigt durch ein nutzenbasiertes Verfahren nicht an, da für die jeweiligen Teilprobleme die Kosten für alle enumerierten Konfigurationen in einer Cost Evaluation Table abgespeichtert werden. Vergleiche Kap. 1.5. 3 m <= k. 4 Sei m stets konstant. -4- 1.4 Multi-Column Index Generation Das Index Selection Tool generiert nicht nur einspaltige Indexvorschläge, sondern kann durch Iteration des gesamten Verfahrens auch optimale, mehrspaltige Indizes finden. Dabei steigt pro Iteration die Dimension der berechneten Indizes an. Als Input dient der Folgeiteration die bisherige, beste Indexmenge I* aus der vorhergegangenen Berechnung. Für die Auswahl der mehrdimensionalen Indexkandidaten werden aus Gründen der Laufzeitreduktion zwei zusätzliche, alternative Bedingungen in die Candidate Selection aufgenommen. Zum einen fordert man, dass ein d+1-dimensionaler Index id+1 = {s1, s2, … , sd, sd+1} aus Spalten bestünde, die alle indexierwürdig seien, und dessen erste d Spalten ein d-dimensionaler Index in der bisherigen Indexmenge I* sei.5 Alternativ zu dieser MC-LEAD Annahme kann auch der aggressivere MC-ALL Verwendung finden, der für einen d+1-dimensionalen Indexkandidaten voraussetzt, dass er nur aus Indizes der bisherigen Indexmenge I* bestünde. MC-All ist dabei die schärfere heuristische Bedingung, welcher die Laufzeit der Multi Column Index Generation gegenüber MC-LEAD zu Lasten der Optimalität reduziert. 1.5 Cost Evaluation Wie im Abschnitt 1.3 erläutert, baut die Hill-Climbing-Suche neuer Konfigurationen C auf der Kostenvorhersage Kosten(C,W) des Optimizers bezüglich der Workload W auf. Ohne zusätzliche Beschränkungen sind bei #W Queries in der Workload und p möglichen Konfigurationen #W*p Aufrufe des Optimizers nötig, was bei zeitgleichen Operativbetrieb zu Überlastungsproblemen des Optimizers führen kann. Deshalb ist zu überlegen, wie die Anzahl der Optimizeraufrufe reduziert werden kann. Die im Index Selection Tool realisierte Divide-Et-Impera-Lösung baut auf der Eigenschaft der Atomarität einer Konfiguration auf: Man nennt eine Konfiguration C für eine Workload W oder ein Query Q genau dann atomar, wenn die Query Execution Engine (QEE) alle Indizes aus C zur Ausführung eines Queries aus W benutzt. Sei Q ein SELECT- oder UPDATE6-Query und C für Q nicht atomar. Dann ist mindestens ein Index aus C überflüssig und kann deshalb gestrichen werden. Die Ausführungskosten verändern sich durch die Streichung überflüssiger (unbenutzter) Indizes nicht. Folglich gilt: Kosten(C,Q) = min(Kosten(C’,Q)| C’ ⊂ C ^ C’ ist atomar für Q) 5 6 D.h. id+1 = {id*, sd+1} mit id* in I* Annahme: Zugriffspfad muss nicht geupdatet werden. -5- Für INSERT- und DELETE-Queries7 kann man die Ausführungskosten für Q in zwei Teile trennen: In Kosten der Selektion der relevanten Tupel sowie Änderungskosten der Tabellen und Indizes. Sei C für Q nicht atomar, so ergeben sich die Selektionskosten aus obiger Gleichung. Bezüglich der Indexupdatekosten muss für jeden Index in C geprüft werden, welchen Einfluss dieser auf die Updatekosten hat. Folglich gilt: Kosten(C,Q) = min(Kosten(C’,Q)| C’ ⊂ C ^ C’ ist atomar für Q) + ∑i in C [Kosten({i},Q) – Kosten({},Q)] Auf Basis dieser Überlegungen wird deutlich, dass für Q nicht atomare Konfigurationen nicht vom Optimizer evaluiert werden müssen. Im Sinne dynamischer Programmierung werden die vom Optimizer berechneten Kosten atomarer Konfigurationen in einer CostEvaluation-Tabelle verwaltet, welche zur Ermittlung der Kosten nichtatomarer Konfigurationen herangezogen wird. Die Gretchenfrage, die hierbei jedoch noch zu beantworten ist, ist die Problematik, wie die Cost-Evaluation-Komponente die Atomarität von C bezüglich Q erkennt. Eine deterministische Lösung ist unpraktikabel, da erst mit Hilfe des Ergebnisses der QEE tatsächlich festgestellt werden kann, ob Atomarität vorliegt oder nicht. Dann ist diese Information aber nutzlos, da der Optimizer schon zur Berechnung des optimalen Ausführungsplanes herangezogen worden ist, obwohl man dies bei Nichtatomarität gerade verhindern wollte. Folglich sind Annahmen über das Verhalten der QEE nötig, mit denen man die Zugriffspfadwahl antizipieren kann. Die CostEvaluation-Komponente vermutet hierbei, dass die QEE maximal j Indizes pro Tabelle und t tabellendifferente Indizes8 verwendet. Daraus ableitend wird für eine Konfiguration C bezüglich eines Queries Q die Atomarität antizipiert, wenn alle Indizes in C aus indexierwürdigen Spalten von Q bestünden, maximal j Indizes aus einer Tabelle und maximal t Indizes aus verschiedenen Tabellen in C vorkämen. Empirisch bewährt, im Sinne von Evaluationsreduktion bei gleichzeitig geringen Optimalitätseinbußen, haben sich die Werte j = t = 2. 1.6 What-If Index Creation Um die Kosten einer Konfiguration C bezüglich der Workload W evaluieren zu lassen, benötigt der Optimizer Kenntnis von der hypothetischen Konfiguration C. Dies kann auf dem normalen Wege mit Hilfe des CREATE-INDEX-Statements geschehen, bei dem alle Indizes in C tatsächlich auf der Datenbank erstellt werden. Ohne sonstige Anpassungen kann so der Optimizer mit Hilfe der in den Systemkatalogen hinterlegten Indizes die Kosten für die Workload W berechnen. Jedoch behindert jene naive Lösung durch die Ausführung zahlreicher CREATE-INDEX-Statements den parallel stattfindenden Operativbetrieb. Auch sind keine alternativen Datenbankzustände modellierbar, da der Optimizer anhand der zur Laufzeit vorliegenden Systemeigenschaften agiert. 7 8 (und Update-Queries mit zu ändernden Zugriffspfaden) D.h. Nur ein maximal t Tabellen betreffender Join kann komplett über Indizes erfolgen. -6- Eine weitaus leistungsfähigere Lösung besteht in der Simulation hypothetischer Konfigurationen, welche jedoch aufgrund mangelnder Katalogeinträge eine Anpassung des Optimizers erfordert. Vorteilhaft ist hierbei die mögliche Berücksichtigung alternativer Datenbankzustände sowie die erzwingbare Vorfahrt des Operativbetriebs. Abbildung 2: Interfaces der HCA-Engine (Quelle: [CN97]) Die Simulation gegenüber den SQL-Server-Basiskomponenten übernimmt hierbei die Hypothetical Configuration Analysis Engine (HCA-Engine), welche folgende Interfaces für das Index Selection Tool bereithält: 1. CREATE WORKLOAD definiert auf Basis einer mir dem SQL Profiler aufgezeichneten oder selbsterstellten .sql-Datei die Workload. 2. DEFINE CONFIGURATION definiert eine Menge hypothetischer Indizes, welche in der Enumeration-Komponente gebildet werden. 3. SET DATABASE SIZE ermöglicht die Modellierung alternativer Datenbankzustände, in dem für jede Tabelle Tj ein Skalierungsfaktor mj angegeben werden kann, die der Optimizer bei der Kostenberechnung berücksichtigt. -7- 4. Die Simulation alternativer Indizes gegenüber dem Optimizer baut auf der Tatsache auf, dass die tatsächliche Struktur des Index’ für die Kostenabschätzung irrelevant ist. Nur die Größe des Index’ besitzt hier Relevanz, so dass für die Definition hypothetischer Indizes eine reine statistische Betrachtung mit dem Statement „CREATE INDEX … WITH STATISTICS_ONLY“ genügt. Um den Aufbau der Statistiken zu beschleunigen, wird ein 5%-Sampling durchgeführt. 5. Mit „ESTIMATE CONFIGURATION W FOR C“ wird letztendlich eine Zugriffsmöglichkeit bereitgestellt, mit der die Cost-EvaluationKomponente die Kosten (atomarer) Konfigurationen evaluieren kann. Da keinerlei Katalogeinträge über die hypothetischen Indizes vorhanden sind, muss der Optimizer in einer separaten Verbindung (HC_mode_call) im NoExecution-Modus aufgerufen werden, in der die Konfiguration C sowie die Skalierungsfaktoren übertragen werden. 1.7 Experimentelle Studien Das Index Selection Tool wurde in [CN97] einem non-iterativen Baseline-Algorithmus ohne dynamische Programmierung und Candidate Selection im Rahmen des TPCDBenchmarkes9 gegenübergestellt. Wie der Abbildung 4 auf Seite 9 zu entnehmen ist, erreicht das Index Selection Tool einen Performancevorsprung um Faktor vier bis zehn, wobei bei Benchmarks mit vielen Joins (TPCD_2) der Unterschied am größten ist. Jedoch sei angemerkt, dass zwar der Benchmark unabhängig zusammengestellt worden ist, der Vergleichskandidat aber zu einem von Mircosoft Research eigens vereinfachten Algorithmus gehört. Unabhängige Vergleiche mit ähnlichen Produkten anderer DBMSHersteller (v.a. sei hier der lernende Optimizer LEO von IBM DB2 genannt) waren dem Autor trotz intensiver Suche leider nicht möglich. Abbildung 3: Aufbau des TPCD-Benchmarks (Quelle: [CN97]) 9 Ein vom unabhängigen Transaction Processing Performing Counsil erstellter Benchmark, welcher langlaufende Abfragen gegen komplexe Strukturen in Rahmen von Entscheidungsunterstützungsanwendungen verwendet. -8- Abbildung 4: Vergleich des Index Selection Tools mit einem Baseline-Algorithmus (Quelle: [CN97]) 1.8 Fazit Das Index Selection Tool bietet dem Datenbankadministrator unter einer aufgeräumten Benutzeroberfläche die Möglichkeit, unter Beachtung von Speicherplatzbeschränkungen und Workloadspezifika eine Menge guter Indizes vorschlagen zu lassen. Aufgrund der NP-Schwere des Problems werden zahlreiche heuristische Annahmen getroffen, die - vor allem bezüglich der Tournament Selection bei der Candidate Index Selection - die Optimalitätsgüte des Vorschlages schmälern, auf der anderen Seite aber erst eine praktikable Lösung des Suchproblems ermöglichen. -9- 2. Automated Statistic Management 2.1 Einleitung Um die Kosten eines theoretischen Ausführungsplanes einer Query zu bestimmen, benötigt der Optimizer Kenntnis über die Kardinalitäten von Zwischenergebnissen jenes Planes. Diese können zu Lasten der Güte der Kostenermittlung (und somit der Optimierung) im Sinne einer Gleichverteilungsannahme basierend auf der Tabellengesamtgröße oder aber exakter anhand von Statistiken, welche abhängig vom Selektionsbereich der Query die Anzahl10 betroffener Tupel speichert, geschätzt werden. Auch hier ist der Datenbankadministrator mit einem Tradeoff konfrontiert, da zwar die Optimalitätsgüte mit der Anzahl der Statistiken tendenziell steigt, die Verwaltungskosten für Erstellung und Aktualisierung von Statistiken jedoch die Performance des Systems negativ beeinträchtigen. Dieses Problem versucht man mit Hilfe eines Automated Statistic Managements zu lösen, dessen Hauptaufgabe in der Suche guter Statistiken bei gleichzeitiger Einhaltung oberer Grenzen (wie beispielsweise den verfügbaren Speicherplatz) liegt. Dieses Vorhaben und dessen algorithmische Lösung in [CN01] ähnelt in vielerlei Hinsicht dem Index Selection Tool und wurde deshalb auch in den DTA integriert. Neben jenem Kernelement bietet das Automated Statistics Management noch weitere Möglichkeiten für Verbesserungen, die in den nächsten zwei Abschnitten erörtert werden. 2.2 Statistics on Intermediate Tables (SITs) Ein Kernproblem bei der Verwendung von Statistiken besteht in der Beschränkung jener Strukturen auf Basistabellen. So sind zwar einfache Queries wie Result1 = SELECT * FROM R WHERE R.a > 10 mit Hilfe einer Statistik über R.a, dem die Selektivität für R.a>10 nR.a>10 entnommen wird, einfach durch #Result1 = #R * nR.a>10 abschätzbar, jedoch schlägt die Beschränkung von Statistiken auf Basistabellen bei komplizierteren, tabellenübergreifenden Queries wie im folgenden zu sehen ist fehl. Gesetzt den Fall, folgendes Query müsse vom Optimizer bezüglich seiner Kosten evaluiert werden: Result2 = SF R,S WHERE R.x = S.y AND S.a < 10 10 Zur Vereinfachung sei hier von der tatsächlichen Speicherstruktur der Statistik abstrahiert. - 10 - Eine naïve Kardinalitätsvorhersage, welche nur Statistiken auf Attribute von Basistabellen (hier: R.x, S.y, S.a) zur Verfügung hätte, schätzt durch Ermittlung der Größe des Joins über R und S basierend auf den Histogrammen über R.x und S.y sowie der Selektivität nS.a<10 #Result2 = #Join(R,S) * nS.a<10 . Bei dieser Art der Kardinalitätsabschätzung wird jedoch durch die Multiplikation der Selektivität nS.a<10, welche anhand der vollständigen Basistabelle S berechnet wurde, propagiert, dass S.a in S genauso verteilt ist wie S.a im Join(R,S). Auch ein Umschreiben des Planes, so dass zuerst die Selektion und dann der Join ausgeführt würde, liefert eine falsche Abschätzung, da zwar nun die Selektivität nS.a<10 sich korrekt auf die vollständige Basistabelle S bezieht, die Größe des Joins jedoch nun unbekannt ist und mit Statistiken auf die vollständigen Basistabelle R und S propagiert würde. Beiden Ausführungsplanarten ist gemein, dass die Kostenabschätzung durch Verwendung von Statistiken auf Basistabellen nur dann korrekt ist, wenn die Propagation, das heißt die Annahme der Unabhängigkeit beider WHERE-Bedingungen, zutrifft. Andernfalls fehloptimiert das System oben genannte Query. Eine Lösung jenes Problems liegt in der Verwendung von Statistiken auf Zwischenergebnissen des Ausführungsplanes (Statistics on Intermediate Tables, SITs), welche Microsoft Research in [BC02] vorgeschlagen hat und entweder dynamisch zur Laufzeit des Optimierungsprozesses oder ad-hoc per SQL-Befehl generiert werden können. Von überragender Wichtigkeit ist hierbei die Fragestellung, welche SITs erstellt werden sollen. Da, wie oben erwähnt, die Kostenabschätzungen auf SITs nur dann genauer sind, wenn die Unabhängigkeit aller WHERE-Bedingungen verletzt ist, ist die Erstellung aller möglichen SITs sowohl aus Kosten- (Erstellungsaufwand) als auch aus Nutzensicht fruchtlos. Vielmehr ist eine heuristische Beschränkung auf nützliche SITs zielführend, so dass [BC02] ein DTA-ähnliches, workloadbasierendes Verfahren implementierte, welches in der vorverarbeitenden Candidate Selection nur jene Attribute in SITs zulässt, über die gejoint oder gefiltert wird. Um die Nützlichkeit eines SITs bezüglich einer Query einzuschätzen, wird der Grad der Unabhängigkeit der WHERE-Bedingungen der Query untersucht. Dazu wird anhand von Statistiken auf Basistabellen die Größe des Endergebnisses nach unten („best case“) und nach oben („worst case“) ex ante abgeschätzt. Bei Unabhängigkeitsannahme liegt die vom Optimizer kalkulierte Kardinalität zwischen best und worst case, so dass bei realiter Abhängigkeit und Nichtverwendung von SITs im best case eine Überschätzung, im worst case eine Unterschätzung der Ausführungskosten stattfindet. Folglich ist eine SIT für ein Query genau dann lohnend, wenn die Streuung der Güte der Kostenvorhersage hoch ist, also das Best-Case-Szenario sich hinreichend stark vom Worst-Case-Szenario unterscheidet. - 11 - Die Implementierung von SITs innerhalb des Automated Statistics Managements des Microsofts SQL Servers, welche unter anderem eine Anpassung der Plantransformationskomponente des Optimizers zur Verwendung von SITs vorsieht, wurde in [BC02] gegen ein System, welches nur mit Statistiken auf Basistabellen arbeitet, getestet. Wie den Ergebnissen in Abbildung 5 zu entnehmen ist, kann durch die Verwendung von SITs hier eine durchschnittliche Verbesserung der Ausführungszeiten um 400% erreicht werden. Abbildung 5: SITs vs. Statistiken auf Basistabellen (Quelle: [BC02]) 2.3 Self Tuning Histograms (ST-Histograms) Problembehaftet innerhalb des Automated Statistic Managements ist weiterhin die Tatsache, dass für Statistiken in Form von Histogrammen zum Aufbauzeitpunkt (ad-hoc vs. optimizerinduziert) alle zugrundeliegenden Daten erfasst und sortiert werden müssen. Das heißt bei n Tupeln in Tabelle T kostet die Erstellung einer Statistik über T.a O(n) I/O-Aufwand sowie O(n*log(n)) CPU-Kapazität. Dies ist in Anbetracht der möglichen Behinderung des Operativbetriebs vor allem zu laststarken Zeiten problematisch. Ein denkbarer Ausweg stellt Microsoft Research in [AC99] vor. Jener Lösung liegt zugrunde, dass die Query Execution Engine (QEE) für jedes Query nach Abarbeitung des Ausführungsplanes die Anzahl der betroffenen Tupel kennt. Dieses kostenlose Feedback lässt sich nun – basierend auf den Selektivitätseigenschaften der Query – nutzen, um sich selbstverbessernde Histogramme, welche anfänglich naiv ohne Datenerfassung initialisiert worden, der tatsächlichen Datenverteilung anzupassen. Durch Verwendung solcher ST-Histogramme ist eine erhebliche Einsparung von Erstellungsaufwand zu Lasten der verbesserungsanstoßenden Query-Ausführung möglich. Ein weiterer Vorteil besteht in der automatischen Verfeinerung des Histogramms, da entgegen normaler Statistiken kein UPDATE STATISTICS benötigt wird. - 12 - Modifikationen an ST-Histogrammen sind in drei unterschiedlichen Phasen möglich: i. Die (ad-hoc oder optimizerinduzierte) Initialisierung des ST-Histogramms zum Erstellungszeitpunkt benötigt keinerlei I/O-Kosten. Basierend auf der im Systemkatalog gespeicherten Tabellengröße, den aus dem Wertebereich des statistisch zu approximierenden Attributes entnommenen Histogrammgrenzen sowie der heuristisch zu schätzenden11 Anzahl der Bins (Histogrammbalken) wird anfänglich eine Gleichverteilung zwischen den Grenzen des STHistogramms angenommen, welches einen Scan über alle auftretenden Attributwerte der betreffenden Tabelle überflüssig macht. ii. Während der Queryausführung12 wird auf Basis einer Rückkopplungsschleife in der QEE das ST-Histogramm automatisch, im Sinne einer Binhöhenänderung verfeinert. Dazu wird durch Verwendung der Selektionsgrenzen der Query die Menge betreffender Bins bestimmt, welche auf die Anzahl der von der QEE zurückgelieferten Tupel hin korrigiert wird. Die Binhöhe verändert sich dabei proportional zum Schätzfehler13 sowie zur bisher im STHistogramm gespeicherten Höhe. Abbildung 6 macht dies anhand eines Beispieles deutlich. iii. Neben der Verfeinerung des ST-Histogramms kann parallel auch eine Restrukturierung, im Sinne einer Binstrukturänderung nötig werden, in dem benachbarte Bins nahezu gleicher Höhe verschmolzen (geringe Interklassenvarianz) und Bins mit (vermutlich) hohen intraklassen Höhenunterschieden getrennt werden. Abbildung 6: Beispiel für die Verfeinerung eines ST-Histogramms, bei dem die QEE für das Query „SF R WHERE R.a BETWEEN [20;70]“ 105 Tupel als Feedback liefere. 11 Beispielsweise nach der Regel von Sturgess mit 1+ld(n) Onlinemodus angenommen. Es ist eine auch zur Queryausführung zeitlich versetzte, Log-basierte Verfeinerung möglich. 13 = bisherige Binhöhe aller betroffenen Queries – Anzahl der Tupel aus QEE-Feedback 12 - 13 - Ein experimenteller Vergleich zwischen selbstverbessernden und normalen Histogrammen zeigt in Abbildung 7, dass ST-Histogramme mit Verfeinerung (Refinement) grundsätzlich bessere Kostenabschätzungen liefern als bei Gleichverteilungsannahme, die bei fehlen jeglicher Statistiken vom Optimizer präsumiert wird. Im Kontrast zu den notselftuning MaxDiff-Histogrammen ist der Schätzfehler von ST-Histogrammen stark von der Schiefe (Skew)14 der zugrunde gelegten Attributsverteilung abhängig. So ist bei positiven Schiefen kleiner Eins die Fehlerdifferenz unter 5%, welche jedoch bei zunehmender Schiefe der Verteilung zu Lasten der ST-Histogramme heftig ansteigt. Abbildung 7: Vergleich der Genauigkiet verschiedener Statistiken anhand steigender Verteilungsschiefen (Quelle: [AC99]) Zusammenfassend ist zu konstatieren, dass sich ST-Histogramme vor allem bei nichtschiefen Attributsverteilungen oder - aufgrund der Dominanz gegenüber der Gleichverteilungsannahme - bei Spalten, deren geringe Relevanz normale, genauere Statistiken entbehrlich machen, eignen. Vor allem in der Nische für Data-Warehouse-Systeme bringt die Ersparnis von Initialisierungsaufwand (zu Lasten der Queryausführung) durch Verwendung von ST-Histogrammen hohen Nutzen für den Endanwender, welcher innerhalb des meist zeitkritischen Füllvorganges keine I/O-Kapazität für zusätzliche Statistiken über Attribute der äußerst langen Facttable zur Verfügung hat. 14 Eine Schiefe von Null ist einer symmetrischen Verteilung (z.B. Normalverteilung) gleichzusetzen. Schiefen größer Null symbolisieren rechtsschiefe, linkssteile Verteilungen (z.B. Log-Normalverteilung). - 14 - Literaturverzeichnis [AC99] Aboulnaga, A, Chaudhuri, S., Self-Tuning Histograms: Building Histograms Without Looking at Data. Proceedings of ACM SIGMOD, Philadelphia, 1999. [Ag04] Agrawal S. et. al, Database Tuning Advisor for Microsoft SQL Server 2005. Proceedings of the 30th International Conference on Very Large Databases (VLDB04), Toronto, Canada, 2004. [BC02] Bruno N., Chaudhuri S., Exploiting Statistics on Query Expressions for Optimization. Proceedings of the ACM SIGMOD, Madison, WI, USA, 2002. [BC03] Bruno N., Chaudhuri S., Efficient Creation of Statistics over Query Expressions. Proceedings of 19th International Conference on Data Engineering, Bangalore, India, 2003. [BC04] Bruno N., Chaudhuri S., Conditional Selectivity for Statistics on Query Expressions. Proceedings of the ACM SIGMOD, Paris, France, 2004. [CN00] Chaudhuri S., Narasayya V., Automating Statistics Management for Query Optimizers. Proceedings of 16th International Conference on Data Engineering, San Diego, USA 2000. [CN97] Chaudhuri S., Narasayya V., An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server. Proceedings of the 23rd International Conference on Very Large Databases (VLDB97), Athens, Greece, 1997, pp. 146-155, 1997. [CN98] Chaudhuri, S., Narasayya V., AutoAdmin "What-If" Index Analysis Utility. Proceedings of ACM SIGMOD, Seattle, 1998. - IV -