Oracle 11g historise les statistiques de votre base de données. Cela permet de revenir en arrière. Vous conviendrez que ce type de manipulation est parfois une fausse bonne idée dans la mesure où, si les statistiques ont été collectées, c’est, espérons, parce que leur ancienne version ne représentaient plus vraiment les données. Revenir en arrière, ne vous sauvera donc pas toujours. Je ne lancerai pas un débat sur la manière de collecter les statistiques. Toutefois, partageons, ci-dessous, quelques commandes pour manipuler l’historique des statistiques

Période de rétention des statistiques

Le package dbms_stats et les vues du dictionnaire de données constituent les outils pour manipuler l’historique des statistiques. Par exemple, ci-dessous, vous pouvez visualiser la rétention des statistiques définie dans votre base de données :

select dbms_stats.get_stats_history_retention
  from dual;

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

Un exemple simple

Dans ce qui suit, vous trouverez un exemple simple dont j’ai déjà parlé; il s’agit d’observer que les histogrammes sont collectés automatiquement sur les colonnes qui sont utilisées dans les clauses WHERE des select. Pour cela, nous allons créer une table avec des données, non uniformément distribuées:

sqlplus scott/tiger

create table X(
      id   number, 
      text varchar2(1000));

create index X_idx on X(id);

insert into X 
  (select rownum+1, 
          rpad('X',900,'Y') 
     from dual 
  connect by level <= 10000);


insert into X 
  (select 1, 
          rpad('X',900,'Y') 
     from dual 
  connect by level <= 10000);

commit;

exec dbms_stats.gather_table_stats( -
             user, -
             'X',  -
             method_opt=>'FOR ALL COLUMNS SIZE AUTO');

col column_name format a5
select column_name, 
       count(*) 
  from user_histograms 
 where table_name='X' 
 group by column_name

COLUM   COUNT(*)
----- ----------
TEXT           2
ID             2

Comme vous pouvez l’apercevoir, par défaut et malgré la stratégie automatique, aucun histogramme n’est collecté; exécutons une requête qui inclus la colonne ID dans sa clause WHERE et reprenons les statistiques; les histogrammes sont collectés:

var x number
exec :x:=2;

select text 
  from x
 where id=:x;

exec dbms_stats.gather_table_stats( -
             user, -
             'X',  -
             method_opt=>'FOR ALL COLUMNS SIZE AUTO');

col column_name format a5
select column_name, 
       count(*) 
  from user_histograms 
 where table_name='X' 
 group by column_name

COLUM   COUNT(*)
----- ----------
TEXT           2
ID           129

Statistiques: retour arrière

Pour revenir en arrière, on peut vérifier les différentes versions des statistiques dans user_tab_stats_history comme ci-dessous:

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
select last_analyzed 
  from user_tables 
 where table_name='X';

LAST_ANALYZED
-------------------
07/03/2010 14:31:33

select STATS_UPDATE_TIME 
  from user_tab_stats_history 
 where table_name='X' order by 1;

STATS_UPDATE_TIME
-----------------------------------
07-MAR-10 02.27.44.376474 PM +01:00
07-MAR-10 02.29.59.569304 PM +01:00
07-MAR-10 02.31.33.503626 PM +01:00

Restaurer consiste alors à utiliser dbms_stats.restore_table_stats comme ci-dessous :

exec dbms_stats.restore_table_stats( -
             user, 'X',              -
             to_timestamp_tz('07-MAR-10 02.30.00.00000 PM +01:00',-
                             'DD-MON-YY HH.MI.SSxFF AM TZH:TZM'))

select column_name, count(*) 
  from user_histograms 
 where table_name='X' 
 group by column_name;

COLUM COUNT(*)
----- ----------
TEXT        2
ID        2

select STATS_UPDATE_TIME 
  from user_tab_stats_history 
 where table_name='X' 
 order by 1;

STATS_UPDATE_TIME
-----------------------------------
07-MAR-10 02.27.44.376474 PM +01:00
07-MAR-10 02.29.59.569304 PM +01:00
07-MAR-10 02.31.33.503626 PM +01:00
07-MAR-10 03.05.02.101079 PM +01:00

Vous remarquerez que l’historique des versions de statistiques n’est pas supprimé; au contraire, une nouvelle version des statistiques est créée.

Fin…

Vous finirez par une note optimiste :

drop table X purge;