Les statistiques Oracle

Passer les stats, celà devient une phrase de plus en plus à la mode. Elle l'est surtout depuis l'expension du mode CBO (cost based optimization) d'Oracle.

A quoi servent les statistiques Oracle ?


Les statistiques Oracle sont en fait la récupération de diverses informations concernant la volumétrie des tables, la distribution des différentes valeurs des champs indexés, la taille moyenne des tuples,... . Cet ensemble d'informations génèrera via un algorithme propre à Oracle (mis à jour au fur et à mesure des versions) un coût pour chaque plan d'exécution. En mode CBO, Oracle choisira, pour une requête donnée, le plan d'exécution le moins coûteux.
Ces différentes données (statistiques) sont stockées dans des tables du dictionnaire de données, et visibles sous les vues dba_tables, dba_indexes, ...


Comment exécuter la mise à jour des stats ?


Pour celà, il existe deux méthodes, suivant celle choisie, voici les requêtes SQL dans un cas, appels PL dans l'autre (voir aussi la reconstruction de tous les index dont les statistiques sont "anciennes"):

analyze table T1 compute statistics;
analyze index I1 compute statistics;

ou bien (à préférer à partir de la 8i)

EXEC DBMS_STATS.gather_table_stats('USEREX', 'T1');
EXEC DBMS_STATS.gather_index_stats('USEREX', 'I1');

Bien qu'en général, les statistiques sont calculées sur l'ensemble du schéma de manière globale, en cascade :

EXEC DBMS_STATS.gather_schema_stats('USEREX', cascade=>TRUE);

Il existe également la possibilité d'estimer les statistiques au lieu de les calculer complètement, Oracle ne prendra alors qu'un échantillon de la table, respectivement :

analyze table T1 estimate statistics sample 60 percent;
analyze index I1 estimate statistics sample 60 percent;


ou bien

EXEC DBMS_STATS.gather_table_stats(ownname=>'USEREX', tabname=>'T1', estimate_percent=> 60);
EXEC DBMS_STATS.gather_index_stats(ownname=>'USEREX', index_name=>'I1', estimate_percent=> 60);


La valeur de estimate_percent peut prendre dbms_stats.auto_sample_size afin qu'oracle auto-détermine la meilleure valeur.


Quand effectuer la mise à jour des stats ?



Le mieux est : "dès que nécessaire".
Cette opération devenant parfois très coûteuse en temps, il n'est pas envisageable de le faire à chaque modification de la base.
D'autre part, une mise à jour trop ancienne peut influencer le plan d'exécution et provoquer de réels écarts de temps de réponse. Il est courant de les passer toutes les semaines pour des bases de productions voire toutes les nuits lorsque de forts mouvements peuvent avoir lieux au cours d'une journée.
Dans tous les cas : lors d'une migration, d'un import conséquent ou de la modification du modèle physique (changement d'un paramètre de stockage, création d'index, partitionnement, réorganisation, ...)

Il existe cependant un processus de collecte automatique mettant à jour des informations sur l'utilisation des différentes tables au fil de l'eau via le mot clé monitoring à la création d'une table / d'un index ; le calcul des statistiques sur ces objets est alors optimisé car Oracle saura sur quels segments l'activité aura eu de l'influence et recalculera les statistiques uniquement sur ceux dont au moins 10% des tuples a subi une modification / ajout / suppression.

create table tstat (colA number, colB number, colC numnber) monitoring;

Il est possible d'effectuer cette collecte automatique sur une table existante :

alter table tstat monitoring;

ou bien de tout un schéma :

EXEC DBMS_STATS.alter_schema_tab_monitoring('USEREX', TRUE);


Attention à bien considérer les impacts notamment un ralentissement faible mais général à toute modification de la base (insertion / mise à jour) ; ainsi lors d'un import, il vaut mieux désactiver cette fonctionnalité et repasser les stats sur le schéma en fin d'opération.

La table dba_tab_modifications permet de connaître les différentes modifications survenues sur une table monitorée :


SQL> desc dba_tab_modifications;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)
DROP_SEGMENTS NUMBER



Comment utiliser les statistiques d'un environnement à l'autre ?




Parfois lors de test, il est intéressant de connaître le futur comportement d'un traitement (ou d'une requête) ; or la volumétrie de notre environnement de test n'est pas celui de notre environnement de production. Il s'agit alors d'exporter les stats de prod pour les utiliser sur notre environnement de test :

$> exp USEREX/PASSEX@BDDPROD FILE=stat_prod.exp STATISTICS=Y ROWS=N OWNER=USEREX
$> imp USEREX/PASSEX@BDDTEST FILE=stat_prod.exp STATISTICS=Y ROWS=N OWNER=USEREX


Ceci permet alors d'effectuer une analyse sur le plan d'exécution utilisé en fonction de l'environnement source.

Si les statistiques ne sont pas utilisées, Oracle collecte des statistiques partielles en fonction du paramètre optimizer_dynamic_sampling lors de l'exécution des requêtes (à partir de 9i). Ceci peut être particulièrement utile lors de la forte volatilité des données stockées dans une table.

Pour aller plus loin, voir les histogrammes associés aux statistiques.


Voir aussi :
- Pourquoi mon index n'est pas utilisé ?
- Manipulation des statistiques
- Les index B-tree
- Tuning d'une vue
- Créer un index de fonction
- Comparer l'exécution d'une requête avec 2 index différents
- Hint oracle et choix du plan d'exécution




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories