Histogrammes en 11gR2
Les performances d’Oracle reposent sur l’analyse des requêtes SQL au travers du Cost Based Optimizer (CBO) qui s’appuie sur les statistiques sur les divers objets de la base. Toute le problématique des statistiques Oracle est d’avoir une représentation la plus fidèle possible des données en base en un minimum d’informations. La solution proposée par Oracle pour le faire est d’utiliser les histogrammes.
Au niveau des tables et sans s’occuper des problématiques de partitionnement les statistiques sont visibles dans des vues diverses qui vont donner des informations au niveau de la table (par exemple DBA_TABLES, ALL_TAB_STATISTICS, USER_TABLES …) puis pour les colonnes des tables (USER_TAB_COLUMNS, DBA_TAB_COL_STATISTICS …) et enfin pour certaines colonnes un histogramme est créé et visible dans des vues telles que DBA_COL_HISTOGRAMS. Reste à juger de leur pertinence afin de décider si oui ou non on en a besoin pour telle ou telle colonne.
Frequency Histograms
Un petit jeu de test ?
create table t0 ( c1 number ); insert into t0 select abs(ceil(dbms_random.normal*3)) from sys.dual connect by rownum <= 1E5 ; exec dbms_stats.gather_table_stats(user, 'T0', method_opt=>'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)
On effectue un Explain plan pour toutes les requête dont le prédicat est C1=x où x varie de -2 à 15 que l’on compare au nombre effectif d’occurrence de chaque valeur ( count(*) group by C1 ).
Résultat
En bleu l’histogramme, en rouge le nombre effectif d’occurrences des valeurs données en abscisse … On a ici un histogramme qui représente parfaitement le contenu de la colonne C1, et qui contredit la documentation officielle du fait qu’il ne décroît pas progressivement au delà des valeurs minimales et maximales.
Pourquoi ?
L’histogramme a été calculé sur un échantillon de 100 %. Le CBO le considère comme “juste” en l’occurrence à raison, mais dans deux semaines ? Au bout de deux semaines aussi, et c’est nettement moins agréable.
Si on laisse Oracle choisir l’échantillon, ça donne quoi ?
exec dbms_stats.gather_table_stats(user, 'T0')
ATTENTION: L’échelle est logarithmique. Mais là on a bien ce que décrit la documentation. Et l’histogramme garde sa pertinence.
Height Balanced Histograms
En résumé: Les Frequency histograms c’est génial mais ça prend de la place donc si le nombre de valeur distinctes d’une colonne est trop important il faut trouver une autre solution pour rendre compte de la répartition non uniforme des données et ne pas trop encombrer le dictionnaire des données; c’est là qu’intervient le second type d’histogramme appelé height balanced histogram.
Oracle a décidé que le nombre maximal de valeurs distinctes qu’il allait stocker pour une colonne était de 254, au delà ou si on demande de calculer l’histogramme pour moins de valeurs qu’il n’en existe pour la colonne (non documenté) l’histogramme devient height balanced.
Un nouveau petit jeu de test ?
create table t1 ( c1 number );
insert /*+ APPEND */ into t1
select ceil(dbms_random.normal*35)
from sys.dual
connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)
create table t2 ( c1 number );
insert /*+ APPEND */ into t2
select (150-abs(c))*sign(c+0.1)
from (select ceil(dbms_random.normal*35) c
from sys.dual
connect by rownum <=1E5 ) ;
On obtient les répartitions de données suivantes :
Cardinalités sans histogramme
Si on calcule les statisitques sur la table en ne calculant pas l’histogramme on obtient la distribution de cardinalités suivantes (en vert):
Comme on pouvait s’y attendre, Oracle considère que chaque valeur est représentée autant que les autres. A noter tout de même: avant la valeur minimale et au delà de la valeur maximale détectée, le CBO estime une cardinalité progrssivement décroissante de la valeur moyenne calculée jusuq’à une valeur de 1. La raison en est que les statistiques n’ont pas vocation à être calculées en permanence et que donc, la table vivant, de nouvelles valeurs peuvent apparaitre.
Histogramme calculé sur un échantillon de 100% et pour 254 buckets
0n obtient la distribution de cardinalités suivantes (en bleu):
On constate :
- Que l’histogramme fonctionne par palliers
- Que dans chaque pallier chaque valeur n’est pas équitabement représentée.
- Que l’estimation d’une cardinalité est au pire égale à l’estimation sans histogramme.
Histogramme calculé sur un échantillon de 100% et on laisse à Oracle le choix du nombre de buckets
On constate :
- Peu de différence avec la distribution précédente
Histogramme calculé sur un échantillon et pour un nombre du buckets nombre de buckets dont les choix sont laissés à Oracle
On constate :
- Cette fois ci, trois palliers
- Une moins bonne correspodance des cardinalités estimées avec les cardinalités réelles
Globalement sur ces tests
Les histogrammes sont assez représentatifs des données (ouf !) mais peut-être que ça vient de la forme de la courbe, d’où l’idée d’essayer avec une courbe ‘inversée’.
On recrée le deuxième type d’histogramme (100% buckets auto)
On constate :
- Une estimation aussi “bonne” ou “mauvaise” que l’estimation faite dans les mêmes conditions sur l’autre répartion de données.
Comment un histogramme est-il calculé ?
les données de la colonne sont ordonnées et le résultat découpé en autant de buckets que vous ou Oracle en avez décidé. A partir de là à chaque point de rupture entre deux buckets, la valeur est enregistrée.A chaque bucket correspond donc le nombre de lignes valuées de la table divisé par le nombre du buckets. A une valeur de la colonne qui est relevée correspond une cardinalité estimée au nombre de fois où elle a été relevée multiplié par le nombre de lignes que contient un bucket
Est-il possible de faire mieux ?
Oui mais c’est très coûteux: en effet, si on fait du dynamic sampling on obtient ça:
Seulement, du coup, le temps de parsing est très fortement allongé et les lectures disques augmentées
Limites des histogrammes
Le calcul des histogrammes parait un bon compromis, cependant il est des cas où il s’avère inutile. Le graphe ci-après représente l’histogramme et les valeurs calculées sur une table de 10 millions de lignes sur une colonne présentant près de 1600 valeurs distinctes.
On constate :
- L’estimation faite est équivalente à celle qui serait survenue en l’absence d’histogramme. Si le calcul de l’histogramme n’est pas néfaste, il reste contre-productif.
Jusqu’à maintenant les histogrammes étaient calculés sur des colonnes de type numérique. Si on calcule un histogramme sur une colonne de type varchar2 on peut rencontrer un soucis dans le cas où un grand nombre de valeurs distinctes ont leur 32 premiers caractères en commun.
create table t6 ( c1 varchar2(64) ); insert /*+ APPEND */ into t6 select 'Foo Bar Baz Foo Bar Baz Foo Bar Baz '||to_char(ceil(dbms_random.normal*35)) from sys.dual connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)
On constate :
- L’estimation faite est égale au nombre de lignes de la table et donc totalement hors de propos.
Conclusion
Les histogrammes représentent un moyen intéressant d’avoir une estimation de la cardinalité réelle d’une occurrence de valeur pour une colonne donnée. Cependant, si les valeurs sont trop nombreuses ou pour certains types de données trop centrées autour d’une même valeur les statistiques calculées seront au mieux inutiles et au pire complètement erronées.
PS
Cet article est paru précédemment sur le site de mon ancien employeur.