Statistiken (Oracle)

Die Optimizer-Statistiken sind eine Sammlung von Daten, die weitere Details bzw. Informationen über die Datenbank und die Objekte der Datenbank beschreiben. Damit der Cost-Based-Optimizer (CBO) den effizientesten Ausführungsplan von einer SQL-Abfrage berechnen und auswählen kann, müssen Informationen über die Tabelle? und Indizes, die in der SQL-Abfrage beteiligt sind, vorhanden sein. Der Rule-Based-Optimizer (RBO) verwendet dagegen keine Statistiken.

Es ist wichtig, dass Statistiken immer für alle Tabellen, Spalten und alle Indizes eines Schemas erstellt werden. Man kann in den Data-Dictionary-Views (mit USER, ALL oder DBA) überprüfen, ob Statistiken schon vorhanden sind. Die entsprechenden Data-Dictionary-Views können einfach mit einer SELECT-Anweisung abgefragt werden.

Zu den Data-Dictionary-Views für DBA gehören:

  • DBA_TABLES
  • DBA_OBJECT_TABLES
  • DBA_TAB_STATISTICS
  • DBA_TAB_COL_STATISTICS
  • DBA_TAB_HISTOGRAMS
  • DBA_TAB_COLS
  • DBA_COL_GROUP_COLUMNS
  • DBA_INDEXES
  • DBA_IND_STATISTICS
  • DBA_CLUSTERS
  • DBA_TAB_PARTITIONS
  • DBA_TAB_SUBPARTITIONS
  • DBA_IND_PARTITIONS
  • DBA_IND_SUBPARTITIONS
  • DBA_PART_COL_STATISTICS
  • DBA_PART_HISTOGRAMS
  • DBA_SUBPART_COL_STATISTICS
  • DBA_SUBPART_HISTOGRAMS

In den Views, wie DBA_TABLES, ALL_TABLES und USER_TABLES, gibt es die Spalten NUM_ROWS und LAST_ANALYZED. Wenn dort keine Werte zu sehen sind, dann fehlen die Statistiken. In diesem Fall wird Oracle zwar auch einen Ausführungsplan erzeugen, aber er wird nicht optimal sein. Aus diesem Grund soll man die Statistiken erzeugen, bevor der kostenbasierte Optimizer verwendet wird.

Zu den Statistiken gehören die Anzahl und die Verteilung der Daten in der Datenbank, sowie die Größen der beteiligten Tabellen und die Selektivität der vorhandenen Indizes. Dazu kommen auch Systemdaten, wie I/O-Laufzeiten, Größe von Hautspeicher und CPU-Geschwindigkeit.

Man kann die Statistiken in vier Bereiche aufteilen:

1. Statistiken von Tabellen

  • Anzahl von Zeilen
  • Anzahl von Blocks
  • Durchschnittliche Zeilenlänge

2. Statistiken von Spalten

  • Anzahl von unterschiedlichen Werten (NDV) pro Spalte
  • Anzahl von NULLs pro Spalte
  • Datenverteilung (Histogramm)
  • Erweiterte Statistiken

3. Statistiken von Indizes

  • Anzahl von Leaf-Blocks
  • Levels
  • Clustering Factor

4. Statistiken von System

  • I/O-Performance und -Nutzung
  • CPU-Performance und -Nutzung

Diese Informationen werden mit den Prozeduren von dem DBMS_STATS-Package gesammelt und im DATA DICTIONARY gespeichert. Die Prozeduren von dem DBMS_STATS-Package enthalten verschiedene Parameter.

In dem folgenden Beispiel werden die Parameter der Prozedur GATHER_SCHEMA_STATS gezeigt:

DBMS_STATS.GATHER_SCHEMA_STATS (

	ownname,
	estimate_percent, 
	block_sample , 
	method_opt,degree,
	granularity,
	cascade,
	stattab, 
	statid,
	options,
	statown,
	no_invalidate, 
	gather_temp,
	gather_fixed

);

Der automatischen Sammlung von Statistiken soll für die meisten Datenbank-Objekte, die sich mit mäßiger Geschwindigkeit verändert, ausreichen. Es gibt aber auch einige Fälle von Objekten, die ihre Größe um 10% oder mehr zunehmen oder auch von Tabellen?, die gelöscht oder abgeschnitten (truncate) werden und dann in einem späteren Zeitpunkt wiederaufgebaut werden. In solchen Fälle müssen die Statistiken öfter gesammelt werden. Deswegen ist es weiterhin möglich, die Statistiken durch das DBMS_STATS-Package manuell zu sammeln und zu verwalten. Somit können auch weitere wichtige Aufgaben zur Verwaltung von Statistiken erledigt werden.

