Introduction
Le package DBMS_STATS est l'un des plus importants sous Oracle. Il permet de générer les statistiques de la base, indispensables pour que l'optimiseur calcule de bons plans d'exécutions. Mais ce package est bien plus riche que la simple collecte des stats et c'est ce que nous allons voir dans cet article.

Attention, l'objectif est de montrer les familles de fonctions de ce package, et non pas de tester chacune de ces fonctions avec chacun des paramètres. Les exemples seront donc basiques.

La liste des fonctions du package DBMS_STATS pour oracle 12 est ici : https://docs.oracle.com/database/121/ARPLS/d_stats.htm.



 
Points d'attention
Aucun.



Base de tests
Une base Oracle 12.



Exemples
============================================================================================
Afficher les statistiques
============================================================================================

Pour afficher les stats de la base, on peut utiliser le package DBMS_STATS ou bien passer par les vues DBA_TAB_STATISTICS, DBA_IND_STATISTICS, DBA_TAB_COL_STATISTICS etc etc.
     
L'utilisation du package DBMS_STATS est assez lourde, si on compare avec les vues prévues par Oracle.

Liste des fonctions

     - GET_TABLE_STATS Procedure : Gets all table-related information
     - GET_COLUMN_STATS Procedures : Gets all column-related information
     - GET_INDEX_STATS Procedures : Gets all index-related information
     - GET_SYSTEM_STATS Procedure : Gets system statistics from stattab, or from the dictionary if stattab is NULL

Exemple : afficher les stats d'une table
C'est assez pénible de passer par ces fonctions, comme vous le voyez.
     SQL> DECLARE
        v_numrows     NUMBER;
        v_numblks     NUMBER;
        v_avgrlen     NUMBER;
        v_cachedblk   NUMBER;
        v_cachehit    NUMBER;
     BEGIN
     DBMS_STATS.GET_TABLE_STATS(
        ownname => null,
        tabname => 'EMPLOYEES',
     --   stattab => 'TAB_STATS_TESTS', ==> attention, ce paramètre ne doit pas être renseigné sinon une erreur ORA-20000 arrivera
        numrows => v_numrows,
        numblks => v_numblks,
        avgrlen => v_avgrlen,
        cachedblk => v_cachedblk,
        cachehit => v_cachehit);
     
     dbms_output.put_line('Nombre de lignes : ' || to_char(v_numrows));   
     dbms_output.put_line('Nombre de blocs : ' || to_char(v_numblks));   
     dbms_output.put_line('Longueur moyenne d''un enregistrement : ' || to_char(v_avgrlen));   
     END;
     /
     
     Nombre de lignes : 107
     Nombre de blocs : 5
     Longueur moyenne d'un enregistrement : 69

Il est bien plus simple de passer par des vues déjà définies.
Commençons par les stats sur les tables.
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' order by table_name;
     TABLE_NAME           NUM_ROWS  BLOCKS STA
     -------------------- ---------- ---------- ---
     COUNTRIES             25        NO
     DEPARTMENTS             27      5 NO
     EMPLOYEES            107      5 NO
     JOBS                 19      5 NO
     JOB_HISTORY             10      5 NO
     LOCATIONS             23      5 NO
     REGIONS               4      5 NO
     7 rows selected.
    
Poursuivons avec les stats sur les index.
     SQL> select INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, LAST_ANALYZED from DBA_IND_STATISTICS where OWNER = 'HR' order by TABLE_NAME, INDEX_NAME;
     INDEX_NAME          TABLE_NAME           BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS LAST_ANAL
     ------------------------- -------------------- ---------- ----------- ------------- ---------- ---------
     COUNTRY_C_ID_PK       COUNTRIES            0        1         25        25 23-AUG-18
     DEPT_ID_PK          DEPARTMENTS            0        1         27        27 23-AUG-18
     DEPT_LOCATION_IX      DEPARTMENTS            0        1          7        27 23-AUG-18
     EMP_DEPARTMENT_IX      EMPLOYEES            0        1         11       106 23-AUG-18
     EMP_EMAIL_UK          EMPLOYEES            0        1        107       107 23-AUG-18
     EMP_EMP_ID_PK          EMPLOYEES            0        1        107       107 23-AUG-18
     EMP_JOB_IX          EMPLOYEES            0        1         19       107 23-AUG-18
     ...
     19 rows selected.
     
Terminons avec les stats sur les colonnes.
     SQL> select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, LAST_ANALYZED, AVG_COL_LEN from DBA_TAB_COL_STATISTICS where owner = 'HR' order by TABLE_NAME, COLUMN_NAME;
     TABLE_NAME         COLUMN_NAME      NUM_DISTINCT      DENSITY LAST_ANAL AVG_COL_LEN
     -------------------- -------------------- ------------ ---------- --------- -----------
     COUNTRIES         COUNTRY_ID             25          .04 23-AUG-18          3
     COUNTRIES         COUNTRY_NAME            25          .04 23-AUG-18          9
     COUNTRIES         REGION_ID                 4          .25 23-AUG-18          3
     DEPARTMENTS         DEPARTMENT_ID            27 .037037037 23-AUG-18          4
     DEPARTMENTS         DEPARTMENT_NAME            27 .037037037 23-AUG-18         12
     DEPARTMENTS         LOCATION_ID             7 .142857143 23-AUG-18          3
     DEPARTMENTS         MANAGER_ID             11 .090909091 23-AUG-18          3
     EMPLOYEES         COMMISSION_PCT             7 .142857143 23-AUG-18          2
     35 rows selected.
     
Il existe d'autres statistiques dans la base, celles liées au hardware (vitesse des I/O lecture et écriture...), aux vues fixes (X$...) etc etc mais les plus importantes sont celles liées aux tables et index pour que le CBO puisse calculer le meilleur plan d'exécution.


============================================================================================
Collecter les statistiques
============================================================================================

La première utilité du package DBMS_STATS est de collecter les stats de la base.
Plusieurs procédures existent, selon la granularité de la collecte.

Liste des fonctions
     - GATHER_DATABASE_STATS Procedures : Gathers statistics for all objects in the database
     - GATHER_SCHEMA_STATS Procedures : Gathers statistics for all objects in a schema
     - GATHER_TABLE_STATS Procedure : Gathers table and column (and index) statistics
     - GATHER_INDEX_STATS Procedure : Gathers index statistics
     - GATHER_DICTIONARY_STATS Procedure : Gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components
     - GATHER_FIXED_OBJECTS_STATS Procedure : Gathers statistics of fixed objects
     - GATHER_SYSTEM_STATS Procedure : Gathers system statistics
    
     - GATHER_DATABASE_STATS_JOB_PROC : procédure pour collecter automatiquement les stats le soir
     
Exemple : afficher les stats d'un schéma
On supprime d'abord les stats du schéma pour faire notre test.
     SQL> exec DBMS_STATS.DELETE_SCHEMA_STATS('HR');
     PL/SQL procedure successfully completed.
     
Vérifions cela : plus rien dans les colonnes NUM_ROWS et autres de DBA_TAB_STATISTICS; idem pour les stats sur les index et les colonnes.
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' order by table_name;
     TABLE_NAME           NUM_ROWS     BLOCKS STA
     -------------------- ---------- ---------- ---
     COUNTRIES
     DEPARTMENTS
     EMPLOYEES
     JOBS
     JOB_HISTORY
     LOCATIONS
     REGIONS
     7 rows selected.
                 
On collecte les stats pour le schéma HR avec la bonne fonction et on vérifie que la vue DBA_TAB_STATISTICS a été mise à jour.
     SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('HR');
     PL/SQL procedure successfully completed.
     
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' order by table_name;
     TABLE_NAME           NUM_ROWS     BLOCKS STA
     -------------------- ---------- ---------- ---
     COUNTRIES             25        NO
     DEPARTMENTS             27      5 NO
     EMPLOYEES            107      5 NO
     JOBS                 19      5 NO
     JOB_HISTORY             10      5 NO
     LOCATIONS             23      5 NO
     REGIONS               4      5 NO
     7 rows selected.


============================================================================================
Supprimer les statistiques
============================================================================================

Il est possible de supprimer les stats, que ce soit au niveau de la base, d'un schéma, d'une table, d'une colonne, d'un index, du dictionnaire de données ou bien des objets X$.

Liste des fonctions
     - DELETE_DATABASE_STATS Procedure : Deletes statistics for the entire database
     - DELETE_SCHEMA_STATS Procedure : Deletes schema-related statistics
     - DELETE_TABLE_STATS Procedure : Deletes table-related statistics
     - DELETE_COLUMN_STATS Procedure : Deletes column-related statistics
     - DELETE_INDEX_STATS Procedure : Deletes index-related statistics
     - DELETE_SYSTEM_STATS Procedure : Deletes system statistics
     - DELETE_DICTIONARY_STATS Procedure : Deletes statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas)
     - DELETE_FIXED_OBJECTS_STATS Procedure : Deletes statistics of all fixed tables

Exemple : supprimer les stats d'un schéma     
     SQL> exec DBMS_STATS.DELETE_SCHEMA_STATS('HR');
     PL/SQL procedure successfully completed.
     
Vérifions cela : plus rien dans DBA_TAB_STATISTICS;
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' order by table_name;
     TABLE_NAME           NUM_ROWS     BLOCKS STA
     -------------------- ---------- ---------- ---
     COUNTRIES
     DEPARTMENTS
     EMPLOYEES
     JOBS
     JOB_HISTORY
     LOCATIONS
     REGIONS
     7 rows selected.


============================================================================================
Restaurer les statistiques
============================================================================================

Il est possible de restaurer des stats supprimées par erreur, sans pour cela avoir une sauvegarde dans une table dédiée (nous verrons cette technique cela plus tard).

Liste des fonctions
     - RESTORE_SCHEMA_STATS Procedure : Restores statistics of all tables of a schema as of a specified timestamp
     - RESTORE_TABLE_STATS Procedure : Restores statistics of a table as of a specified timestamp (as_of_timestamp), as well as statistics of associated indexes and columns
     - RESTORE_DICTIONARY_STATS Procedure : Restores statistics of all dictionary tables (tables of 'SYS', 'SYSTEM' and RDBMS component schemas) as of a specified timestamp
     - RESTORE_FIXED_OBJECTS_STATS Procedure : Restores statistics of all fixed tables as of a specified timestamp
     - RESTORE_SYSTEM_STATS Procedure : Restores statistics of all tables of a schema as of a specified timestamp
     
     - ALTER_STATS_HISTORY_RETENTION Procedure : Changes the statistics history retention value
     - GET_STATS_HISTORY_AVAILABILITY Function : Gets the oldest timestamp where statistics history is available
     - GET_STATS_HISTORY_RETENTION Function : Returns the current statistics history retention value
     
Sur combien de jours sont sauvegardées ces stats? 31 jours.
     SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
     GET_STATS_HISTORY_RETENTION
     ---------------------------
                  31
     
Et voici la date précise.
     SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
     GET_STATS_HISTORY_AVAILABILITY
     ----------------------------------------------------------------
     13-FEB-19 10.10.09.903990000 AM -04:00
     
Exmple : restaurer les stats d'un schéma
On restaure à la date de la veille et on valide cette restauration.
     SQL> exec DBMS_STATS.RESTORE_SCHEMA_STATS(ownname=>'HR',AS_OF_TIMESTAMP=>sysdate-1);
     PL/SQL procedure successfully completed.
     
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' order by table_name;
     TABLE_NAME           NUM_ROWS     BLOCKS STA
     -------------------- ---------- ---------- ---
     COUNTRIES             25        NO
     DEPARTMENTS             27      5 NO
     EMPLOYEES            107      5 NO
     JOBS                 19      5 NO
     JOB_HISTORY             10      5 NO
     LOCATIONS             23      5 NO
     REGIONS               4      5 NO
     7 rows selected.

============================================================================================
Transférer les statistiques entre deux bases
============================================================================================

Parfois on veut tester de nouvelles fonctionnalités dans une base de recette MAIS, manque de chance, sa volumétrie n'a rien à voir avec celle de prod. Un export/import Datapump n'est pas possible car la volumétrie est justement trop importante, la base de recette n'a pas l'espace disponible, les données de prod doivent être cryptées avant l'export mais on ne sait pas faire etc etc.
Une solution est de faire un export de l'ensemble des stats de la base de prod et les importer dans la base de recette.
     
Voici ce qu'il faut faire :
Base prod
     1) Créer une table d'export des stats
     2) Exporter les stats dans cette table
     3) Export Datapump de cette table
     
Base de recette
     4) Créer une table d'export des stats
     5) Exporter les stats de cette base dans cette table qui servira de sauvegarde
     6) Import Datapump de la table des stats de prod
     7) Import des stats dans la base de recette en utilisant la table précédente

     
Exemple : Création d'une table pour sauvegarder les stats
Liste des fonctions
     - CREATE_STAT_TABLE Procedure : Creates a table with name stattab in ownname's schema which is capable of holding statistics

     SQL> exec DBMS_STATS.CREATE_STAT_TABLE('HR', 'TAB_STATS_PROD');

