Depuis … Poulala, oracle s’appuie sur des statistiques plutôt que sur l’heuristique1 pour optimiser ses plans d’exécution. De version en version, les statistiques dont dispose Oracle s’étoffent offrant ainsi à oracle de plus en plus d’informations sur les données stockées en base.
Les statistiques sont essentiellement stockées au niveau du dictionnaire de donnée dans les tables décrivant les divers segments qu’oracle est capable de manipuler mais elles peuvent aussi être calculées à la volée et sur échantillonnage. Elles sont utilisée entre autre par l’optimiseur de requête d’Oracle lorsqu’il analyse une requête dans le but de produire un2 plan d’exécution efficace dans un temps raisonnable3.
A partir de ces statistiques, oracle associe un coût à un texte SQL par un algorithme complexe et amélioré à chaque nouvelle version d’Oracle qui a été décortiqué pour certaines d’entre elles par l’éminent Jonatan Lewis dans le très vendu et encore plus lu Cost Based Oracle fundamentals lui même rédigé en langue de Shakespeare et malheureusement resté jusqu’à ce jour sans traduction française. Cet ouvrage donne une formule qui permet de calculer certains coûts à partir des statistiques Oracle, reste encore à savoir où les trouver et sur ce dernier point c’est aux table de la liste suivante (et plus particulièrement les vertes qu’il conviendra de s’intéresser.
- Sur les tables
- [ALL|DBA|USER]_TABLES4
- [ALL|DBA|USER]_ALL_TABLES
- [ALL|DBA|USER]_TAB_PARTITIONS
- [ALL|DBA|USER]_TAB_SUBPARTITIONS
- [ALL|DBA|USER]_TAB_STATISTICS
- Sur les colonnes
- [ALL|DBA|USER]_TAB_COLUMNS
- [ALL|DBA|USER]_TAB_COLS
- [ALL|DBA|USER]_PART_COL_STATISTICS
- [ALL|DBA|USER]_SUBPART_COL_STATISTICS
- [ALL|DBA|USER]_TAB_COL_STATISTICS
- Sur les index
- [ALL|DBA|USER]_INDEXES
- [ALL|DBA|USER]_IND_STATISTICS
- [ALL|DBA|USER]_IND_PARTITIONS
- [ALL|DBA|USER]_IND_SUBPARTITIONS
- Les histogrammes
- [ALL|DBA|USER]_TAB_HISTOGRAMS
- [ALL|DBA|USER]_PART_HISTOGRAMS
- [ALL|DBA|USER]_SUBPART_HISTOGRAMS
- Statistiques en attente
- [ALL|DBA|USER]_IND_PENDING_STATS
- [ALL|DBA|USER]_TAB_PENDING_STATS
- [ALL|DBA|USER]_COL_PENDING_STATS
- Statistiques objet
- [ALL|DBA|USER]_NESTED_TABLE_COLS
- [ALL|DBA|USER]_OBJECT_TABLES
Les vues vertes (celles qui finissent par STATISTICS) et les vues sur les histogrammes sont les vues pertinentes pour accéder aux statistiques.
Les vues [ALL|DBA|USER]_TAB_STATISTICS et [ALL|DBA|USER]_IND_STATISTICS reposent sur les tables TAB_STATS$ et IND_STATS$ qui sont apparues aux dictionnaire oracle avec la version 10g tandis que les vues [ALL|DBA|USER]_PART_COL_STATISTICS, [ALL|DBA|USER]_SUBPART_COL_STATISTICS et [ALL|DBA|USER]_TAB_COL_STATISTICS reposent sur la table COL$ beaucoup plus ancienne et surtout aussi mise en jeu dans les vues [ALL|DBA|USER]_TAB_COLS et [ALL|DBA|USER]_TAB_COLUMNS.
Au final, on distingue trois grandes classes de statistiques
- Celles sur les tables
- Celles sur les index
- Celles sur les colonnes qu’on distinguera en deux sous catégories :
- Les statistiques moyennes
- Les histogrammes
Contenu des statistiques
Sur les tables
Les statistiques sont :
- Le nombre de ligne (NUM_ROWS)
- Le nombre de blocs5 utilisés(BLOCKS)
- Le nombre de blocs vides et jamais utilisés donc au delà du dernier bloc écrit (EMPTY_BLOCKS)
- L’espace libre moyen par bloc en octets(AVG_SPACE)
- Le nombre de lignes réparti sur plusieurs blocs (CHAIN_CNT)
- Taille moyenne d’une ligne de données en octets (AVG_ROW_LEN)
- Taille moyenne de l’espace libre qui peut être alloué dans les blocs en octets (AVG_SPACE_FREELIST_BLOCKS)
- Nombre de blocs allouables (NUM_FREELIST_BLOCKS)
- Nombre moyen de blocs dans le cache (AVG_CACHED_BLOCKS)
- Pourcentage de blocs directement lus en cache par les requêtes (AVG_CACHE_HIT_RATIO)
Ces deux dernières mesures, celles qui concernent le cache, ne sont pour le moment pas utilisées.
Sur les index
- Profondeur de l’arbre6 (BLEVEL)
- Nombre de blocs feuilles (LEAF_BLOCKS)
- Nombre de clé distinctes (DISTINCT_KEYS)
- Nombre moyen de blocs par clé (AVG_LEAF_BLOCKS_PER_KEY)
- Nombre de bloc de données par clé (AVG_DATA_BLOCKS_PER_KEY)
- Affinité de l’index avec la table indexée (CLUSTERING_FACTOR)
- Nombre de lignes indexées7 (NUM_ROWS)
- Nombre moyen de blocs dans le cache (AVG_CACHED_BLOCKS)
- Pourcentage de blocs directement lus en cache par les requêtes (AVG_CACHE_HIT_RATIO)
Ces deux dernières mesures, celles qui concernent le cache, ne sont pour le moment pas utilisées.
Sur les colonnes
Les statistiques moyennes
- Nombre de valeurs distinctes (NUM_DISTINCT)
- Valeur la plus basse (LOW_VALUE)
- Valeur la plus haute (HIGH_VALUE)
- Nombre moyen de ligne par valeur distinctes (DENSITY)
- Nombre de lignes non valuées (NUM_NULLS)
Les histogrammes
Les histogrammmes sont utilisés pour décrire la distribution des valeurs d’une colonne. Pour chaque colonne d’une table pour laquelle un histogramme a été calculé, on retrouvera les indications suivantes :
- Numéro de colonne de l’histogramme8 (ENDPOINT_NUMBER)
- Valeur numérique pour cette colonne d’histogramme (ENDPOINT_VALUE)
- Valeur réelle pour cette colonne d’histogramme (ENDPOINT_ACTUAL_VALUE)
Les statistiques sont déduites de ces colonnes. Leur fonctionnement est décrit dans l’article Comprendre les histogrammes en 11gR2 que j’avais écrit sur le blog de mon ancien employeur.
1. Un ensemble de règles, donc.
2. Et donc pas le plan le plus efficace
3. Et (re-)donc pas le plus rapidement possible
4. [ALL|DBA|USER]_TABLES : lire ALL_TABLES ou DBA_TABLES ou USER_TABLES
5. On parle de blocs Oracle et non de blocs OS
6. Oracle stocke ses index dans des arbres (même les index dits “bitmap”)
7. Les lignes non valuées ne sont pas indexées
8. On parle bien de la colonne de l’histogramme et non pas de celle de la table.