Die Erstellung von Statistiken über Datenbank-Objekte wie Tabellen oder Indizes ist seit der Einführung des kostenbasierenden Optimizers ein absolutes Muss. Die Qualität der Statistiken entscheidet darüber, ob ein SQL-Statement performant in der Datenbank verarbeitet werden kann oder nicht. Mit Hilfe der Statistiken bestimmt die Datenbank, welche Kosten im Sinne von Ressourcen-Nutzung entstehen und welcher Zugriffs-Pfad zu den geringsten Kosten führt.
Der vorliegende Monatstipp hat nicht das Ziel einer detaillierten Beschreibung des kostenbasierenden Optimizers. Dazu wird auf unsere Muniqsoft-Training-Schulungen und auf die einschlägige Literatur verwiesen.
Vielmehr werden folgende Fragestellungen näher betrachtet:
- Wie sollen Statistiken erstellt werden?
- Wann sollten Statistiken erstellt werden?
- Welche Arten von Statistiken sollten erstellt werden?
WIE SOLLEN STATISTIKEN ERSTELLT WERDEN ?
AUTOMATISCH
Seit Einführung der RDBMS-Version 10g existiert ein automatischer Statistik-Erstell-Job, der für alle Tabellen, die entweder keine oder veraltete (Stale) Statistiken aufweisen, Statistiken erstellt. Die verwendete Prozedur heißt „DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC“ und verwendet die gleichen Default-Parameter wie die „DBMS_STATS.GATHER_*_STATS“-Prozedur. Per Default werden die Statistiken aller Tabellen, deren Datensätze sich zu 10% geändert haben, als veraltet (stale) betrachtet und somit neu erstellt. Dieser Wert ist bis Oracle Version 10.2 fest und kann nicht geändert werden. Wenn die Notwendigkeit besteht, Statistiken für eine Tabelle öfters erstellen zu lassen, gibt es ab Version 11.1 die Prozedur „DBMS_STATS.SET_TABLE_PREFS“.
Z.B. kann die Tabelle „SCHULUNG“ des Users „MQS“ bereits bei 5% Änderungen folgendermaßen als veraltet deklariert werden und somit die Erstellung von Statistiken erzwungen werden:
SQL>
BEGIN
DBMS_STATS.SET_TABLE_PREFS('MQS','SCHULUNG','STALE_PERCENT',5);
END;
/
MANUELL
Wenn das automatische Erstellen von Statistiken nicht gewünscht wird, weil z.B. eine andere Prozedur dies bereits erledigt, kann der Automatismus folgendermaßen nur auf Erstellung der Data Dictionary-Tabellen eingeschränkt werden:
SQL>
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE');
END;
/
Für die manuelle Erstellung von Statistiken ist grundsätzlich das PL/SQL-Package DBMS_STATS und nicht mehr das ANALYZE-Kommando zu verwenden.
Bei der DBMS_STATS-Prozedur sollen die beiden Parameter ESTIMATE_PERCENT und METHOD_OPT näher betrachtet werden:
ESTIMATE_PERCENT gibt den prozentualen Wert an, wieviele Datensätze einer Tabelle als Basis für die Statistikerstellung herangezogen werden. Je höher der Wert, desto genauer die Statistik, aber desto höher auch die Ausführungszeit der Statistikerstellung. Ab 10g hat sich der Default-Wert für estimate_percent von 100% auf AUTO_SAMPLE_SIZE geändert. Hierbei entscheidet die Datenbank selbst über das Verhältnis von untersuchten Datensätzen zur Erzielung brauchbarer Statistiken und der möglichst geringen Systembelastung bedingt durch deren Erstellung. Leider hat sich bei 10g gezeigt, dass die Anzahl der untersuchten Datensätze zu gering war und deswegen ein absoluter Wert für ESTIMATE_PERCENT sinnvoller war.
Mit Version 11g hat Oracle den Algorithmus für AUTO_SAMPLE_SIZE geändert. Der Effekt ist, dass 100% der Datensätze untersucht werden, aber nur eine Systembelastung wie bei einem Sample von ca. 10% zu verzeichnen ist.
Die Empfehlung ist daher bei der Statistik-Erstellung von Tabellen einer 11g-Datenbank für ESTIMATE_PERCENT den AUTO_SAMPLE_SIZE Parameter zu verwenden.
SQL>
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('MQS','SCHULUNG',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
METHOD_OPT
Mit dem Parameter METHOD_OPT kann definiert werden, ob sogenannte Histogramme für Tabellenspalten erzeugt werden. Histogramme teilen dem Oracle Optimizer mit, wie die Werte in den Spalten verteilt sind – z. B. wie oft der Wert „Müller“ in der Spalte „KD_NAME“ enthalten ist. Je öfter der Wert vorhanden ist, desto eher kann sich der Oracle Optimizer dazu entschließen, anstatt einem Direkt-Zugriff über Index alle Datensätze der Tabelle mit einem Full Table Scan zu lesen.
Bei Datenbanken der Version 10g trat bei Verwendung von Histogrammen der sogenannte „Bind-Peeking“-Effekt ein, bei dem Oracle auf Grund des ersten Wertes einer Spalte in der „where“-Klausel einen Ausführungsplan gewählt hat (z.B. ein Full Table Scan, weil der Wert sehr häufig vorkam) und bei der nächsten Ausführung trotz eines Wertes mit geringerer Selektivität (Wert kommt seltener vor) den Ausführungsplan nicht geändert hat.
Um diesen Effekt bei Version 10g zu verhindern gibt es zwei Möglichkeiten:
- Löschen der Histogramme und Erstellen der Statistiken ohne Histogramme
- Ausschalten von Bind Peeking
Wenn in der Applikation aussschließlich Bind-Variablen verwendet werden, sollten die Histogramme gelöscht werden:
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('MQS','SCHULUNG');
END;
/
Nach Ausführen der folgenden Prozedur erstellen sowohl der automatische Statistik-Erstell-Job als auch die DBMS_STATS.GATHER_*_STATS-Prozeduren keine Histogramme mehr.
BEGIN
DBMS_STATS.SET_PARAM(PNAME => 'METHOD_OPT',
PVAL => 'FOR ALL COLUMNS SIZE 1');
END;
/
Wenn die Applikation auch Literale verwendet, ist das Ausschalten des Bind-Peekings die bessere Methode.
SQL> alter system set "_OPTIM_PEEK_USER_BINDS"=false;
Bitte vor Setzen des sogenanten Underscore-Parameters in MyOracleSupport über mögliche Seiteneffekte informieren oder beim Oracle Support nachfragen.
Mit Version 11g ist das sogenannte „Adaptive Cursor Sharing“ zur Verhinderung des Bind-Peekings eingeführt worden. Vereinfacht gesprochen können bei diesem Konzept mehrere Ausführungspläne für ein SQL-Statement bei Vorlage unterschiedlicher Verteilungen in den Bind-Variablen erstellt werden.
Die Empfehlung bei 11g für den Parameter METHOD_OPT ist der Default-Wert mit Erstellung von Histogrammen.
PENDING STATISTICS (Ab Oracle 11.1)
Eine weitere Option, die beim Erstellen von Statistiken beachtet werden kann, sind „Pending Statistics“. Wenn man vom Default abweichende Angaben bei der Erstellung von Statistiken gemacht hat, können diese innerhalb der eigenen Session ohne Beeinflussung des Gesamtsystems als „nicht zu veröffentlichen“ deklariert werden.
BEGIN
DBMS_STATS.SET_TABLE_PREFS ('MQS','SCHULUNG','PUBLISH','FALSE');
END;
/
Mit Hilfe des Session-Parameters “OPTIMIZER_USE_PENDING_STATISTICS“ = TRUE werden die erstellten Statistiken innerhalb der Session verwendet. Wenn die Statistiken akzeptiert werden sollen, können sie „veröffentlicht“ werden und gelten damit für das Gesamtsystem.
BEGIN
DBMS_STATS.PUBLISH_PENDING_STATS('MQS','CONSULTING');
END;
/
WANN SOLLEN STATISTIKEN ERSTELLT WERDEN ?
AUTOMATISCHE ERSTELLUNG
Die Datenbank erstellt seit Version 10g automatisch im Rahmen eines Maintenance-Windows (werktäglich um 22 Uhr, Samstag und Sonntag um 6 Uhr bei 11g ) Statistiken für alle Tabellen, die keine oder veraltete Statistiken haben.
Wenn der Zeitpunkt nicht erwünscht ist, kann die Statistikerstellung folgendermaßen deaktiviert
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE('auto optimizer stats collection',null,null);
END;
/
oder verschoben werden (z. B. am Montag auf 6 Uhr morgens):
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',
'repeat_interval',
'freq=daily;byday=MON;byhour=06;byminute=0; bysecond=0');
END;
/
MANUELLE ERSTELLUNG
Eine manuelle Erstellung bietet sich dann an, wenn zum Zeitpunkt der automatischen Statistik-Erstellung die Tabelle keine repräsentativen Inhalte für Statistiken besitzt.
Ein Batch-Programm hat um 20 Uhr die Tabelle geleert, der Statistik-Erstell-Job um 22 Uhr ermittelt als Anzahl 0 Datensätze, um 2 Uhr morgens befüllt wiederum ein Batch-Job die Tabelle und um 10 Uhr erfolgt eine Query im Rahmen eines DataWareHouse-Systems. Der Oracle Optimizer ist hierbei nicht in der Lage, einen adäquaten Ausführungsplan zu erzeugen.
In einem solchen Fall ist es besser, den Statistik-Erstell-Job als Bestandteil des Ladeprozesses zu definieren. Der Aufwand für die Erstellung der Statistiken während des Ladeprozesses wird durch eine spürbare Laufzeitverbesserung des SQL-Statements (im DWH-Umfeld kann es sich um Stunden handeln) mehr als ausgeglichen.
Eine Alternative wäre auch, die Statistiken nach Beladung zu sperren oder zu löschen. Aufgrund der Möglichkeit des Optimizers, ein dynamisches Sampling bei Tabellen ohne Statistiken während der Laufzeit durchzuführen (init.ora-Parameter "optimizer_dynamic_sampling" Default=2) kann ein immer noch besserer Ausführungsplan als mit falschen Statistiken erzielt werden.
WELCHE ZUSÄTZLICHEN STATISTIKEN SOLLTEN ERSTELLT WERDEN?
FIXED OBJECT STATISTIKEN
Für X$-Tabellen verwendet der Optimizer voreingestellte Statistikwerte, die nicht unbedingt ein Abbild der tatsächlichen Beladung darstellen. Da Dynamic Sampling nicht automatisch für diese Tabellen verwendet wird, sollten Statistiken folgendermaßen erzeugt werden:
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
Die Statistik-Erstellung für Fixed Objects sollte nach Datenbank-Upgrades oder nach Änderungen in der Datenbank-Konfiguration wiederholt werden.
SYSTEM-STATISTIKEN
Neben den Objekt-Statistiken benötigt der Oracle Optimizer Informationen über die Kapazität des Systems, auf dem die Datenbank läuft.
Bei der Ausführung der Query „select * from mqs.consulting order by mitarbeiter“ kommen im wesentlichen zwei Ausführungspfade in Betracht:
- Full-Table-Scan auf mqs.consulting und Sortierung der Ergebnismenge
- Ermitteln der (bereits sortierten) Zeilen über den Primary Index
Wenn das System über schnelle CPUs und langsame Platten verfügt, ist der erste Ausführungspfad günstiger. Bei schnellen Platten und langsameren CPUs ist möglicherweise der zweite Pfad günstiger.
Damit sich die Datenbank einen Überblick über das System verschaffen kann, ist es empfehlenswert, mit folgender Prozedur Systemstatistiken zur Hauptverarbeitungszeit zu erstellen:
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'INTERVAL',
Interval => 60);
END;
/
Die von Oracle im Intervall von 60 Minuten ermittelten Werte können anschließend der VIEW SYS.AUX_STATS$ entnommen werden.
Fazit
Das Vorhandensein von passenden Objekt-Statistiken ist die unabdingbare Voraussetzung für ein performantes Datenbanksystem und eine hohe Akzeptanz auf Anwenderseite.
Statistiken auf Fixed Tables und auf Systemressourcen verbessern den Durchsatz in der Datenbank, haben aber bei weitem nicht den Effekt wie die Objekt-Statistiken.
Detaillierte Informationen zu Statistiken erhalten Sie in den DB Tuning , SQL Tuning und Optimizer Schulungsangeboten der Muniqsoft GmbH.