Das DBMS_STATS-Package enthält folgende Funktionalitäten:

  • Statistiken sammeln
  • Statistiken importieren und exportieren
  • Statistiken durch andere Statistiken ersetzen, um unterschiedliche Workloads zu simulieren
  • Statistiken löschen
  • Statistiken sperren
  • Statistiken als privat deklarieren
  • Statistikberechnung auf Gruppen oder Ausdrücke von Spalten erweitern
  • Unterschiede der Statistiken von verschiedenen Zeitpunkten überwachen
  • Auf Statistiken eines älteren Zeitpunkts zurücksetzen

Die Prozeduren des DBMS_STATS-Packages können Statistiken über die Datenbank, das DATA DICTIONARY, feste Objekte, Indizes, das ganze Datenbankschema, das System und einzelne Tabellen sammeln.

Wegen der ständigen Änderungen in einer Datenbank ist es sehr wichtig, dass die Statistiken immer wieder aktualisiert werden, damit der Cost-Based-Optimizer den kostengünstigen Ausführungsplan berechnen und auswählen kann. Ab Oracle 10g können die Statistiken durch die Prozedur GATHER_DATABASE_STATS_JOB_PROC vom DBMS_STATS-Package automatisch gesammelt und aktualisiert werden. Diese Prozedur ist eine interne Prozedur und muss nicht manuell gestartet werden. Sie sammelt Statistiken von Datenbank-Objekten, für die zuvor noch keine Statistiken gesammelt wurden oder deren Statistiken veraltet sind. Veraltete Statistiken gehören zu Objekten, bei denen mehr als 10% der Zeilen modifiziert wurden und noch keine Aktualisierung der Statistiken durchgeführt wurde. Die Prozedur GATHER_DATABASE_STATS sammelt Statistiken der Datenbank. Die Option GATHER AUTO hat eine ähnliche Funktionalität. Die GATHER_DATABASE_STATS_JOB_PROC-Prozedur hat aber den Vorteil, dass dieses Verfahren die Statistiken von Objekten zuerst verarbeitet, die am meisten verwendet werden. Dadurch wird sichergestellt, dass die am dringensten benötigten Statistiken zuerst gesammelt bzw. aktualisiert werden.

Die automatische Sammlung von Statistiken stützt sich auf dem Modification-Monitoring-Feature. Wenn dieses Feature deaktiviert ist, dann wird der Job der automatischen Sammlung nicht in der Lage sein, veraltete Statistiken zu erkennen. Diese Feature wird aktiviert, wenn der Parameter STATISTICS_LEVEL auf TYPICAL oder ALL eingestellt ist.

Statistiken können auch über den Befehl "ANALYZE TABLE tabellenname COMPUTE STATISTICS;" gesammelt werden. Das Kommando "ANALYZE" wird aber nicht weiterentwickelt und soll aus diesem Grund nicht mehr verwendet werden.

Eine andere Form von Statistiken sind die Histogramme. Die Spaltenstatistiken können als Histogramme gespeichert werden. Sie sollen ebenfalls erzeugt werden, weil Histogramme die genaue Schätzung der Verteilung einzelner Spaltendaten angeben.

Die grundlegende Information über die Verteilung der Spaltendaten, die durch die einfache Sammlung von Statistiken mit dem DBMS_STATS-Package erzeugt werden, ist der Maximal- und Minimalwert der Spalten. Das kann für die Bedürfnisse des Optimizers unzureichend sein. Histogramme liefern dem Optimizer verbesserte Werte über die Selektivität einzelner Spalten mit Datenabweichungen, die bei der Erstellung des optimalen Ausführungsplans mit ungleichmäßiger Datenverteilung sehr relevant sind. Aus diesem Grund sind Histogramme ein wichtiges Entscheidungskriterium, um den richtigen Index zu verwenden.

Histogramme können durch den Befehl "ANALYZE TABLE tabellenname COMPUTE STATISTICS FOR ALL COLUMNS; " erstellt werden, aber weil der Befehl "ANALYZE" von Oracle nicht weiterentwickelt wird, ist das nicht zu empfehlen. Das DBMS_STATS-Package bietet auch hier die Möglichkeit an, Histogramme zu erzeugen.

Siehe auch: Performance-Tuning, SQL-Tuning?, Optimizer, Ausführungsplan einer SQL-Abfrage, Ausführungsplanoperationen?, Hints, Werkzeuge zum SQL-Tuning

Quellen:

Kategorie: Tuning, S