Schnellere SQL-Performance mit dbms_stats

Der method_opt-Parameter für dbms_stats ist für die Aktualisierung von Statistiken bei Änderungen der Tabellen- und Indexdaten sehr hilfreich. Darüber hinaus lässt sich mit ihm feststellen, für welche Spalte Histogramme erforderlich sind.

In manchen Fällen beeinflusst die Werteverteilung innerhalb eines Index die Entscheidung des CBO, ob ein Index-Scan oder ein Full Table-Scan durchzuführen ist. Dies geschieht, wenn eine where-Klausel einen nicht proportionalen Wertebetrag angibt, so dass ein Full Table-Scan günstiger als der Index-Zugriff wäre.

In Oracle können Histogramm-Statistiken erstellt werden, wenn ein stark verzerrter Index vorliegt, in dem bestimmte Werte eine nicht proportionale Zeilenanzahl aufweisen. In der Praxis kommt dies sehr selten vor, wobei häufig der Fehler gemacht wird, den CBO-Statistiken unnötigerweise Histogramme hinzuzufügen. Im Allgemeinen werden Histogramme eingesetzt, wenn die Werte einer Spalte eine Änderung im Ausführungsplan zulassen.

Zur Unterstützung einer sinnvollen Generierung von Histogrammen verwendet Oracle den method_opt-Parameter von dbms_stats. Auch innerhalb der method_opt-Klausel gibt es wichtige neue Optionen – skewonly, repeat und auto:

Die Option skewonly ist sehr zeitaufwendig, da sie die Verteilung der Werte in jeder Spalte in sämtlichen Indexen untersucht.

Wenn dbms_stats einen Index mit ungleichmäßiger Spaltenverteilung findet, werden Histogramme für den betreffenden Index erstellt um den kostenbasierten SQL-Optimizer bei der Entscheidung zwischen Index-Scan und Full Table-Scan zu unterstützen. Sollte ein Index beispielsweise eine Spalte aufweisen, die in 50 Prozent der Zeilen vorkommt, wie in Listing B dargestellt, würde ein vollständiger Full Table-Scan die entsprechenden Zeilen schneller auffinden als ein Index-Scan.

Bei einer erneuten Analyse von Statistiken werden unter Verwendung der Option repeat erheblich weniger Ressourcen belegt. Die repeat-Option (Listing C) analysiert lediglich Indizes mit bestehenden Histogrammen neu, wobei keine weiteren Histogramme erzeugt werden. Diese Vorgehensweise empfiehlt sich für die regelmäßige Analyse von Statistiken.

Die Option auto kommt in dbms_stats zum Einsatz, wenn über den Befehl alter table xxx monitoring die Überwachung der Tabellen in Oracle aktiviert wird. Die in Listing D gezeigte auto-Option erstellt Histogramme auf Grundlage der Datenverteilung und der Art des Zugriffs der Anwendung auf die Spalten (z. B. die Auslastung der Spalte wie in der Überwachung festgestellt). Die Verwendung von method_opt=>’auto‘ ähnelt dem Einsatz von gather auto im option-Parameter von dbms_stats.

Parallele Sammlung

Oracle ermöglicht das parallele Sammeln von CBO-Statistiken, wodurch sich der Zeitaufwand für die Statistikerstellung erheblich verringert. Für die parallele Statistiksammlung ist ein SMP-Server mit mehreren CPUs erforderlich.

Verbesserte Ausführungsgeschwindigkeit

Das dbms_stats-Utility stellt eine hervorragende Möglichkeit zur Steigerung der SQL-Ausführungsgeschwindigkeit dar. Durch den Einsatz von dbms_stats für das Sammeln hochwertiger Statistiken trifft der CBO in der Regel eine sinnvolle Entscheidung hinsichtlich der schnellsten Vorgehensweise zur Ausführung von SQL-Abfragen. Das dbms_stats-Utility wird immer noch weiter verbessert und die praktischen neuen Funktionen der automatischen Bestimmung der repräsentativen Basis sowie der automatischen Generierung von Histogrammen erleichtern die Arbeit mit Oracle erheblich.