Als PDF Downloaden!

Statistiken zu Tabellen und Indizes sammeln

Der kostenbasierte Optimizer braucht vernünftige, möglichst neue Statistiken, um den günstigsten Ausführungsplan ermitteln zu können. Deshalb sollten Tabellen und Indizes regelmäßig, vor allem aber nach größeren Änderungen, analysiert werden. Die bei der Analyse gesammelten statistischen Daten stehen dem Optimizer über das Data Dictionary zur Verfügung. Einträge werden vorgenommen i

• DBA_TABLES
• DBA_TAB_COLUMNS 
• DBA_INDEXES

Es gibt zwei Möglichkeiten, Statistiken zu erstellen und zu löschen:

• Der Analyze-Befehl (gilt seit Version 9i als veraltet), Oracle empfiehlt
• Das DBMS_STATS-Package (ab Version 8i)

Der (veraltete) Analyze-Befehl

Mit dem Analyze-Befehl kann immer nur ein einzelnes Objekt (Tabelle oder Index; auf Cluster wird hier nicht näher eingegangen) behandelt werden. Er bietet u.a. folgende Optionen:

• COMPUTE STATISTICS: Hier wird das komplette Objekt analysiert; diese Option liefert die genauesten Statistiken, ist allerdings auch am zeitintensivsten.

• ESTIMATE  STATISTICS: Hier wird nur eine Stichprobe analysiert, deren Größe optional noch mit angegeben werden kann als Zeilenanzahl (ROWS) oder als Prozentsatz (PERCENT); der Default liegt bei 1064 Zeilen.

• DELETE STATISTICS: Mit dieser Option können die Statistiken zu einem Objekt wieder gelöscht werden.

Bei Tabellen werden standardmäßig auch alle Indizes mit analysiert, und es können Informationen sowohl zur Tabelle als ganzes als auch zu den einzelnen Spalten gesammelt werden. Optional kann die Analyse durch eine FOR-Klausel eingeschränkt werd

• FOR TABLE: nur die Tabelle (ohne Spalten und Indizes)
• FOR ALL (INDEXED) COLUMNS: nur die (indizierten) Spalten
• FOR COULMNS: nur bestimmte Spalten
• FOR ALL INDEXES: nur die Indizes

Gelöscht werden immer alle Statistiken, auch diejenigen zu Spalten und den zugehörigen Indizes.

Beispiele:

ANALYZE TABLE test COMPUTE STATISTICS;

ANALYZE TABLE testtable ESTIMATE STATISTICS;

ANALYZE TABLE testtable DELETE STATISTICS;

ANALYZE TABLE testtable ESTIMATE STATISTICS SAMPLE 10 PERCENT
    FOR ALL INDEXED COLUMNS;              

Das (empfohlene) DBMS_STATS-Package

Ab Version 8i können Statistiken auch mit diesem Package erstellt werden. Es bietet zusätzlich die Möglichkeit, Statistiken zu erstellen und in einer Tabelle abzuspeichern, ohne dass sie Einfluss auf den Optimizer haben. Einzelheiten hierzu finden sie in der Oracle-Dokumentation. Hier wird im Weiteren nur auf Statistiken eingegangen, die im Data Dictionary gespeichert werden.

Ab Version 10g existiert standardmäßig ein Job ("GATHER_STATS_JOB"), der in einem vordefiniertem Zeitfenster (Wochentags zwischen 22.00 Uhr und 6.00 Uhr, Wochenende zwischen 24.00 und 0.00 Uhr) alle Objekte der Datenbank überprüft und bei Bedarf - falls keine Statistiken vorhanden oder diese veraltet sind - analysiert.

Das Package enthält u.a. Prozeduren, um Statistiken in unterschiedlichem Umfang zu sammeln und zu löschen:

• GATHER_TABLE_STATS | GATHER_INDEX_STATS und DELETE_TABLE_STATS | DELETE_INDEX_STATS: Statistiken zu einem einzelnen Objekt

