Collecte de statistiques : principes

La collecte de statistiques dans une base Oracle est un élément indispensable du Tuning d’application. Des statistiques manquantes ou obsolètes sont souvent la cause de requêtes lentes, aux performances instables, entrainant une surconsommation de ressources (Cpu, accès disques, mémoire).

A partir de la version 10g, Oracle a automatisé cette collecte pour obtenir une bonne qualité des statistiques dans une grande majorité des cas, sans obliger le DBA à écrire des scripts complexes. Associée à resource manager, la collecte des statistiques est par ailleurs peu intrusive sur les applications.

Même si cette collecte automatique empêche globalement les tables d’avoir des statistiques non valides, elle ne suffit pas toujours à garantir des performances stables et garanties. En connaitre les principes et savoir où il y a des manques permet au DBA d’ajuster certains paramètres et de mieux maitriser des dérives éventuelles.

Dans cette première partie nous décrivons la raison d’être des statistiques et comment Oracle a implémenté la collecte automatique.

Pourquoi collecter des statistiques ?

Le Cost Based Optimizer

Depuis Oracle 10.1, le Cost Based Optimizer (CBO) est devenu le seul optimiseur supporté. Le CBO a la lourde responsabilité de choisir un plan d’exécution optimal pour chaque requête, de la plus simple à la plus complexe. Le plan choisi est celui qui a le moindre cout (cost). Ce cout est évalué exclusivement à partir des statistiques et non à partir de l’exécution réelle. Les statistiques doivent permettre notamment d’évaluer la sélectivité des prédicats qui conduit à choisir tel index (ou pas d’index) et la cardinalité qui conduit à choisir l’ordre de jointure, le type de jointure, et le coût d’un tri ou d’un agrégat (group by).

Basiquement les statistiques sont :

Pour les tables :

Nombre de lignes de chaque table
Nombre de valeurs distinctes de chaque colonne
Taille moyenne des colonnes

Pour les indexes

Nombre de valeurs dans l’index
Nombre de clés dans l’index (valeurs distinctes)
Nombre moyen de valeurs par clé
Clustering factor de l’index (très influent sur le choix de l’index)

Chacun, dans son expérience, aura constaté a quel point un plan d’exécution peut avoir une incidence énorme en terme de temps de réponse d’une requête et pour la santé générale de l’instance (il suffit de quelques requêtes ayant un plan d’exécution inapproprié pour mettre à mal une machine).

De quoi est constitué le cout (cost) ?

Le cost s’affiche par exemple avec la commande explain. Il s’agit d’un chiffre positif dont le valeur indique quel plan devrait permettre l’exécution la plus rapide de la requête. Ainsi une requête ayant un coût de 100 devrait être plus rapide qu’une requête ayant un coût de 500. Attention, nous avons bien dit devrait.

Sans détailler ici le cheminement utilisé par l’optimiseur pour déterminer le plan d’exécution optimal, il faut savoir que le calcul du cout dépend principalement de l’estimation du temps Cpu (Cpu costing introduit avec Oracle 9.2) et de l’estimation du nombre de lectures de blocs accédés.

Par exemple, si une requête doit effectuer un accès restreint à certaines lignes d’une table via une Where Clause puis faire un tri sur une colonne indexée, un plan possible est de balayer la table en effectuant des lectures multi-blocks puis un tri sur les lignes, un autre serait d’accéder d’abord à l’index de colonne puis à chaque ligne de la table par son rowid.

Le premier chemin peut être moins couteux en nombre de lectures (gare les lectures multi-blocks sont très performantes) mais nécessite du temps Cpu supplémentaire pour faire le tri. Le second chemin évite le tri mais peut nécessiter de faire de nombreuses lectures mono-blocks.

Selon le matériel, les lectures disques, le vitesse du Cpu, la présence de plus ou moins de mémoire cache varient, ce qui complique encore un peu l’équation : un plan optimal dans un certain environnement hardware peut ne plus l’être dans un autre environnement.

C’est pourquoi, en plus de la requête elle-même (sélectivité et cardinalité), Oracle a besoin d’informations sur la vitesse du Cpu et la capacité de débit des disques. Cela se calcule à l’aide des statistiques système (voir dans les parties suivantes de l’article).

Le dynamic Sampling c’est quoi ?

Les statistiques sont tellement indispensables qu’Oracle les calculera dynamiquement si elles sont absentes sur une ou plusieurs tables au moment de l’exécution d’une requête. Il passera du temps supplémentaire dans la phase d’analyse de la requête (parse) en regardant un petit pourcentage (sampling) des lignes de tables ou partitions de tables sans statistiques et récoltera un minimum d’informations. On appelle cela le dynamic sampling. Bien que le dynamic sampling puisse être mis à profit de façon positive et intelligente dans certains cas, il est toujours d’actualité d’avoir des statistiques préalablement calculées et stockées dans le dictionnaire.

Statistiques obsolètes : pourquoi, quand ?

Table monitoring

Collecter des statistiques est un processus couteux (avoir des statistiques non à jour se payera en requêtes anormalement consommatrices de ressources et sera encore plus couteux). Il ne peut pas être fait à chaque fois qu’une mise à jour à lieu sur la table et donc pas en temps réel. A la place, Oracle décompte de manière implicite le nombre de modifications faites sur chaque table. C’est la table monitoring. Ce décompte apparait dans la vue DBA_TABLE_MODIFICATIONS qui contient le nombre de modifications (INSERT, UPDATE, DELETE) de toute table depuis son dernier calcul de statistiques.

Prise en compte du table monitoring par Oracle

Le nombre de lignes modifiées est comparé au nombre total de lignes de la table. Par défaut Oracle se base sur le seuil de 10%. Si plus de 10% des lignes ont été modifiées depuis la dernière collecte sur une table, les statistiques de la table sont considérées comme obsolètes. Cela est reflété dans la colonne STALE_STATS de la vue DBA_TAB_STATISTICS qui prend la valeur Y (Yes). La collecte automatique se chargera de refaire les statistiques sur cette table (voir ci-dessous).

Le seuil de 10% convient dans la plupart des cas mais peut être modifié depuis la version 11g d’Oracle en fonction de critères comme la taille de la table ou sa fréquence de mise à jour. Par exemple une table de 1000000 de lignes ne verra ses statistiques recalculées qu’après la mise à jour de 100000 lignes ce qui peut nécessiter une attente de plusieurs jours ou semaines et peut être un problème (pendant ce temps là, les valeurs maximum des colonnes peuvent augmenter significativement, par exemple une date basée sur SYSDATE). Un abaissement de ce seuil à 5% va entrainer un calcul plus fréquent des statistiques sur cette table. Utiliser pour cela la procédure dbms_stats.set_table_prefs.

Flush des informations de table monitoring

Pour des raison évidentes d’efficacité, le Table monitoring est fait en mémoire après chaque insert/update/delete. l’information mémoire est stockée périodiquement sur disque et peut être consultée via la vue DBA_TAB_MODIFICATIONS. La procédure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO vide les dernières informations encore en mémoire Le privilège système ANALYZE ANY est requis pour exécuter cette procédure.

Job de collecte des statistiques

Le cœur de la collecte des statistiques est constitué du Job Oracle. Celui-ci est actif par défaut après la création de toute base depuis Oracle 10g. Il y a eu des évolutions importantes en 11g.

Nous donnons des informations sur ce Job dans la partie II.