Oracle 11g : Restaurer les statistiques d'une table ou d'un schéma

6 Septembre 2017

Aujourd'hui, nous allons voir comment restaurer des statistiques Oracle d'une table ou encore un schéma.

Il peut en effet arriver que l'optimiseur (CBO) choisisse un plan d’exécution différent de ce qui pourrait être couramment utilisé, suite à des modifications de données dans une ou plusieurs tables, un paramètre instance qui aurait été modifié, ou encore un calcul de statistiques dont la configuration aurait changée..

Dans mon exemple, je vais générer une erreur humaine comme une suppression "malencontreuse" de stats sur une table, afin de voir comment les restaurer rapidement.

Avant toute action sur les statistiques, vérifions leur rétention en base de données (cela serait dommage de vouloir restaurer à une date à laquelle il n'y a plus de stats...)

SQL> select dbms_stats.get_stats_history_retention from dual;

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

ou encore

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
09-AUG-17 05.55.13.932491000 PM +02:00

Pour modifier cette rétention, il suffit d'utiliser la fonction "alter_stats_history_retention" du package DBMS_STATS, par exemple :

SQL> exec dbms_stats.alter_stats_history_retention(7);
PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
 7

Lors de la prochaine exécution d'un calcul de statistiques, une purge automatique sera lancée pour supprimer les données qui sont antérieures à la rétention fixée (ici 7J).

Mais attention, car si nous sommes sur une base avec de nombreux schémas ou tables, la purge des statistiques peut entraîner une génération excessive d'archivelog (si la base est en archive log bien sur).

Pour davantage d'information et pour purger manuellement les données, vous pouvez consulter cet article : Oracle 11g : How to PURGE Optimizer stats - Tablespace SYSAUX fills up !!!

Vérifions maintenant les récents calculs de statistiques sur ma table MRS.SALES, en intérogeant la table DBA_OPTSTAT_OPERATIONS :

SQL> select * from dba_optstat_operations where target like '%MRS%.%SALES%';

OPERATION          TARGET                               START_TIME                    END_TIME
------------------------- ---------------------------------------------------------------- ---------------------------------------- ---------------------------------------------------------------------------
gather_table_stats      MRS."SALES"                               24-AUG-17 03.50.20.000000 PM +02:00        24-AUG-17 03.50.21.046996 PM +02:00
gather_table_stats      MRS."SALES"                               31-AUG-17 04.50.50.000000 PM +02:00        31-AUG-17 04.50.51.841875 PM +02:00
gather_table_stats      MRS."SALES"                               31-AUG-17 05.31.26.000000 PM +02:00        31-AUG-17 05.31.27.388239 PM +02:00
gather_table_stats      MRS."SALES"                               31-AUG-17 05.32.34.000000 PM +02:00        31-AUG-17 05.32.35.079071 PM +02:00

Vérifions maintenant le nombre de lignes dans la table SALES, calculées par le CBO lors du dernier calcul de statistiques :

SQL> select table_name, num_rows from user_tables where table_name='SALES';

TABLE_NAME             NUM_ROWS
------------------------------ ----------
SALES                   160000

Je supprime maintenant les statistiques sur ma table MRS.SALES :

SQL> exec dbms_stats.delete_table_stats('MRS','SALES');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name='SALES';

TABLE_NAME             NUM_ROWS
------------------------------ ----------
SALES

Bon, là nous avons bel et bien perdu nos stats.. Au lieu de les recalculer, ce qui pourrait prendre pas mal de temps en fonction de la taille de la table, je vais les restaurer en quelques petites secondes, à savoir qu'il n'y a quasi voir pas d'impact sur la charge et le workload de la DB, contrairement à un calcul complet.

SQL> exec dbms_stats.restore_table_stats('MRS','SALES','31-AUG-17 05.32.35.079071 PM +02:00');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name='SALES';

TABLE_NAME             NUM_ROWS
------------------------------ ----------
SALES                   160000

Et voilà le tour est joué, nous avons retrouvé nos stats !

@+
Micka