• GATHER_SCHEMA_STATS | DELETE_SCHEMA_STATS: Statistiken zu allen Tabellen/ Indizes eines Schemas

• GATHER_DATABASE_STATS | DELETE_DATABASE_STATS: Statistiken zu allen Objekten der Datenbank

Für Oracle Versionen vor 10g gilt

Bei den GATHER-Prozeduren - außer GATHER_INDEX_STATS - muss explizit mit angegeben werden, ob Indizes mit analysiert werden sollen (Parameter cascade muss auf TRUE gesetzt sein, der Default ist FALSE).

Ohne Angabe einer Stichproben-Größe in Prozent (Parameter estimate_percent) wird das gesamte Objekt analysiert (entspricht COMPUTE beim ANALYZE-Befehl).

Optional kann auch noch angegeben werden, ob statt zufällig ausgewählter Zeilen zufällig ausgewählte Blöcke verwendet werden sollen (Parameter block_sample), Grad der Parallelität (Parameter degree) und eine vom Default ('FOR ALL COLUMNS SIZE 1') abweichende FOR-Klausel (Parameter method_opt, zulässig sind aber nur Spaltenoptionen).

Ab Version 10g gilt

Die folgenden Parameter der GATHER-Prozeduren unterliegen standardmäßig nun einem von Oracle gesteuerten Automatismus, der allerdings bei Bedarf umgangen bzw. geändert werden ka

• CASCADE => DBMS_STATS.AUTO_CASCADE
• ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
• METHOD_OPT => FOR ALL COLUMNS SIZE AUTO
• NO_INVALIDATE => DBMS_STATS.AUTO_INVALIDATE
• GRANULARITY => AUTO
• AUTOSTATS_TARGET => AUTO

Die Standardeinstellungen können sie über folgenden SELECT abfragen:

SELECT DBMS_STATS.GET_PARAM('<parameter>') FROM dual;

Beispiel für Änderungen an den Standardeinstellungen:

BEGIN
    DBMS_STATS.SET_PARAM('CASCADE','DBMS_STATS.AUTO_CASCADE');
    DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','5');
    DBMS_STATS.SET_PARAM('DEGREE','NULL');
    DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL INDEXED COLUMNS');
  END;
  /

Ab Version 11g werden die Standardeinstellungen nun über die Funktion GET_PREFS ermittelt:

SELECT DBMS_STATS.GET_PREFS('<parameter>') FROM dual;

Bei DELETE_TABLE_STATS werden Statistiken zu den einzelnen Spalten und zu den zugehörigen Indizes standardmäßig mitgelöscht, wenn nicht cascade_columns bzw. cascade_indexes auf FALSE gesetzt wird.

Beispiele (auf BEGIN und END bzw. EXEC wird hier verzichtet):

DBMS_STATS.GATHER_INDEX_STATS('SCOTT', 'PK_EMP');

DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP');

DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP',
    estimate_percent => 10,
    cascade => TRUE,
    block_sample => TRUE);

DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',
    estimate_percent => 10,
    options => 'GATHER STALE',
    cascade => TRUE);

DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', 10); -- estimate_percent

DBMS_STATS.GATHER_DATABASE_STATS(5, -- estimate_percent
    TRUE, -- block_sample
    'FOR ALL INDEXED COLUMNS SIZE 1'); -- method_opt

DBMS_STATS.DELETE_TABLE_STATS('SCOTT', 'EMP');

DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

DBMS_STATS.DELETE_DATABASE_STATS;

Die Übersicht über alle Prozeduren und Funktionen zu DBMS_STATS finden Sie in der Oracle-Dokumentation zur jeweiligen Oracle Version.

Achtung:

Bedenken Sie, dass sich in jeder neuen Version von Oracle Änderungen an den Standardeinstellungen ergeben können.

Suche