Les noms des colonnes de la table créée sont peu parlant car ils doivent gérer tous les types de stats : base, table, index etc etc donc impossible d'avoir un nom spécifique pour chaque entité.
     SQL> desc TAB_STATS_PROD
      Name                  Null?    Type
      --------------------------------------
      STATID             VARCHAR2(128)
      TYPE                 CHAR(1)
      VERSION            NUMBER
      FLAGS                NUMBER
      C1                    VARCHAR2(128)
      C2                    VARCHAR2(128)
      C3                    VARCHAR2(128)
      C4                    VARCHAR2(128)
      C5                    VARCHAR2(128)
      C6                    VARCHAR2(128)
      N1                    NUMBER
      N2                    NUMBER
      N3                    NUMBER
      N4                    NUMBER
      N5                    NUMBER
      N6                    NUMBER
      N7                    NUMBER
      N8                    NUMBER
      N9                    NUMBER
      N10                NUMBER
      N11                NUMBER
      N12                NUMBER
      N13                NUMBER
      D1                    DATE
      T1                    TIMESTAMP(6) WITH TIME ZONE
      R1                    RAW(1000)
      R2                    RAW(1000)
      R3                    RAW(1000)
      CH1                VARCHAR2(1000)
      CL1                CLOB
      BL1                BLOB
     
     SQL> SELECT count(*) FROM TAB_STATS_PROD;
       COUNT(*)
     ----------
          0
     
Exemple : export des stats de la base entière dans une table dédiée
Liste des fonctions
     - EXPORT_DATABASE_STATS Procedure : Retrieves statistics for all objects in the database and stores them in the user statistics table identified by statown.stattab
     - EXPORT_SCHEMA_STATS Procedure : Retrieves statistics for all objects in the schema identified by ownname and stores them in the user statistics table identified by stattab
     - EXPORT_TABLE_STATS Procedure : Retrieves statistics for a particular table and stores them in the user statistics table
     - EXPORT_COLUMN_STATS Procedure : Retrieves statistics for a particular column and stores them in the user statistics table identified by stattab
     - EXPORT_INDEX_STATS Procedure : Retrieves statistics for a particular index and stores them in the user statistics table identified by stattab
     - EXPORT_SYSTEM_STATS Procedure : Retrieves system statistics and stores them in the user statistics table
     - EXPORT_FIXED_OBJECTS_STATS Procedure : Retrieves statistics for fixed tables and stores them in the user statistics table identified by stattab
     - EXPORT_DICTIONARY_STATS Procedure : Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) and stores them in the user statistics table identified by stattab
     
     - DROP_STAT_TABLE Procedure : Drops a user statistics table created by CREATE_STAT_TABLE
     
     SQL> exec DBMS_STATS.EXPORT_DATABASE_STATS('TAB_STATS_PROD');
     PL/SQL procedure successfully completed.
     
Je vous laisse regarder l'intérieur de la table...
     SQL> SELECT count(*) FROM TAB_STATS_PROD;
       COUNT(*)
     ----------
          77788
    
Exemple : import des stats de la base entière depuis une table dédiée
Liste des fonctions
     - IMPORT_DATABASE_STATS Procedure : Retrieves statistics for all objects in the database from the user statistics table and stores them in the dictionary
     - IMPORT_SCHEMA_STATS Procedure : Retrieves statistics for all objects in the schema identified by ownname from the user statistics table and stores them in the dictionary
     - IMPORT_TABLE_STATS Procedure : Retrieves statistics for a particular table from the user statistics table identified by stattab and stores them in the dictionary
     - IMPORT_COLUMN_STATS Procedure : Retrieves statistics for a particular column from the user statistics table identified by stattab and stores them in the dictionary
     - IMPORT_INDEX_STATS Procedure : Retrieves statistics for a particular index from the user statistics table identified by stattab and stores them in the dictionary
     - IMPORT_SYSTEM_STATS Procedure : Retrieves system statistics from the user statistics table and stores them in the dictionary
     - IMPORT_FIXED_OBJECTS_STATS Procedure : Retrieves statistics for fixed tables from the user statistics table identified by stattab and stores them in the dictionary
     - IMPORT_DICTIONARY_STATS Procedure : Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and RDBMS component schemas) from the user statistics table and stores them in the dictionary
     
N'ayant pas deux bases chez moi, je vais tester l'import sur ma base en supprimant les stats d'un schéma.
     SQL> exec DBMS_STATS.DELETE_SCHEMA_STATS('HR');
     PL/SQL procedure successfully completed.
     
Vérifions cela : plus rien dans DBA_TAB_STATISTICS; c'est idem pour les index et les colonnes.
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' order by table_name;
     TABLE_NAME           NUM_ROWS     BLOCKS STA
     -------------------- ---------- ---------- ---
     COUNTRIES
     DEPARTMENTS
     EMPLOYEES
     JOBS
     JOB_HISTORY
     LOCATIONS
     REGIONS
     TAB_STATS_PROD
     8 rows selected.
     
     On importe les stats : attention, cette fois on ne fait pas de restore mais le résultat sera le même.
     SQL> exec DBMS_STATS.IMPORT_SCHEMA_STATS('HR', 'TAB_STATS_PROD');
     PL/SQL procedure successfully completed.
     
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' order by table_name;
     TABLE_NAME           NUM_ROWS     BLOCKS STA
     -------------------- ---------- ---------- ---
     COUNTRIES             25        NO
     DEPARTMENTS             27      5 NO
     EMPLOYEES            107      5 NO
     JOBS                 19      5 NO
     JOB_HISTORY             10      5 NO
     LOCATIONS             23      5 NO
     REGIONS               4      5 NO
     TAB_STATS_PROD
     8 rows selected.

     Il existe aussi la procédure TRANSFER_STATS Procedure : Transfers statistics for specified table(s) from a remote database specified by dblink to the local database


============================================================================================
Gérer les préférences des statistiques
============================================================================================

Pour avoir la valeur des préférences liées aux stats, il n'y a, à ma connaissance, ni paramètre caché ni table, il faut passer par des fonctions du package DBMS_STATS, à savoir GET_PREFS. Idem pour modifier celles-ci, il faut utiliser les fonctions SET_xxx_PREFS.

Liste des fonctions
     - GET_PREFS Function : Gets the default value of the specified preference

     - SET_DATABASE_PREFS Procedure : Sets the statistics preferences of all the tables
     - SET_SCHEMA_PREFS Procedure : Sets the statistics preferences of all the tables owned by the specified owner name
     - SET_TABLE_PREFS Procedure : Sets the statistics preferences of the specified table in the specified schema
     - SET_GLOBAL_PREFS Procedure : Sets the global statistics preferences
     
     - RESET_GLOBAL_PREF_DEFAULTS Procedure : Resets the default values of all parameters to Oracle recommended values

Les préférences se gèrent comme les stats, on peut les exporter de base à base, les importer depuis une autre base et les supprimer.
     - EXPORT_DATABASE_PREFS Procedure : Exports the statistics preferences of all the tables
     - EXPORT_SCHEMA_PREFS Procedure : Exports the statistics preferences of all the tables owned by the specified owner name
     - EXPORT_TABLE_PREFS Procedure : Exports statistics preferences of the specified table in the specified schema into the specified statistics table
     
     - IMPORT_DATABASE_PREFS Procedure : Imports the statistics preferences of all the tables
     - IMPORT_SCHEMA_PREFS Procedure : Imports the statistics preferences of all the tables owned by the specified owner name
     - IMPORT_TABLE_PREFS Procedure : Sets the statistics preferences of the specified table in the specified schema
     
     - DELETE_DATABASE_PREFS Procedure : Deletes the statistics preferences of all the tables
     - DELETE_SCHEMA_PREFS Procedure : Deletes the statistics preferences of all the tables owned by the specified owner name
     - DELETE_TABLE_PREFS Procedure : Deletes statistics preferences of the specified table in the specified schema

Exemple : afficher et modifier la préférence STALE PERCENT
Voyons voir la valeur pour laquelle Oracle estime que les stats sont stale, c'est à dire obsolètes : 10%, la valeur par défaut.
     SQL> select DBMS_STATS.GET_PREFS('STALE_PERCENT', 'HR', 'EMPLOYEES') from dual;
     DBMS_STATS.GET_PREFS('STALE_PERCENT','HR','EMPLOYEES')
     --------------------------------------------------------------------------------
     10
    
 Mettons le paramètre STALE à 25% pour l'ensemble des tables du schéma HR.
     SQL> exec DBMS_STATS.SET_SCHEMA_PREFS('HR', 'STALE_PERCENT', 25);
     PL/SQL procedure successfully completed.
     
     SQL> select DBMS_STATS.GET_PREFS('STALE_PERCENT', 'HR', 'EMPLOYEES') from dual;
     DBMS_STATS.GET_PREFS('STALE_PERCENT','HR','EMPLOYEES')
     --------------------------------------------------------------------------------
     25
     
     
============================================================================================
Locker et unlocker les statistiques
============================================================================================

Il est possible de locker et délocker les stats d'un schéma ou d'une table. C'est utile lorsque celles-ci par exemple ont été générées en journée lors d'une montée en charge de la base et qu'on ne veut surtout pas écraser le soir lors du calcul automatique car le soir l'activité est faible. On considère alors qu'on a des stats de référence et on ne veut surtout plus y toucher.

Liste des fonctions
     - LOCK_SCHEMA_STATS Procedure : Locks the statistics of all tables of a schema
     - LOCK_TABLE_STATS Procedure : Locks the statistics on the table
     - LOCK_PARTITION_STATS Procedure : Locks statistics for a partition
     
