dimanche 20 décembre 2015

Les histogrammes (2): Frequency


Dans l’article précédent nous avons vu que la cardinalité pour la colonne C_FREQ était mal estimée par le CBO car il lui manquait l’information sur la distribution des valeurs de cette colonne :
select * from T1 where C_FREQ=3; 
 
------------------------------------------------------------------------------------ 
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | 
------------------------------------------------------------------------------------ 
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 35 | 
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4913 | 10 |00:00:00.01 | 35 | 
------------------------------------------------------------------------------------

Cette information peut être apportée en calculant un histogramme sur la colonne. Jusqu’à la version 11G on avait 2 types d’histogrammes : FREQUENCY et HEIGHT-BALANCED. Les histogrammes de type FREQUENCY vont stocker le nombre de lignes (la fréquence) de chaque valeur ce qui permet d’obtenir des cardinalités très fiables. Cependant ces histogrammes sont limités à des colonnes ayant moins de 254 valeurs distinctes.

Voici comment on calcule des statistiques avec histogrammes pour la colonne C_FREQ :

exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for columns C_FREQ size 3');

Le chiffre 3 indique que je souhaite utiliser 3 buckets puisqu’on a que 3 valeurs distinctes. Même si j'utilisais une valeur supérieure Oracle ne prendrait en compte que 3 buckets pour cette colonne. 
On peut vérifier qu’un histogramme de type FREQUENCY a bien été calculé en questionnant la table USER_TAB_COL_STATISTICS :
select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram  
from user_tab_col_statistics  
where table_name='T1' and column_name='C_FREQ'; 
 
COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM 
------------------------------ ------------ ---------- ---------- ----------- ----------- ---------- 
C_FREQ                                    3 ,000033924          0           3       14739 FREQUENCY


On peut également visualiser le contenu de l’histogramme en requêtant la vue USER_TAB_HISTOGRAMS : 
select endpoint_value as column_value, 
endpoint_number as cummulative_frequency, 
endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) as frequency 
from user_tab_histograms 
where table_name = 'T1' and column_name = 'C_FREQ'; 
 
COLUMN_VALUE CUMMULATIVE_FREQUENCY  FREQUENCY 
------------ --------------------- ---------- 
           1                 13257      13257 
           2                 14729       1472 
           3                 14739         10

La colonne ENDPOINT_VALUE correspond à la valeur de la colonne et la colonne ENDPOINT_NUMBER correspond au nombre cumulé de lignes. Ainsi, pour obtenir le nombre de lignes (la fréquence) pour la valeur 2 il suffit de soustraire au ENDPOINT_NUMBER de la valeur 2 le ENDPOINT_NUMBER de la valeur 1 (14729-13257=1472), d’où l’utilisation de la fonction LAG dans la requête ci-dessus.
On avait vu dans l’article précédent que sans histogrammes le CBO n’arrivait pas à estimer la cardinalité pour C_FREQ=3:
select * from T1 where C_FREQ=3; 
 
------------------------------------------------------------------------------------ 
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |      35 | 
|*  1 |  TABLE ACCESS FULL| T1   |      1 |   4913 |     10 |00:00:00.01 |      35 | 
------------------------------------------------------------------------------------

Voyons donc maintenant ce qu’estime le CBO avec l’histogramme FREQUENCY en place pour cette colonne :
alter system flush shared_pool; 
select * from T1 where C_FREQ=3; 
 
-------------------------------------------------------------------------------------------------- 
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
-------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT            |          |      1 |        |     10 |00:00:00.01 |       5 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |      1 |     10 |     10 |00:00:00.01 |       5 | 
|*  2 |   INDEX RANGE SCAN          | IDX_FREQ |      1 |     10 |     10 |00:00:00.01 |       3 | 
--------------------------------------------------------------------------------------------------

L’estimation match cette fois parfaitement la réalité (E-Rows=A-Rows=10). Du coup, le CBO opte pour un accès indexé et le nombre de logical reads est divisé par 5.

Cet exemple montre bien qu’un histogramme Frequency permet d’obtenir une cardinalité égale ou proche de la réalité. Cette justesse au niveau de l’estimation vient du fait que pour les histogrammes FREQUENCY il existe autant de buckets que de valeurs distinctes, ainsi, toutes les valeurs présentes dans la table sont populaires à condition bien sûr qu’un calcul de statistiques ait eu lieu au moment où cette distribution non uniforme des données était présente. En effet, imaginons une nouvelle ligne insérée dans la table T1 avec C_FREQ = 4, la valeur 4 n’étant pas présente dans l’histogramme FREQUENCY le CBO ne peut estimer correctement la cardinalité pour cette valeur. Il est donc primordial d’avoir un calcul de statistiques qui tourne toujours au juste moment.

Dans le prochain article nous verrons comment Oracle calcule les histogrammes HEIGHT-BALANCED pour répondre aux problématiques des colonnes de plus 254 valeurs distinctes.