Neues zum Statistikmanagement mit DBMS_STATS
von Ulrike Schwinn, ORACLE Deutschland GmbH

Optimizer-Statistiken liefern seit der Einführung des Cost Based Optimizers eine der wichtigsten Grundlagen zur Ermittlung eines guten Ausführungsplans durch den Optimizer. Um das Statistikmanagement zu erleichtern, gibt es seit Oracle Version 10g einen vordefinierten Datenbank-Scheduler Job, der in einem vordefinierten Mainentance Window ausgeführt wird, und das Sammeln von Statistiken automatisiert. Automatisch wird dabei die Sample-Grösse festgelegt, die "STALE"-Statistiken berechnet und die Histogramm-Erzeugung festgelegt.
Die manuelle Nutzung des Package DBMS_STATS ist dadurch allerdings nicht überflüssig geworden, da DBMS_STATS über den vordefinierten Scheduler-Job hinaus eine Vielzahl von zusätzlichen Möglichkeiten bietet, die zudem in 11g erweitert worden sind. Abgesehen vom manuellen Sammeln von Statistiken kann die Nutzung des Package DBMS_STATS weitere interessante Aufgaben erfüllen wie z.B.:

  • Statistiken importieren und exportieren
  • Statistiken durch andere Statistiken ersetzen, um unterschiedliche Workloads zu simulieren
  • Statistiken löschen
  • Statistiken sperren
  • Statistiken als privat deklarieren (11g)
  • Statistikberechnung auf Gruppen oder Ausdrücke von Spalten erweitern (11g)
  • Unterschiede der Statistiken von verschiedenen Zeitpunkten monitoren
  • Auf Statistiken eines älteren Zeitpunkts zurücksetzen

    • (11g): Die erst ab 11g zur Verfügung stehenden Funktionen sind gekennzeichnet.

    In folgendem Tipp wird die Verwendung einiger dieser Funktionen an Beispielen demonstriert.

    Seit Oracle Release 10g werden die Statistiken automatisch im SYSAUX Tablespace gespeichert, falls sie mit dem Package DBMS_STATS verändert worden sind. Damit soll die Möglichkeit gegeben werden, bis zu einem bestimmten Zeitpunkt Statistiken zurückzusetzen. Die Speicherdauer kann dabei konfiguriert und angezeigt werden. Folgendes Beispiel zeigt die Verwendung: 
    -- Anzeige der Retention Time
SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> SELECT to_char(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'dd.mon.yyyy hh24:mi:ss') AS ab_datum FROM dual;

AB_DATUM
--------------------
21.nov.2008 01:16:54

-- Anpassen der Speicherdauer
SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);
PL/SQL procedure successfully completed.
    Um zu beurteilen, wann Statistiken auf Schema- oder Datenbank-Ebene gesammelt worden sind, kann die View DBA_OPTSTAT_OPERATIONS verwendet werden. 
    SQL> SELECT * FROM dba_optstat_operations ORDER BY end_time;

OPERATION                     TARGET   START_TIME           END_TIME
----------------------------- -------- -------------------- --------------------
....
gather_schema_stats           COMP     22.12.2008 14:17     22.12.2008 14:26
alter_stats_history_retention          22.12.2008 15:46     22.12.2008 15:46
gather_schema_stats           COMP     22.12.2008 16:35     22.12.2008 16:43
gather_database_stats(auto)            22.12.2008 22:00     22.12.2008 22:08
gather_schema_stats           SCOTT    23.12.2008 10:28     23.12.2008 10:28
    Abgesehen vom automatischen Datenbank Job-Lauf (siehe gather_database_stats(auto)), sind offensichtlich Schema-Statistiken von COMP und SCOTT berechnet worden, wie in unserem Beispiel zu sehen ist.

    Eine genaue Historie der Tabellen-Statistiken lässt sich über die View DBA_TAB_STAT_HISTORY einsehen. In folgendem Beispiel der Tabelle BIGEMP zeigt sich, dass die Statistiken der Tabelle in kurzem Zeitraum hintereinander gesammelt wurden. 
    SQL> ALTER SESSION SET nls_timestamp_tz_format='dd.mm.yyyy hh24:mi';
