Als PDF Downloaden!

Tipps & Tricks: Mai 2014
Bereich:
DBA
Erstellung:
05/2014 KS
Versionsinfo:
10g, 11g
Letzte Überarbeitung:
05/2014 KS
Tipps zur Statistikerstellung in der Datenbank
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-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 Version 10gR2 fest und kann nicht geändert werden. Wenn die Notwendigkeit besteht, Statistiken für eine
Tabelle öfters erstellen zu lassen, gibt es ab Version 11gR1 die Prozedur
DBMS_STATS.SET_TABLE_PREFS .
Z.B. kann die Tabelle CONSULTING 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','CONSULTING','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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 6
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','CONSULTING',
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 6
Wenn in der Applikation aussschließlich Bind-Variablen verwendet werden, sollten die Histogramme gelöscht
werden:
SQL>
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('MQS','CONSULTING');
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.
SQL>
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 11g)
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.
SQL>
BEGIN
DBMS_STATS.SET_TABLE_PREFS ('MQS','CONSULTING','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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 6
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.
SQL>
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
SQL>
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE('auto optimizer stats collection',null,null);
END;
/
oder verschoben werden (z. B. am Montag auf 6 Uhr morgens):
SQL>
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 6
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:
SQL>
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
zwei Ausführungspfade in Betracht:
kommen im wesentlichen
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:
SQL>
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 5 von 6
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.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 6 von 6