On crée une table de tests avec 100 lignes.
     SQL> CREATE TABLE test01(id number);
     SQL> INSERT INTO test01 select level from dual connect by level < 101;
     SQL> COMMIT;
     
     SQL> SELECT count(*) FROM test01;
       COUNT(*)
     ----------
            100
     
On génère les stats et on vérifie celles-ci.
     SQL> exec DBMS_STATS.GATHER_TABLE_STATS('HR', 'TEST01');
     
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' and table_name = 'TEST01';
     TABLE_NAME           NUM_ROWS     BLOCKS STA
     -------------------- ---------- ---------- ---
     TEST01                100      5 NO

Exemple : locker les stats d'une table     
On lock la table, on y insère 1 000 lignes, on recalcule les stats et on vérifie si l'INSERT a été pris en compte.
     SQL> exec DBMS_STATS.LOCK_TABLE_STATS('HR', 'TEST01');
     SQL> INSERT INTO test01 select level from dual connect by level < 1001;
     SQL> SELECT count(*) FROM test01;
       COUNT(*)
     ----------
           1100
     
Et voilà, les stats sont bien lockées, impossible d'en générer de nouvelles.      
     SQL> exec DBMS_STATS.GATHER_TABLE_STATS('HR', 'TEST01');
     BEGIN DBMS_STATS.GATHER_TABLE_STATS('HR', 'TEST01'); END;
     
     *
     ERROR at line 1:
     ORA-20005: object statistics are locked (stattype = ALL)
     ORA-06512: at "SYS.DBMS_STATS", line 36873
     ORA-06512: at "SYS.DBMS_STATS", line 36507
     ORA-06512: at "SYS.DBMS_STATS", line 8582
     ORA-06512: at "SYS.DBMS_STATS", line 9461
     ORA-06512: at "SYS.DBMS_STATS", line 35836
     ORA-06512: at "SYS.DBMS_STATS", line 36716
     ORA-06512: at line 1
     
On peut aussi, bien sur, délocker des stats avec les fonctions UNLOCK.

Liste des fonctions
     - UNLOCK_SCHEMA_STATS Procedure : Unlocks the statistics on all the tables in schema
     - UNLOCK_TABLE_STATS Procedure : Unlocks the statistics on the table
     - UNLOCK_PARTITION_STATS Procedure : Unlocks the statistics for a partition


Exemple : unlocker les stats d'une table    
C'est OK, le délockage fonctionne bien, on a pu générer de nouvelles stats.
     SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'TEST01');
     SQL> exec DBMS_STATS.GATHER_TABLE_STATS('HR', 'TEST01');
     SQL> select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = 'HR' and table_name = 'TEST01';
     TABLE_NAME           NUM_ROWS     BLOCKS STA
     -------------------- ---------- ---------- ---
     TEST01               1100      5 NO


============================================================================================
Laisser les statistiques en attente de publication
============================================================================================

Oracle permet de calculer de nouvelles stats et de les laisser en attente, de ne pas les rendre effectives de suite pour le CBO. Oracle assure cette fonctionnalité en laissant ces stats ailleurs que dans le dictionnaire de données.
Intérêt : générer des stats de prod MAIS les laisser en mode pending, les transférer dans une base de tests et, si les plans d'exécution sont meilleurs, les rendre accessibles en prod.

Liste des fonctions
     - PUBLISH_PENDING_STATS Procedure : Publishes the statistics gathered and stored as pending
     - EXPORT_PENDING_STATS Procedure : Exports the statistics gathered and stored as pending
     - DELETE_PENDING_STATS Procedure : Deletes the private statistics that have been collected but have not been published

Exemple : ne pas publier les stats d'un schéma    
On met le paramètre PUBLISH à FALSE pour éviter que le schéma HR n'ait de nouvelles stats.
     SQL> exec DBMS_STATS.SET_SCHEMA_PREFS('HR', 'PUBLISH', 'FALSE');
     SQL> select table_name, num_rows from DBA_TAB_STATISTICS where owner = 'HR' and table_name = 'TEST01';
     TABLE_NAME           NUM_ROWS
     -------------------- ----------
     TEST01               1100
     
On crée dix lignes, on collecte les stats et, bingo, on voit bien que la colonne NUM_ROWS n'a pas augmenté.
     SQL> INSERT INTO test01 select level from dual connect by level < 11;
     10 rows created.
     
     SQL> COMMIT;
     SQL> SELECT count(*) FROM test01;
       COUNT(*)
     ----------
           1110
           
     SQL> exec DBMS_STATS.GATHER_TABLE_STATS('HR', 'TEST01');
     SQL> select table_name, num_rows from DBA_TAB_STATISTICS where owner = 'HR' and table_name = 'TEST01';
     TABLE_NAME           NUM_ROWS
     -------------------- ----------
     TEST01               1100
     
