Hysterie um Histogramme

Hysterie um Histogramme
Autoren: Michael Lindermann und Roger Niemeyer, ORDIX AG, Paderborn
DOAGNews Q4_2005
Dieses Werk ist urheberrechtlich geschützt. Die dadurch begründeten Rechte, insbesondere
die der Übersetzung, des Nachdrucks, des Vortrags, der Entnahme von Abbildungen und
Tabellen, der Funksendung, der Mikroverfilmung oder der Vervielfältigung auf anderen
Wegen und der Speicherung in Datenverarbeitungsanlagen, bleiben, bei auch nur auszugsweiser Verwertung, vorbehalten. Eine Vervielfältigung dieses Werkes oder von Teilen dieses
Werkes ist auch im Einzelfall nur in den Grenzen der gesetzlichen Bestimmungen des
Urheberrechtes der Bundesrepublik Deutschland vom 9. September 1965 in der jeweils
geltenden Fassung zulässig. Sie ist grundsätzlich vergütungspflichtig. Zuwiderhandlungen
unterliegen den Strafbestimmungen des Urheberrechtsgesetzes.
©2005
Zur möglichst effizienten Ausführung von SQL-Statements kann bei Oracle der Cost-BasedOptimizer eingesetzt werden. Dieser erstellt den Ausführungsplan anhand statistischer Informationen, die über die Daten vorliegen. Dabei spielt die Güte der Statistik eine große Rolle, aber auch
andere Faktoren sind zu berücksichtigen. Dieser Artikel richtet sich an Datenbank-Administratoren, die sich mit dem Thema Statistikerzeugung unter Oracle 9 und 10 beschäftigen. Es
werden Details angesprochen, die für den kostenbasierenden Optimizer von Oracle wichtig sind.
Durch die Analyse von Tabellen kann man sich Performance-Vorteile verschaffen – aber auch
zunichte machen.
Gegenüber dem regelbasierten Optimizer (RBO), der ein starres Regelwerk bei der Erstellung des
Ausführungsplans abarbeitet, fließen bei dem Cost-Based-Optimizer (CBO) auch statistische Daten
bei der Entscheidungsfindung ein. Dies ist insbesondere dann von Vorteil, wenn die Daten nicht
gleich verteilt sind. Mithilfe von Statistiken kann eine Aussage zur Selektivität der Daten getroffen
werden. Damit bietet der CBO ein wichtiges Kriterium für den Ausführungsplan. Auf dieser
Grundlage trifft er eine Vorhersage für die günstigste Zugriffsart oder die optimale Join-Order.
Als Beispiel betrachten wir eine einfache Tabelle T1 bei der in der Spalte C1 die Werte 1 bis 20
auftreten. Die absolute Häufigkeit der einzelnen Werte ist in Abbildung 1 dargestellt. Der Wert 5
kommt mit deutlichem Abstand am häufigsten vor, die restlichen Zahlen sind in etwa gleich verteilt.
Abb. 1: Absolute Häufigkeit der Werte der Beispieltabelle.
Wird für T1 eine einfache Statistik erzeugt (ANALYZE TABLE oder dbms_stats.gather_table_stats
ohne FOR-Klausel) geht Oracle zunächst von einer Gleichverteilung der Datensätze aus. Das
bedeutet, dass bei einem SELECT wie in Abbildung 2 ein Index-Zugriff stattfindet, da vermeintlich
1/20, also 5 Prozent der Gesamtdaten betroffen sind. Das zeigt auch der zugehörige Ausführungsplan in Abbildung 2. Ein Full-Table-Scan (FTS) wäre an dieser Stelle effizienter gewesen
(siehe Abbildung 3). Deutlich erkennbar ist die Diskrepanz zwischen geschätzten und realen
Kosten.
©2005
SQL> select * from t1
where c1 = 5;
13280 Zeilen ausgewahlt.
Ausfuhrungsplan
---------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=42
Card=942 Bytes=195936)
1
0 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
(Cost=42 Card=942 Bytes=195936)
2
1 INDEX (RANGE SCAN) OF 'T1_C1_IDX'
(NON-UNIQUE) (Cost=2 Card=942)
Statistiken
---------------------------------------------------0
recursive calls
0
db block gets
27071
consistent gets
0
physical reads
0
redo size
232771 bytes sent via SQL*Net to client
10390 bytes received via SQL*Net from client
887
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
13280
rows processed
Abb. 2: Ausführungsplan, nach Ermessen des Cost-Based-Optimizers.
©2005
SQL> select /*+ full(T1) erzwinge FTS */ * from t1
where c1 = 5
13280 Zeilen ausgewahlt.
Ausfuhrungsplan
----------------------------------------------------0
1
SELECT STATEMENT Optimizer=CHOOSE (Cost=106 Card=942 Bytes=195936)
0
TABLE ACCESS (FULL) OF 'T1' (Cost=106 Card=942 Bytes=195936)
Statistiken
----------------------------------------------------0
recursive calls
0
db block gets
1546 consistent gets
41
0
physical reads
redo size
232851 bytes sent via SQL*Net to client
10390 bytes received via SQL*Net from client
887
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
13280
rows processed
Abb. 3: Ausführungsplan mit Hinweis auf den Full-Table-Scan.
Histogramme zur Darstellung von “Schieflagen”
Um dem CBO Informationen über die Verteilung der Daten zur Verfügung zu stellen, müssen
erweiterte Statistiken generiert werden. Dies geschieht in Form von Histogrammen, die von
ANALYZE TABLE oder dbms_stats.gather_table_stats mit FOR-Klausel erzeugt
©2005
werden. Dabei kann die Granularität über die Anzahl der zu erzeugenden Intervalle als sizeParameter übergeben werden. Bei (großen) Tabellen mit vielen unterschiedlichen Werten wird ein
so genanntes Height-Based-Histogramm erzeugt. Die Bezeichnung kommt daher, weil die
Unterteilung der Daten so erfolgt, dass in jedem Intervall etwa gleich viele Daten liegen.
Betrachten wir dazu wieder unser Beispiel von oben. Zur Abschätzung der Zahlenverteilung sollen
zehn Intervalle benutzt werden:
((Beginn Programmcode))
SQL> exec dbms_stats.gather_table_stats('RN', 'T1', method_opt =>
'for columns c1 size 10');
((Ende Programmcode))
Um die Intervalle alle „auf eine Höhe“ zu bringen, muss – eine Ungleichverteilung vorausgesetzt –
die Intervallbreite variabel sein. Dabei kann es vorkommen, dass sich ein einzelner Wert über
mehrere Intervalle erstreckt. Anders ausgedrückt, haben dann mehrere Intervalle denselben
Endpunkt.
In der View USER_TAB_HISTOGRAMS kann man sich das Histogramm ansehen: Die Spalte
ENDPOINT_NUMBER gibt die Intervallnummer an und ENDPOINT_VALUE den Wert an der
Intervallgrenze.
((Beginn Programmcode))
SQL> select * from user_tab_histograms
where table_name = 'T1'
order by column_name, endpoint_value
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
-------------------- ----------------------- ------------------------------ -------------------------- ---------------------------------------T1 C1 0 1
T1 C1 1 5
T1 C1 2 5
T1 C1 3 5
T1 C1 4 5
T1 C1 5 5
T1 C1 6 5
T1 C1 7 5
T1 C1 8 8
T1 C1 9 15
T1 C1 10 20
((Ende Programmcode))
Abb. 4: Darstellung des Histogramms in USER_TAB_HISTOGRAMS. Die farblich markierten Zeilen 2 – 7
werden in der View nicht dargestellt und sind hier nur zum besseren Verständnis eingefügt.
Kommen Intervallgrenzen mehrfach vor, wird nur die mit der höchsten Intervallnummer
ausgegeben. Bei zehn Intervallen repräsentiert dann jedes einzelne ein zehnprozentiges Vorkommen
der Werte im Intervall.
©2005
In unserem Beispiel ist der Wert „5“ Endpunkt von sieben Intervallen, woraus auf ein Vorkommen
von etwa 70 Prozent geschlossen werden kann. Den restlichen Zahlen bleibt demnach insgesamt nur
eine Häufigkeit von 30 Prozent. Dies spiegelt die tatsächlichen Verhältnisse sehr gut wieder. Mit
Hilfe dieses Histogramms entscheidet sich der CBO nun auch ohne Hint für einen Full-Table-Scan
wenn nach der Zahl „5“ selektiert wird. In allen anderen Fällen wird weiterhin über den Index
gegangen.
Ein nicht ganz so präzises, aber in unserem Beispiel völlig ausreichendes Ergebnis hätte man auch
mit der Intervallzahl „3“ erzielen können: Die „5“ ist Endpunkt zweier Intervalle und hat damit eine
geschätzte Häufigkeit von 2/3. Hier lohnt sich ein Full-Table-Scan!
„Value Based“ – „Height Based“
Ein Sonderfall tritt auf, wenn die Intervallanzahl größer oder gleich der Anzahl der
unterschiedlichen Werte der betreffenden Spalte ist (siehe Spalte NUM_DISTINCT in
USER_TAB_COLUMNS).
Abb. 5: Value Based-Histogramm der Tabelle T1.
Dann kann jeder Wert in ein eigenes Intervall fallen und es wird ein Histogramm im klassischen
Sinne erzeugt. Im Gegensatz zum zuvor beschriebenen wird es auch als „Value Based“ bezeichnet.
In diesem Fall muss die Spalte ENDPOINT_NUMBER anders interpretiert werden, nämlich als
©2005
kumulierte Histogrammhöhe. Das heißt, in diesem Fall kann man die tatsächliche Häufigkeit eines
Wertes berechnen, indem man von seiner ENDPOINT_NUMBER die des Vorgängers abzieht.
Ab Oracle 10g ist die Art des Histogramms in der View USER_TAB_COLUMNS in der Spalte
HISTOGRAM hinterlegt. Mögliche Werte sind „none“, „frequency“ (value based) und „height
balances“ (height based). Leider ist diese Spalte in Oracle 9i noch nicht verfügbar. Trotzdem ist
eine Unterscheidung möglich. Generell kann nur bei einem Height-Based-Histogramm in der Spalte
ENDPOINT_NUMBER eine „0“ auftauchen. Bei einem Value-Based-Histogramm macht nämlich
der Wert „0“ keinen Sinn, da hier die ENDPOINT_NUMBER, wie oben beschrieben, für die Anzahl
der Häufigkeit eines Wertes steht. In jedem Intervall liegt aber genau ein Wert und damit muss die
Häufigkeit mindestens eins sein.
dbms_stats und ANALYZE arbeiten bezüglich des SIZE-Parameters unterschiedlich. Wird das
ANALYZE-Kommando mit einem SIZE-Wert aufgerufen, der gleich der Anzahl unterschiedlicher
Werte der entsprechenden Spalte ist, dann wird erwartungsgemäß ein Value-Based-Histogramm
erzeugt. Dbmbs_stats mit dem gleichen SIZE-Wert erzeugt weiterhin ein Histogramm vom Typ
Height-Based. Erst wenn eine gewisse Schwelle, die deutlich über NUM_DISTINCT liegen kann,
überschritten wird, werden auch hier Value-Based-Histogramme erzeugt. In unserem Beispiel
(NUM_DISTINCT=20) ist der Grenzwert 27!
Problemfall Bind-Variablen
Wird per SQL-Statements auf Tabellen zugegriffen, für die Histogramm-Statistiken vorhanden sind,
und die Bind-Variablen enthalten, müssen auch diese beim ersten Ausführen geparst werden. Um
den Ausführungsplan zu erzeugen, wird die Bind-Variable dabei einmalig durch ihren aktuellen
Wert ersetzt.Der Ausführungsplan ist damit zukünftig für dieses Statement festgelegt. Das
Histogramm wird später nicht mehr berücksichtigt. Diese Strategie wird als "Bind Peaking"
bezeichnet. Erwischt man nun zufällig einen Wert, der einen Full-Table-Scan nach sich zieht,
werden alle folgenden Statements ebenso per Full-Table-Scan abgearbeitet, selbst wenn aufgrund
der Statistik ein Index-Range-Scan vorteilhafter wäre.
Der Ausführungsplan ändert sich erst dann wieder, wenn die entsprechenden Informationen aus
dem Shared-Pool verdrängt werden. Das kann durch die Neuerzeugung von Statistiken, ein Leeren
des Shared-Pools oder einen Neustart der Instanz geschehen.
Der Widerspruch besteht also darin, dass bei stark ungleich verteilten Daten eine HistogrammStatistik sinnvoll ist, diese aber durch die Verwendung von Bind-Variablen zunichte gemacht wird.
Es gibt mehrere Möglichkeiten diesen Widerspruch aufzulösen:
• Über den bool'schen Parameter _optim_peek_user_binds kann man das Verhalten von
Oracle beeinflussen. Ändert man den Wert von true (standard) auf false, wird kein BindPeeking durchgeführt und der Optimizer verhält sich regelbasiert (RBO). In unserem Beispiel
bedeutet das, dass immer über den Index gegangen wird.
©2005
• Man verzichtet auf Bind-Variablen im Code, der auf solche Tabellen zugreift. Dann ist aber
auch mit einem erhöhten Parsing-Aufwand zu rechnen. Dies ist aber dann gut vertretbar, wenn
der Anteil des Parsens im Vergleich zur Laufzeit des Kommandos gering ist.
• Man verzichtet auf die Statistiken – was bei mehr oder weniger gleich verteilten Daten sowieso
zu empfehlen ist.
Fazit
Histogramme sollten mit Bedacht eingesetzt werden. Man muss sich gut überlegen, ob sich der
Einsatz wirklich lohnt und im ersten Schritt zunächst mit einfachen Statistiken arbeiten. Außerdem
kostet die Erstellung und Pflege der Statistiken natürlich auch Zeit und Ressourcen. Histogramme
bringen einen Performance-Gewinn, wenn die Daten asymmetrisch verteilt sind. Der Einsatz von
Bind-Variablen an dieser Stelle kann den gewonnenen Vorteil allerdings wieder zunichte machen.
Kontakt:
Roger Niemeyer und Michael Lindermann
[email protected]
Begriffserklärungen
Bind-Variable Häufig vorkommende Statements müssen jedes Mal geparst
werden, auch wenn sie sich nur durch Parameterwerte
unterscheiden. Bei Verwendung von Bind-Variablen muss nur
einmal geparst werden.
Bind-Peaking
Beim ersten Parsen werden Bind-Variablen durch ihren
tatsächlichen Wert ersetzt, falls Histogramme zur Verfügung
stehen.
Cost-Based-Optimizer (CBO) Der kostenbasierte Optimizer erstellt Ausführungspläne
aufgrund statistischer „Überlegungen“.
Rule-Based-Optimizer (RBO)
Ausführungspläne werden nach einem starren Regelwerk
erstellt.
Full-Table-Scan (FTS)
Zugriffsart auf Tabellen, bei der bis zur High-Water-Mark
gelesen wird.
High-Water-Mark (HWM)
Die High-Water-Mark ist der höchste Füllgrad eines
Segmentes, der dort bisher erreicht wurde.
Histogramm
Grafische Darstellung der Häufigkeitsverteilung von (Mess)Werten.
©2005