Historische Tabellenstatistiken

Zur Analyse eines Problems mit einem Frequency Histogramm, in dem zwischenzeitlich ein paar relevante Werte fehlten, habe ich dieser Tage nach einer Möglichkeit gesucht, die aktuellen Tabellen-Statistiken mit früheren Werten zu vergleichen, und bin in Uwe Hesses Blog fündig geworden. Das erforderliche Hilfsmittel ist die table function dbms_stats.diff_table_stats_in_history. Dazu ein Beispiel:

-- Anlage einer Testtabelle
create table t_stats
as
select rownum id
     , mod(rownum, 10) col1
  from dual
connect by level <= 10000;

-- erste Statistikerhebung
exec dbms_stats.gather_table_stats(user, 't_stats')

-- Hinzufügen neuer Sätze
insert into t_stats(id, col1)
select rownum id
     , mod(rownum, 10) col1
  from dual
connect by level <= 100000;

commit;

-- zweite Statistikerhebung
exec dbms_stats.gather_table_stats(user, 't_stats')

Mit Hilfe der View user_tab_stats_history kann man die historischen Zeitpunkte der Statistikerfassung bestimmen:

select table_name
     , stats_update_time
  from user_tab_stats_history
 where table_name = 'T_STATS';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ --------------------------------
T_STATS                        30.08.12 20:26:04,607000 +02:00
T_STATS                        30.08.12 20:26:42,486000 +02:00

Und dann kann man die table function mit einer geeigneten Zeitangabe parametrisieren und erhält einen Report:

select * from table(dbms_stats.diff_table_stats_in_history(
                    ownname => user,
                    tabname => upper('T_STATS'),
                    time1 => systimestamp,
                    time2 => to_timestamp('30.08.2012 20:26:10','dd.mm.yyyy hh24:mi:ss'),
                    pctthreshold => 0));

REPORT
-------------------------------------------------------------------------------
###############################################################################

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

TABLE         : T_STATS
OWNER         : DBADMIN
SOURCE A      : Statistics as of 30.08.12 20:29:28,935000 +02:00
SOURCE B      : Statistics as of 30.08.12 20:26:10,000000 +02:00
PCTTHRESHOLD  : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

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

T_STATS                     T   A   110000     248        8          110000
                                B   10000      21         7          10000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

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

ID              A   100824  ,000009918 NO   0       5    C102  C30B  110000
                B   10000   ,0001      NO   0       4    C102  C302  10000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


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

Mit diesem Hilfsmittel kann man nachträglich noch die Entscheidungsgrundlagen des CBO bestimmen.