On remet le paramètre PUBLISH à TRUE, on collecte les stats et celles-ci sont bien mises à jour.
     SQL> exec DBMS_STATS.SET_SCHEMA_PREFS('HR', 'PUBLISH', 'TRUE');
     SQL> exec DBMS_STATS.GATHER_TABLE_STATS('HR', 'TEST01');
     SQL> select table_name, num_rows from DBA_TAB_STATISTICS where owner = 'HR' and table_name = 'TEST01';
     TABLE_NAME           NUM_ROWS
     -------------------- ----------
     TEST01               1110


============================================================================================
Visualiser les statistiques sous forme de rapport
============================================================================================

Il est possible, sans collecter les stats, d'afficher quelles seront celles qui seront générées lors du prochain lancement d'une procédure DBMS_STATS.GATHER_xxx. Pour cela, il faut utiliser les fonctions DBMS_STATS.REPORT_xxx. Attention, ce sontd es fonctions, pas des procédures donc il faut faire un SELECT ... FROM dual;

Liste des fonctions
     - REPORT_GATHER_DATABASE_STATS Functions : Runs the GATHER_DATABASE_STATS Procedures in reporting mode.
     - REPORT_GATHER_SCHEMA_STATS Functions : Runs the GATHER_SCHEMA_STATS Procedures in reporting mode
     - REPORT_GATHER_TABLE_STATS Function : Runs the GATHER_TABLE_STATS Procedure in reporting mode
     - REPORT_COL_USAGE Function : Reports the recorded column (group) usage information
     
     - REPORT_GATHER_DICTIONARY_STATS Functions : Runs the GATHER_DICTIONARY_STATS Procedure in reporting mode
     - REPORT_GATHER_FIXED_OBJ_STATS Function : Runs the GATHER_FIXED_OBJECTS_STATS Procedure in reporting mode
     
     - REPORT_STATS_OPERATIONS Function : Generates a report of all statistics operations that take place between two timestamps which may or may not have been provided
     - REPORT_GATHER_AUTO_STATS Function : Runs the auto statistics gathering job in reporting mode
     - REPORT_SINGLE_STATS_OPERATION Function : Generates a report for the provided operation optionally in a particular pluggable database (PDB) in a multitenant environment

Exemple : report pour les stats d'un schéma
A ma grande surprise, Oracle n'affiche rien pour ce code, peut-être qu'il n'y avait aucune modifications dans ma base susceptible de générer de nouvelles stats...
     SQL> VARIABLE my_report CLOB;
     BEGIN
         :my_report :=DBMS_STATS.REPORT_GATHER_SCHEMA_STATS(
         ownname      => 'HR'       ,
         detail_level => 'TYPICAL',
         format=>'TEXT'    );
         dbms_output.put_line(dbms_lob.substr( :my_report, 4000, 1 )) ;
         END;
     /
     SQL> PL/SQL procedure successfully completed.


============================================================================================
Modifier à la main les statistiques
============================================================================================

Il est possible aussi de modifier certaines statistiques à la main.

Liste des fonctions
     - SET_TABLE_STATS Procedure : Sets table-related information
     - SET_COLUMN_STATS Procedures : Sets column-related information
     - SET_INDEX_STATS Procedures : Sets index-related information
     - SET_SYSTEM_STATS Procedure : Sets system statistics

Exemple : modifier le nombre de lignes d'une table
On va passer le nombre de lignes de 11110 à 1.
     SQL> select table_name, num_rows from DBA_TAB_STATISTICS where owner = 'HR' and table_name = 'TEST01';
     TABLE_NAME           NUM_ROWS
     -------------------- ----------
     TEST01               11110

     SQL> exec DBMS_STATS.SET_TABLE_STATS (ownname=>'HR', tabname=>'TEST01', numrows=>1);

     SQL> select table_name, num_rows from DBA_TAB_STATISTICS where owner = 'HR' and table_name = 'TEST01';
     TABLE_NAME           NUM_ROWS
     -------------------- ----------
     TEST01                  1


============================================================================================
Visualiser les différences entre des statistiques générées à deux dates différentes
============================================================================================

Oracle nous permet aussi de visualiser l'évolution des stats dans le temps, en utilisant les fonctions DIFF_xxx.

Liste des fonctions

     - DIFF_TABLE_STATS_IN_HISTORY Function : Compares statistics for a table from two timestamps in past and compare the statistics as of that timestamps
     - DIFF_TABLE_STATS_IN_PENDING Function : Compares pending statistics and statistics as of a timestamp or statistics from dictionary
     - DIFF_TABLE_STATS_IN_STATTAB Function : Compares statistics for a table from two different sources

