DB2 Universal Database - Systemverwaltung


Erfassen und Verwenden von Verteilungsstatistiken

Der Datenbankmanager kann zwei Arten statistischer Daten, "Häufigkeitswerte" und "Quantile", sammeln, verwalten und verwenden, die in effizienter Weise Schätzwerte über die Verteilung von Datenwerten in einer Spalte bereitstellen. Die Verwendung dieser Statistiken durch das Optimierungsprogramm kann zu bedeutend genaueren Schätzungen über die Anzahl von Zeilen in einer Spalte führen, die ein bestimmtes Gleichheits- oder Bereichsvergleichselement erfüllen. Diese genaueren Schätzwerte wiederum erhöhen die Wahrscheinlichkeit, daß das Optimierungsprogramm den optimalen Zugriffsplan auswählt.

Die statistischen Daten über die Verteilung der Datenwerte können durch die Angabe der Klausel WITH DISTRIBUTION im Befehl RUNSTATS gesammelt werden. Zwar entsteht durch die Erfassung dieser zusätzlichen Statistikdaten ein höherer Systemaufwand bei der Ausführung des Dienstprogramms RUNSTATS, jedoch kann der SQL-Compiler diese Informationen bei der Auswahl des besten Zugriffsplans zur Unterstützung heranziehen.

In einigen Fällen sammelt der Datenbankmanager keine Verteilungsdaten und gibt keinen Fehler aus. Zum Beispiel:

Die Verteilungsstatistik ist für die erste Spalte von Indizes exakt. Für jede weitere Spalte verwendet der Datenbankmanager Hash-Verfahren und Stichprobentechniken, um die Verteilungsstatistiken zu schätzen, da zur Berechnung exakter Statistiken zuviel Zeit und Speicher, als zu praktischen Zwecken geeignet sind, erforderlich wären. Diese Techniken sind gängige Methoden der Statistik, die akzeptierte Grade an Genauigkeit liefern.

Verteilungsstatistiken können durch Aktualisieren von SYSSTAT.COLDIST und Festlegen aller COLVALUE- und VALCOUNT-Werte auf 0 oder -1 für die Spalten entfernt werden, für die keine Verteilungsstatistiken mehr benötigt werden.

Die folgenden Themen enthalten Informationen, die Ihnen einen tieferen Einblick in die Verteilungsstatistiken und ihre Verwendung geben sollen:

Beschreibung der Verteilungsstatistiken

Für eine Konstante N>=1 bestehen die N häufigsten Werte in einer Spalte aus dem Datenwert, der am häufigsten vorkommt (d. h., der die meisten Duplikate hat), dem Datenwert, der am zweithäufigsten vorkommt, usw. bis zu dem Datenwert, der am N-thäufigsten vorkommt. Die entsprechende Häufigkeitsstatistik besteht aus diesen "N" Datenwerten und den zugehörigen Werten für die Anzahl der Vorkommen (Häufigkeit) in der Spalte.

Das K-Quantil für eine Spalte ist der kleinste Datenwert V, so daß mindestens "K" Zeilen Datenwerte enthalten, die kleiner oder gleich V sind. Ein K-Quantil kann berechnet werden, indem die Zeilen in der Spalte nach aufsteigenden Datenwerten sortiert werden. Das K-Quantil ist der Datenwert in der K-ten Zeile der sortierten Spalte.

Betrachten Sie zum Beispiel die folgende Spalte von Daten:

   C1
   --
    B
    E
    Y
    B
    F
    G
    E
    A
    J
    K
    E
    L

Diese Spalte kann sortiert werden, so daß sich die folgenden geordneten Werte ergeben:

   C1'
   --
    A
    B
    B
    E
    E
    E
    F
    G
    J
    K
    L
    Y

Es gibt neun unterschiedliche Datenwerte in Spalte C1. Für N = 2 ergibt sich folgende Häufigkeitsstatistik:

      SEQNO    COLVALUE     VALCOUNT
   -----    ---------    --------
     1          E            3
     2          B            2

