Restaurer des statistiques antérieures

Depuis la 10g, à chaque fois que vous calculez des statistiques dans Oracle via le package DBMS_STATS les statistiques précédentes sont sauvegardées dans le dictionnaire de données pendant une durée par défaut de 31 jours. L’intérêt de cette historisation des statistiques c’est qu’il est alors possible de les restaurer facilement.

La restauration de statistiques antérieures peut être utile lorsqu’après un calcul de stats sur une ou plusieurs tables on se retrouve avec des nouveaux plans moins performants. Le fait de restaurer les statistiques précédentes peut nous permettre de revenir aux performances correctes observées précédemment en attendant d’analyser le problème de performances lié à ces nouvelles stats.

Voici un exemple très simple pour comprendre le fonctionnement de l’historisation des statistiques.

SQL> select table_name,last_analyzed from user_tables where table_name='T1';
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
T1                             25-JUL-11
 
SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                  72742

Je vois que les dernières stats sur ma table T1 datent du 25/07/2011 et que le nombre de lignes dans la table est estimé à 72742.
Maintenant je vide la table et je recalcule des stats :

SQL> truncate table T1;
 
Table truncated.
 
SQL> exec dbms_stats.gather_table_stats(USER,'T1');
 
PL/SQL procedure successfully completed.
 
SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                      0

J’ai bien maintenant 0 lignes dans ma table et les stats reflètent bien cet état de fait.

L’historique des statistiques est visualisable dans la vue USER_TAB_STATS_HISTORY :

SQL> select STATS_UPDATE_TIME
  2  from user_tab_stats_history where TABLE_NAME='T1';
 
STATS_UPDATE_TIME
---------------------------------------------------------------------------
25-JUL-11 02.40.18.819000 PM +02:00
25-JUL-11 02.45.31.305000 PM +02:00
25-JUL-11 02.45.47.274000 PM +02:00
25-AUG-11 01.44.58.074000 PM +02:00

En requêtant la vue USER_TAB_STATS_HISTORY on voit que lors des 31 derniers jours les statistiques ont été collectées 4 fois sur la table T1.

Ces stats sont sauvegardées et sont donc restaurables en utilisant la procédure RESTORE_TABLE_STATS du package DBMS_STATS.

SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(    OWNNAME=>USER, TABNAME=>'T1', as_of_timestamp=>sysdate-1);
  3  END;
  4  /
 
PL/SQL procedure successfully completed.

La requête ci-dessus restaure l’état des statistiques de la table T1 de la veille.
D’ailleurs si j’interroge la vue USER_TAB_STATISTICS je vois que les statistiques courante de T1 reflètent l’existence de 72742 lignes alors que ma table est vide.

SQL> select TABLE_NAME,NUM_ROWS
  2  from user_tab_statistics where table_name='T1';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                  72742

Il existe des procédures équivalentes à RESTORE_TABLE_STATS pour restaurer les statistiques au niveau de toute la base ou bien au niveau schéma, dictionnaire de données etc. (voir la doc Oracle).