SQL> SELECT table_name, stats_update_time FROM dba_tab_stats_history WHERE table_name='BIGEMP';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -------------------------
BIGEMP                         23.12.2008 09:46
BIGEMP                         23.12.2008 09:51
BIGEMP                         23.12.2008 09:59
...
    Welches sind nun die Unterschiede in den Statistikberechnungen? Einfach Überprüfen lassen sich die Tabellenstatistiken mit einem Differenzreport, der über die Funktion DIFF_TAB_STATS_IN_HISTORY generiert wird. Als Eingabeparameter müssen zwei Zeitpunkte eingegeben werden. Der letzte Parameter gibt in Prozent an, wie gross die Veränderung mindestens sein muss, um angeziegt zu werden - hier mindestens 1 Prozent. Im Beispiel werden die Statistiken von 9:51 Uhr mit dem aktuellen Stand verglichen. 
    SQL> set long 10000
SQL> SELECT * FROM TABLE (DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY
  ('SCOTT', 'BIGEMP',to_timestamp('23.12.2008 09:51','dd.mm.yyyy hh24:mi'),sysdate,1));

REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : BIGEMP
OWNER         : SCOTT
SOURCE A      : Statistics as of 23.12.2008 09:51
SOURCE B      : Statistics as of 23.12.2008 10:28
PCTTHRESHOLD  : 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

BIGEMP                      T   A   14         4          37         14
                                B   114688     756        38         114688
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

COMM            A   4       .25        NO   10      2    80    C20F  4
                B   4       .25        NO   81920   2    80    C20F  32768
MGR             A   6       .166666666 NO   1       4    C24C4 C2500 13
                B   6       .166666666 NO   8192    4    C24C4 C2500 106496
FLAG            A   NO_STATS
                B   1       1          NO   104689  2    4E    4E    9999
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
...
    Es ist leicht zu erkennen, dass sich die Zeilengrösse zwischen dem Zeitpunkt A und B verändert hat. Zusätzlich ist die Spalte FLAG hinzugefügt worden. Mit einem einzigen Kommando lässt sich nun der Zeitpunkt A wiederherstellen. 
    SQL> execute DBMS_STATS.RESTORE_TABLE_STATS ('SCOTT','BIGEMP',to_timestamp('23.12.2008 09:51','dd.mm.yyyy hh24:mi'));
PL/SQL procedure successfully completed.
    Die Überprüfung mit der Funktion DIFF_TABLE_STATS_IN_HISTORY zeigt keine Veränderungen mehr an; das Ersetzen der Statistiken war also erfolgreich. 
    SQL> SELECT * FROM TABLE (DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY
  ('SCOTT', 'BIGEMP',to_timestamp('23.12.2008 09:51','dd.mm.yyyy hh24:mi'),sysdate,1));
REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : BIGEMP
OWNER         : SCOTT
SOURCE A      : Statistics as of 23.12.2008 09:51
SOURCE B      : Statistics as of 23.12.2008 10:44
PCTTHRESHOLD  : 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN COLUMN STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

         0
    Danach ist es natürlich immer noch möglich die Statistiken wieder auf den aktuellen Zeitraum B zu ändern.

    Solche Massnahmen sollten natürlich zuerst in einer entsprechenden Testumgebung ausgetestet werden. Steht keine Testumgebung zur Verfügung, gibt es ab 11g die neue Möglichkeit die Statistiken als pending zu deklarieren und in einer lokalen Session oder natürlich auch in einer Testumgebung zu testen. Im folgenden Beispiel wird dies demonstriert.

    Es wird eine Testtabelle EMP14, eine Kopie der EMP Tabelle mit 14 Zeilen, zur Verfügung gestellt und danach die aktuellen Statistiken gesammelt. 
    SQL> CREATE TABLE emp14 AS SELECT * FROM emp;
