Schnellere SQL-Performance mit dbms_stats

Wenn eine SQL-Anweisung ausgeführt wird, muss die Datenbank die Abfrage in einen Ausführungsplan konvertieren und das beste Verfahren zum Auslesen der Daten bestimmen. Bei Oracle steht jeder SQL-Abfrage eine große Auswahl an Optionen für den Ausführungsplan zur Verfügung, darunter die Wahl des Indexes für das Auffinden der Tabellenzeile, die Festlegung der Reihenfolge für die Zusammenführung mehrerer Tabellen und die Bestimmung der einzusetzenden internen Zusammenschlussverfahren (Oracle bietet die Methoden Nested-Loop-Joins, Hash-Joins, Star-Joins und Sort-Merge-Joins). Diese Ausführungspläne werden von dem kostenbasierten Oracle SQL-Optimizer verarbeitet, allgemein auch bekannt als CBO.

Die vom Oracle SQL-Optimizer getroffene Auswahl hinsichtlich der Ausführungspläne kann nur so gut sein wie die Oracle-Statistiken. Um stets den besten Ausführungsplan für eine SQL-Abfrage auszuwählen, greift Oracle auf Informationen über die Tabellen und Indizes der Abfrage zurück.

Mit der Einführung des dbms_stats-Packages bietet Oracle ein einfaches Verfahren, um Statistiken für den CBO zu sammeln. Die früheren analyze-table-Befehle und dbms_utility-Metoden zur Erzeugung von CBO-Statistiken sind nun überflüssig und in gewisser Hinsicht sogar für die SQL-Performance gefährlich, da sie nicht immer hochwertige Informationen über die Tabellen und Indizes erfassen. Der CBO verwendet dagegen Objektstatistiken zur Auswahl des optimalen Ausführungsplans für alle SQL-Anweisungen.

Das dbms_stats-Utility schneidet bei der Schätzung der Statistiken erheblich besser ab, insbesondere bei umfangreichen, partitionierten Tabellen, wobei die bessere Qualität der Statistiken schnellere SQL-Ausführungspläne bedingt.

Listing A zeigt ein Beispiel für die Ausführung von dbms_stats mit der options-Klausel:

Um die Vorteile von dbms_stats vollständig beurteilen zu können, muss man alle wichtigsten Direktiven näher betrachten. Hier ein Überblick über die einzelnen Direktiven und deren Verwendung beim Sammeln hochwertiger Statistiken für den kostenbasierten SQL-Optimizer.

Der options-Parameter

Unter Nutzung einer der vier genannten Methoden bestimmt diese Option die Vorgehensweise bei der Aktualisierung der Oracle-Statistiken:

• gather – analysiert das gesamte Schema neu.
• gather empty – analysiert nur Tabellen ohne bestehende Statistiken.
• gather stale – analysiert nur Tabellen mit mehr als 10 % an Änderungen neu (Eingaben, Updates, Löschungen).
• gather auto – analysiert Objekte ohne Statistiken und Objekte mit veralteten Statistiken (die Verwendung von gather auto entspricht der Kombination von gather stale und gather empty).

Dabei ist zu bemerken, dass sowohl gather stale als auch gather auto überwacht werden müssen. Nach Eingabe des Befehls alter table xxx monitoring führt Oracle geänderte Tabellen in der Ansicht dba_tab_modifications auf, so dass man die exakte Anzahl der seit der letzten Statistikanalyse erfolgten Eingaben, Updates und Löschungen erkennen kann.

Die Option estimate percent

Das folgende estimate_percent-Argument stellt ein neues Verfahren dar, wie man Oracle dbms_stats die automatische Schätzung des optimalen Prozentanteils der für das Sammeln von Statistiken verwendeten Repräsentanten eines Segments ermöglichen kann:

Die Exaktheit der automatischen Bestimmung des Repräsentantenanteils für eine Statistik lässt sich in der Spalte dba_tables sample_size überprüfen. Hierbei ist anzumerken, dass Oracle bei der automatischen Schätzung zwischen 5 und 20 Prozent als repräsentative Basis verwendet. Wie immer gilt: Je besser die Qualität der Statistiken, desto besser die Entscheidung des CBO.