Friedrich-Schiller-Universität Jena „Autonomic Database

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 -