Table created.

SQL> execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP14');
PL/SQL procedure successfully completed.
    Nun werden alle Zeilen gelöscht und erneut die Statistiken berechnet. Die Berechnung der Statistiken wird allerdings erst dann durchgeführt, nachdem wir den Status PUBLISH auf FALSE gesetzt haben. Diese Art der Statistiken werden im Folgenden als pending oder privat bezeichnet. 
    SQL> DELETE FROM emp14;
14 rows deleted.
SQL> COMMIT; 
COMMIT complete.

-- PUBLISH auf FALSE setzen
SQL> execute DBMS_STATS.SET_TABLE_PREFS('SCOTT','EMP14','PUBLISH','FALSE');
PL/SQL procedure successfully completed.

--Statistiken berechnen
SQL> execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP14');
PL/SQL procedure successfully completed.
    Eine Überprüfung der Statistiken zeigt, dass die "alten" Statistiken mit 14 Zeilen verwendet werden. 
    SQL> SELECT num_rows, last_analyzed FROM user_tables WHERE table_name='EMP14';

  NUM_ROWS LAST_ANALYZED
---------- ----------------
        14 23.12.2008 11:21
    Pending Statistiken lassen sich nun in den entsprechenden Data Dictionary Views wie DBA_TAB_PENDING_STATS oder DBA_IND_PENDING_STATS anzeigen. Wie publizierte Statistiken gibt es die Möglichkeiten mit den Prozeduren EXPORT_PENDING_STATS einen Export durchzuführen und auf einem Testsystem einen Vergleich durchzuführen. Allerdings steht keine Funktion IMPORT_PENDING_STATS (Stand Oktober 2010) zur Verfügung. Möchte man hingegen einen Differenzreport generieren, lässt sich wie im vorangegangen Beispiel eine spezielle Funktion des Package DBMS_STATS nutzen. Der folgende Auszug illustriert dies am Beispiel: 
    SQL> SELECT * FROM table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('SCOTT','EMP14'));

REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : EMP14
OWNER         : SCOTT
SOURCE A      : Current Statistics in dictionary
SOURCE B      : Pending Statistics
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

EMP14                       T   A   14         4          37         14
                                B   0          4          0          0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

COMM            A   4       .25        NO   10      2    80    C20F  4
                B   0       0          NO   0       0      0
DEPTNO          A   3       .333333333 NO   0       3    C10B  C11F  14
                B   0       0          NO   0       0      0
EMPNO           A   14      .071428571 NO   0       4    C24A4 C2502 14
                B   0       0          NO   0       0      0
ENAME           A   14      .071428571 NO   0       6    41444 57415 14
                B   0       0          NO   0       0      0
HIREDATE        A   13      .076923076 NO   0       8    77B40 77BB0 14
                B   0       0          NO   0       0      0
JOB             A   5       .2         NO   0       8    414E4 53414 14
                B   0       0          NO   0       0      0
MGR             A   6       .166666666 NO   1       4    C24C4 C2500 13
                B   0       0          NO   0       0      0
SAL             A   12      .083333333 NO   0       4    C209  C233  14
                B   0       0          NO   0       0      0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
...

    Interessant ist die Möglichkeit in der lokalen Session den Parameter OPTIMIZER_USE_PENDING_STATISTICS zu verwenden. Ist dieser Parameter auf TRUE eingestellt, werden die pending Statistiken bei der Ermittlung des Ausführungsplans berücksichtigt. In Verbindung mit dem SQL Performance Analyzer ist damit ein einfaches Austesten von Änderungen an Statistiken ohne Risiko möglich.

    Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

    Zurück zur Community-Seite