Wenn die Anzahl der Quantile, die gesammelt werden, gleich 5 ist (siehe Anzahl der Quantile für Spalten (num_quantiles)), dann ergeben sich folgende K-Quantile für diese Spalten mit K = 1, 3, 6, 9 und 12:

   SEQNO    COLVALUE     VALCOUNT
   -----    ---------    --------
     1          A            1
     2          B            3
     3          E            6
     4          J            9
     5          Y           12

In diesem Beispiel ist das 6-Quantil gleich E, da die sechste Zeile in der sortierten Spalte einen Datenwert gleich E aufweist (und 6 Zeilen in der Originalspalte Datenwerte kleiner oder gleich E enthalten).

Derselbe Quantilwert kann mehrmals auftreten, wenn es sich um einen gängigen Wert handelt. Für einen bestimmten Wert werden maximal zwei Quantile gespeichert. Das erste dieser beiden Quantile hat einen Wert COLCOUNT, der die Anzahl der Zeilen, die einen Wert, der streng kleiner als der Wert COLVALUE ist, enthalten, und das zweite der beiden Quantile gibt die Anzahl der Zeilen an, die einen Wert kleiner oder gleich dem Wert COLVALUE enthalten.

Wann sollten Verteilungsstatistiken verwendet werden?

Bei der Entscheidung, ob Verteilungsstatistikdaten für eine bestimmte Tabelle angelegt werden sollten, spielen zwei Faktoren eine ausschlaggebende Rolle:

  1. Die Verwendung statischen oder dynamischen SQLs

    Verteilungsstatistiken sind am besten für dynamisches SQL sowie für SQL, das keine Host-Variablen verwendet, geeignet. Wenn SQL mit Host-Variablen verwendet wird, nutzt das Optimierungsprogramm die Verteilungsstatistikdaten nur in begrenztem Umfang.

  2. Ungleichmäßigkeit der Datenverteilungen

    Verteilungsstatistische Daten sind besonders dann hilfreich, wenn mindestens in einer Spalte der Tabelle die Datenwerte höchst ungleichmäßig verteilt sind und diese Spalte häufig in Gleichheits- bzw. Bereichsvergleichselementen auftritt, wie zum Beispiel in folgenden Klauseln:

       WHERE C1 = KEY;
       WHERE C1 IN (KEY1, KEY2, KEY3);
       WHERE (C1 = KEY1) OR (C1 = KEY2) OR (C1 = KEY3);
       WHERE C1 <= KEY;
       WHERE C1 BETWEEN KEY1 AND KEY2;
    

    Es gibt zwei Arten der Ungleichmäßigkeit bei einer Datenverteilung, die auch nebeneinander auftreten können:

Verteilungsstatistikdaten können mit Hilfe der Klausel WITH DISTRIBUTION im Befehl RUNSTATS oder beim Aufruf der API RUNSTATS durch die Angabe der Werte D, E oder A für den Parameter statsopt gesammelt werden. Weitere Informationen zur Anwendungsprogrammierschnittstelle (Application Programming Interface, API) finden Sie im Handbuch Administrative API Reference.

Wie viele Statistikdaten sollten erfaßt werden?

Wenn eine große Anzahl statistischer Daten über die Werteverteilung in Spalten vorliegt, kann einerseits das Optimierungsprogramm einen besseren Zugriffsplan auswählen, andererseits wächst der Systemaufwand für das Sammeln dieser Statistikdaten und für die Kompilierung der Abfragen entsprechend. Die Größe des Zwischenspeichers für Statistik (siehe Größe des Statistikzwischenspeichers (stat_heap_sz)) kann der Anzahl der statistischen Daten, die berechnet und gespeichert werden können, Grenzen setzen.

Wenn Verteilungsstatistikdaten angefordert werden, speichert der Datenbankmanager standardmäßig die 10 häufigsten Werte für eine Spalte. Eine Anzahl zwischen 10 und 100 häufigsten Werten sollte für die Mehrzahl der praktischen Anwendungen genügen. Im Idealfall sollten ausreichend Häufigkeitsstatistikdaten gespeichert werden, so daß die Häufigkeiten der verbleibenden Werte entweder einander annähernd gleich sind oder im Vergleich zu den Häufigkeiten der häufigsten Werte vernachlässigbar sind.

Zur Festlegung der Anzahl der zu sammelnden Häufigkeitswerte wird der Konfigurationsparameter num_freqvalues verwendet, der im Abschnitt Anzahl der häufigsten Werte (num_freqvalues) beschrieben ist. Der Datenbankmanager sammelt unter Umständen weniger als diese Anzahl von Statistikdaten über die Häufigkeit, da diese Statistikdaten nur für Datenwerte erhoben werden, die mehr als einmal auftreten. Wenn nur Quantilstatistikdaten gesammelt werden, kann dieser Parameter auf den Wert 0 gesetzt werden.

Wenn Verteilungsstatistikdaten angefordert werden, speichert der Datenbankmanager standardmäßig 20 Quantile für eine Spalte. Dieser Wert garantiert einen maximalen Schätzfehler von ungefähr 2,5% für alle einfachen einseitigen Bereichsvergleichselemente (>, >=, < oder <=), und einen maximalen Fehler von 5% für jedes Vergleichselement mit BETWEEN. Als grobe Faustregel zur Bestimmung der Anzahl von Quantilen gilt:

Zum Beispiel ergeben 25 Quantile einen maximalen Schätzfehler von 4% bei BETWEEN-Vergleichselementen und 2% bei Vergleichselementen mit ">". Im allgemeinen sollten mindestens 10 Quantile gespeichert werden. Mehr als 50 Quantile sind nur bei extrem ungleichmäßig verteilten Daten erforderlich.

Zur Festlegung der Anzahl der Quantile wird der Konfigurationsparameter num_quantiles verwendet, der im Abschnitt Anzahl der Quantile für Spalten (num_quantiles) beschrieben ist. Wenn nur Statistikdaten über die Häufigkeit von Werten gesammelt werden, kann dieser Parameter auf den Wert 0 gesetzt werden. Wird dieser Parameter auf den Wert "1" gesetzt, werden ebenfalls keine Quantilstatistikdaten gesammelt, da der gesamte Bereich von Werten in einem Quantil erfaßt würde.

Wie verwendet das Optimierungsprogramm die Verteilungsstatistiken?

Wozu werden statistische Daten zur Werteverteilung gesammelt und gespeichert? Die Antwort liegt in der Tatsache begründet, daß ein Optimierungsprogramm die Anzahl von Zeilen in einer Spalte schätzen muß, die ein Gleichheitsvergleichselement oder ein Bereichsvergleichselement erfüllen, um den Zugriffsplan wählen zu können, der den geringsten Systemaufwand verursacht. Je genauer die Schätzung ist, desto größer ist auch die Wahrscheinlichkeit, daß das Optimierungsprogramm den optimalen Zugriffsplan wählt. Betrachten Sie zum Beispiel die folgende Abfrage:

SELECT C1, C2
    FROM TABLE1
    WHERE C1 = 'NEW YORK'
    AND C2 <= 10

Nehmen Sie an, daß es einen Index für C1 und einen Index für C2 gibt. Ein möglicher Zugriffsplan besteht darin, über den Index für C1 alle Zeilen mit C1 = 'NEW YORK' abzurufen und anschließend jede abgerufene Zeile daraufhin zu überprüfen, ob C2 <= 10 gilt. Ein alternativer Plan wäre, über den Index für C2 alle Zeilen mit C2 <= 10 abzurufen und anschließend jede abgerufene Zeile daraufhin zu überprüfen, ob C1 = 'NEW YORK' gilt. In der Regel entsteht der Hauptaufwand bei der Ausführung der obigen Abfrage durch das Abrufen der Zeilen, so daß es wünschenswert ist, den Plan auszuwählen, der die geringste Anzahl von Zeilenabrufen erforderlich macht. Zur Auswahl des besten Plans ist es also nötig, die Anzahl der Zeilen, die jedes Vergleichselement erfüllen, im voraus abzuschätzen.

Wenn Verteilungsstatistikdaten nicht angefordert werden, arbeitet das Optimierungsprogramm nur mit dem zweithöchsten Datenwert (HIGH2KEY), dem zweitniedrigsten Datenwert (LOW2KEY), der Anzahl unterschiedlicher Werte (COLCARD) und der Anzahl der Zeilen für eine Spalte. Die Anzahl der Zeilen, die ein Gleichheitsvergleichselement oder ein Bereichsvergleichselement erfüllen, wird dann unter der Annahme abgeschätzt, daß die Häufigkeiten der Datenwerte in einer Spalte alle gleich und die Datenwerte gleichmäßig über das Intervall (LOW2KEY, HIGH2KEY) verteilt sind. Im einzelnen wird die Anzahl der Zeilen, die ein Gleichheitsvergleichselement C1 = KEY erfüllen, mit dem Wert CARD/COLCARD abgeschätzt. Die Anzahl der Zeilen, die ein Bereichsvergleichselement C1 BETWEEN KEY1 AND KEY2 erfüllen, wird nach folgender Formel abgeschätzt:

       KEY2 - KEY1
   -------------------  x CARD       (1)
    HIGH2KEY - LOW2KEY

Diese Schätzwerte sind nur dann realistisch, wenn die tatsächliche Verteilung der Datenwerte weitgehend gleichmäßig ist. Wenn keine Verteilungsstatistikdaten verfügbar sind und entweder die Häufigkeiten der Datenwerte grob von einander abweichen oder die Datenwerte in einigen wenigen Unterbereichen des Intervalls (LOW_KEY, HIGH_KEY) Cluster bilden, können die Schätzwerte um Größenordnungen von der Realität abweichen, so daß das Optimierungsprogramm möglicherweise einen nicht optimalen Zugriffsplan wählt.

Wenn Verteilungsstatistikdaten verfügbar sind, können die oben beschriebenen Schätzfehler wesentlich verringert werden, indem die statistischen Daten zur Häufigkeit der Werte zur Berechnung der Anzahl von Zeilen, die ein Gleichheitsvergleichselement erfüllen, und sowohl die statistischen Daten zur Häufigkeit der Werte als auch die Quantilstatistik zur Berechnung der Anzahl von Zeilen, die ein Bereichsvergleichselement erfüllen, herangezogen werden.

Beispiel zur Auswirkung auf Gleichheitsvergleichselemente:

Betrachten Sie zunächst ein Vergleichselement der Form C1 = KEY. Wenn KEY einer der N häufigsten Werte ist, dann verwendet das Optimierungsprogramm einfach die Häufigkeit von KEY, die im Katalog gespeichert ist. Wenn KEY nicht einer der N häufigsten Werte ist, schätzt das Optimierungsprogramm die Anzahl der Zeilen, die das Vergleichselement erfüllen, unter der Annahme ab, daß die nicht häufigen Werte (COLCARD - N) eine gleichmäßige Verteilung aufweisen. Das heißt, die Anzahl der Zeilen wird folgendermaßen abgeschätzt:

   CARD - NUM_FREQ_ROWS
   --------------------           (2)
      COLCARD - N

Hier steht NUM_FREQ_ROWS für die Gesamtzahl der Zeilen mit einem Wert, der gleich einem der N häufigsten Werte ist.

Betrachten Sie zum Beispiel eine Spalte (C), in der sich die Häufigkeit der Datenwerte folgendermaßen darstellt:

   Datenwert  Häufigkeit
   ----------   ---------
       1            2
       2            3
       3           40
       4            4
       5            1

Angenommen, es stehen die statistischen Häufigkeitsdaten nur für den häufigsten Wert (d. h. N = 1) zur Verfügung. Für diese Spalte gilt CARD = 50 und COLCARD = 5. Das Vergleichselement C = 3 wird exakt von 40 Zeilen erfüllt. Unter der Annahme einer gleichmäßigen Datenverteilung wird die Anzahl der Zeilen, die das Vergleichselement erfüllen, als 50/5 = 10 geschätzt, was einen Schätzfehler von -75% mit sich bringt. Bei Verwendung der Häufigkeitsstatistik kann die Anzahl der Zeilen auf 40 geschätzt werden, d. h., es entsteht in diesem Fall kein Fehler.

Analog erfüllen 2 Zeilen das Vergleichselement C = 1. Ohne die Häufigkeitsstatistik wird die Anzahl der Zeilen, die das Vergleichselement erfüllen, auf 10 geschätzt; mithin entsteht ein Fehler von 400%. Die folgende Formel kann zur Berechnung des Schätzfehlers (in Prozent) herangezogen werden:

   geschätzte Zeilen - tatsächliche Zeilen
   ---------------------------------------  X 100
             tatsächliche Zeilen

Bei Verwendung der Häufigkeitsstatistik (N = 1) schätzt das Optimierungsprogramm die Anzahl der Zeilen, die diesen Wert enthalten, anhand der oben gezeigten Formel (2). Zum Beispiel:

   (50 - 40)
   --------- = 3
    (5 - 1)

Der Fehler wird dabei um eine Größenordnung verringert, wie im folgenden gezeigt wird

    3 - 2
   ------- = 50%
      2

Die Anzahl der Zeilen, die ein Bereichsvergleichselement erfüllen, kann mit Hilfe von Quantilen, wie in folgenden Beispielen illustriert, abgeschätzt werden. Betrachten Sie eine Spalte (C), die folgende Werte enthält:

     C
  -------
     0,0
     5,1
     6,3
     7,1
     8,2
     8,4
     8,5
     9,1
    93,6
   100,0

Angenommen, es stehen K-Quantile zur Verfügung für K = 1, 4, 7 und 10:

    K     K-Quantil
   ---  ----------
    1      0,0
    4      7,1
    7      8,5
   10    100,0

Betrachten Sie zunächst das Vergleichselement C <= 8.5. Für die oben angegebenen Daten erfüllen exakt sieben Zeilen dieses Vergleichselement. Unter der Annahme einer gleichmäßigen Datenverteilung und unter Verwendung der oben angegeben Formel (1), wobei KEY1 durch LOW2KEY ersetzt wird, wird die Anzahl der Zeilen, die das Vergleichselement erfüllen, folgendermaßen abgeschätzt:

    8,5 - 5,1
   ---------- x 10 *= 0
   93,6 - 5,1

Die Notation *= bedeutet "annähernd gleich". Der Fehler bei dieser Schätzung ist annähernd -100%.

Bei Verwendung von Quantilen wird die Anzahl der Zeilen, die dasselbe Vergleichselement (C <= 8.5) erfüllen, abgeschätzt, indem 8,5 als einer der K-Quantilwerte aufgesucht und der entsprechende Wert von K, also 7, als Schätzwert verwendet wird. In diesem Fall wird der Fehler auf 0 reduziert.

Betrachten Sie nun das Vergleichselement C <= 10. Dieses Vergleichselement wird von exakt acht Zeilen erfüllt. Anders als im vorigen Beispiel ist der Wert 10 keiner der gespeicherten K-Quantile. Unter der Annahme einer gleichmäßigen Datenverteilung und unter Verwendung der Formel (1) wird die Anzahl der Zeilen, die das Vergleichselement erfüllen, auf 1 geschätzt, d. h., es entsteht ein Fehler von -86%.

Bei Verwendung von Quantilen schätzt das Optimierungsprogramm die Anzahl der Zeilen, die das Vergleichselement erfüllen, als r_1 + r_2 ab, wobei r_1 die Anzahl der Zeilen ist, die das Vergleichselement C <= 8.5 erfüllen, und r_2 die Anzahl der Zeilen ist, die das Vergleichselement C > 8.5 AND C <= 10.0 erfüllen. Wie im vorigen Beispiel gilt r_1 = 7. Zur Abschätzung von r_2 verwendet das Optimierungsprogramm die lineare Interpolation:

                    100,0 - 10,0
   r_2 *= ------------ x (Anzahl Zeilen mit Wert > 8,5 und <= 100,0)
          100,0 - 8,5
 
          100,0 - 10,0
        = ------------ x (10 - 7)
          100,0 - 8,5
 
       *= 3

Als endgültiger Schätzwert wird r_1 + r_2 *= 10 angenommen. Dadurch verringert sich der Fehler um mehr als den Faktor 3.

Der Grund dafür, daß die Verwendung von Quantilen die Genauigkeit der Schätzungen in den obigen Beispielen erhöht, liegt darin, daß die realen Datenwerte "Cluster" im Bereich von 5 bis 10 bilden, aber die Standardformeln zur Schätzung von einer gleichmäßigen Verteilung der Werte zwischen 0 und 100 ausgehen.

Die Verwendung von Quantilen erhöht auch die Genauigkeit, wenn es wesentliche Unterschiede in den Häufigkeiten verschiedener Datenwerte gibt. Betrachten Sie eine Spalte, die Datenwerte mit den folgenden Häufigkeiten enthält:

   Datenwert  Häufigkeit
   ----------   ---------
      20            5
      30            5
      40           15
      50           50
      60           15
      70            5
      80            5

Angenommen, es stehen K-Quantile zur Verfügung für K = 5, 25, 75, 95 und 100:

    K     K-Quantil
   ----   ----------
     5       20
    25       40
    75       50
    95       70
   100       80

Nehmen Sie außerdem an, daß statistische Häufigkeitsdaten für die 3 häufigsten Werte verfügbar sind.

Betrachten Sie das Vergleichselement C BETWEEN 20 AND 30. An der Verteilung der Datenwerte können Sie sehen, daß genau 10 Zeilen das Vergleichselement erfüllen. Unter der Annahme einer gleichmäßigen Datenverteilungen und unter Verwendung der Formel (1), wird die Anzahl der Zeilen, die das Vergleichselement erfüllen, wie folgt abgeschätzt:

        30 - 20
   -------  x 100 = 25
   70 - 30

Diese Abschätzung enthält einen Fehler von 150%.

Unter Verwendung der Häufigkeitsstatistik und der Quantile wird die Anzahl der Zeilen, die das Vergleichselement erfüllen, als r_1 + r_2 abgeschätzt, wobei r_1 die Anzahl der Zeilen ist, die das Vergleichselement (C = 20) erfüllen, und r_2 die Anzahl der Zeilen ist, die das Vergleichselement C > 20 AND C <= 30 erfüllen. Bei Verwendung der Formel (2) wird r_1 folgendermaßen abgeschätzt:

   100 - 80
   -------- = 5
     7 - 3

r_2 wird mit linearer Interpolation folgendermaßen abgeschätzt:

          30 - 20
     ------- x (Anzahl Zeilen mit Wert > 20 und <= 40)
     40 - 20
     30 - 20
   = ------- x (25 - 5)
     40 - 20
   = 10,

Als endgültiger Schätzwert ergibt sich 15, wodurch der Schätzfehler um den Faktor 3 verringert wird.


[ Seitenanfang | Vorherige Seite | Nächste Seite | Inhaltsverzeichnis | Index ]