Exemple afficher les différences des stats d'une table entre ce jour et il y a dix jours
     SQL> set long 1000000
    
SQL> select * from table(dbms_stats.diff_table_stats_in_history(

                    ownname => 'HR',
                    tabname => ('TEST01'),
                    time1 => systimestamp,
                    time2 => systimestamp - 10,
                    pctthreshold => 0));   

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

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

     TABLE          : TEST01
     OWNER          : HR
     SOURCE A      : Statistics as of 31-MAR-19 08.19.21.569712 AM -04:00
     SOURCE B      : Statistics as of 21-MAR-19 08.19.21.000000 AM -04:00

     REPORT
     --------------------------------------------------------------------------------
     MAXDIFFPCT
     ----------
     PCTTHRESHOLD  : 0
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

     NO DIFFERENCE IN COLUMN STATISTICS

     REPORT
     --------------------------------------------------------------------------------
     MAXDIFFPCT
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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


============================================================================================
Créer des statistiques étendues
============================================================================================

Oracle permet de créer des statistiques étendues en indexant des fonctions appliquées à des colonnes (UPPER(nom)...) ou bien de traiter des colonnes qui ont un lien entre elles : par exemple VILLE et PAYS.

Attention, il ne semble pas possible avec la fonction CREATE_EXTENDED_STATS de créer manuellement des stats sur un groupe de colonnes; cette fonction détecte les colonnes liées entre elles en se basant sur les opérations durant les heures de charges de la base.

Pour créer manuellement une stats sur deux colonnes, il faut utiliser DBMS_STATS.GATHER_TABLE_STATS comme le conseille la doc Oracle :
     BEGIN
       DBMS_STATS.GATHER_TABLE_STATS( 'sh','customers',
       METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' || 'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
     END;
     /

Liste des fonctions
     - CREATE_EXTENDED_STATS Function : Creates a virtual column for a user specified column group or an expression in a table
     - DROP_EXTENDED_STATS Procedure : Drops the statistics entry that is created for the user specified extension
     - SHOW_EXTENDED_STATS_NAME Function : Returns the name of the virtual column that is created for the user-specified extension


============================================================================================
Fonctions liées aux histogrammes
============================================================================================

Oracle propose aussi des fonctions liées aux histogrammes.

Liste des fonctions
     - CONVERT_RAW_VALUE Procedures : Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value
     - CONVERT_RAW_VALUE_NVARCHAR Procedure : Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value
     - CONVERT_RAW_VALUE_ROWID Procedure : Converts the internal representation of a minimum value, maximum value, or histogram endpoint actual value into a datatype-specific value
     
     - PREPARE_COLUMN_VALUES Procedures : Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SEED_COL_USAGE Procedure
     - PREPARE_COLUMN_VALUES_NVARCHAR Procedure : Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SEED_COL_USAGE Procedure
     - PREPARE_COLUMN_VALUES_ROWID Procedure : Converts user-specified minimum, maximum, and histogram endpoint datatype-specific values into Oracle's internal representation for future storage using the SEED_COL_USAGE Procedure


============================================================================================
Autres fonctions
============================================================================================

Il existe encore d'autres fonctions sur les stats mais celles-ci sont tellement spécifiques que je les cite seulement, sans faire de test.

Liste des fonctions
     - GATHER_PROCESSING_RATE Procedure : Starts the job of gathering the processing rates which end after interval defined in minutes
     - SET_PROCESSING_RATE Procedure : Sets the value of rate of processing for a given operation
     - DELETE_PROCESSING_RATE Procedure : Deletes the processing rate of a given statistics source. If the source is not specified, it deletes the statistics of all the sources
     
     - COPY_TABLE_STATS Procedure : Copies the statistics of the source [sub] partition to the destination [sub] partition after scaling
     
     - FLUSH_DATABASE_MONITORING_INFO Procedure : Flushes in-memory monitoring information for all the tables to the dictionary
     - GENERATE_STATS Procedure : Generates object statistics from previously collected statistics of related objects
     
     - MERGE_COL_USAGE Procedure : Merges column usage information from a source database, by means of a dblink, into the local database
     
     - PURGE_STATS Procedure : Purges old versions of statistics saved in the dictionary
     - REMAP_STAT_TABLE Procedure : Remaps the names of objects in the user statistics table
     
     - SEED_COL_USAGE Procedure : Iterates over the SQL statements in the specified SQL tuning set, compiles them and seeds column usage information for the columns that appear in these statements
     - UPGRADE_STAT_TABLE Procedure : Upgrades user statistics on an older table