Les histogrammes associés aux statistiques

Les histogrammes sous Oracle permettent, d'une manière générale, de connaître une valeur résultante pour un intervalle de données sources défini.
Il existe, par exemple, un histogramme afin de prendre en compte l'impact d'un paramètre sur les performances générales. Cependant, cet article ne parlera que des histogrammes d'hétérogénéité de valeurs dans une (ou plusieurs) colonne(s) d'une table.

On parle d'histogramme car les valeurs peuvent être représentées dans un graphique du même nom, où se situent, en absisse l'intervalle de valeurs d'une colonne, et en ordonnée, le nombre associé. Pour que l'histogramme soit humainement représentatif, les intervalles sont généralement de même grandeur (c'est ce qu'on peut notamment obtenir avec la fonction WIDTH_BUCKET).

Lorsqu'une colonne est distribuée de manière homogène, l'histogramme se présentera alors sous la forme d'éléments de même hauteur. On parle alors de colonne dont les valeurs sont équilibrées.
A l'inverse, lorsque la distribution est hétérogène, la colonne (ou l'index) est alors déséquilibrée ; c'est particulièrement dans ce cas présent que la présence d'histogrammes (au sens statistique Oracle) permettra au moteur Oracle d'adapter son plan d'exécution le plus finement possible.


Soit la table personne décrite dans un article précédent.
Créons un index sur le prénom :
SQL> create index I_PERS_PRENOM on personne (prenom);

Index created.

Ajoutons des données supplémentaires afin de déséquilibrer l'index.
begin
for i in 1 .. 100 loop
insert into personne (nom, prenom) values (i, 'A'|| i);
end loop;
end;
/

Passons les statistiques en créant un histogramme sur les colonnes indexées :

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'HR',
tabname => 'PERSONNE',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 10'
);
end;
/

Voyons à présent comment se comporte Oracle en fonction du critère de recherche :

SQL> set autot trace exp;
SQL> select * from personne where prenom > 'B';

Execution Plan
----------------------------------------------------------
Plan hash value: 593540522

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 100 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| PERSONNE | 10 | 100 | 2 (0
|* 2 | INDEX RANGE SCAN | I_PERS_PRENOM | 10 | | 1 (0
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PRENOM">'B')



SQL> select * from personne where prenom > 'A';

Execution Plan
----------------------------------------------------------
Plan hash value: 2727463210

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105 | 1050 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSONNE | 105 | 1050 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PRENOM">'A')



Ici, Oracle juge que l'index n'apportera pas de gain significatif, il préfère alors utiliser un full table.

Mais alors quid des bind variables ?

SQL> variable pe char;
SQL> execute :pe := 'A';

PL/SQL procedure successfully completed.

SQL> select * from personne where prenom > :pe;

Execution Plan
----------------------------------------------------------
Plan hash value: 593540522

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 50 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| PERSONNE | 5 | 50 | 2 (0
|* 2 | INDEX RANGE SCAN | I_PERS_PRENOM | 2 | | 1 (0
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PRENOM">:PE)



SQL> begin
2 :pe := 'A';
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select * from personne where prenom > :pe;

Execution Plan
----------------------------------------------------------
Plan hash value: 593540522

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 50 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| PERSONNE | 5 | 50 | 2 (0
|* 2 | INDEX RANGE SCAN | I_PERS_PRENOM | 2 | | 1 (0
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PRENOM">:PE)

Dans le doute, on remarque qu'Oracle prend en compte l'index. Plus précisément, on assistera au phénomène de bind peeking (prise en compte du premier plan d'exécution généré avec les bind).

A noter cependant, qu'à partir de la 11g, Oracle peut construire plusieurs plans d'execution pour une même requête (avec des valeurs différentes pour leur bind variables associées), il s'agit de l'adaptive cursor sharing.


Reprenons l'exemple, cette fois en réduisant nos histogrammes à 2 et en déséquilibrant encore plus la colonne en reprenant la boucle, non pas sur 100 insertions, mais sur 1000 :
delete from personne where prenom like 'A%';
100 rows deleted.

begin
for i in 1 .. 100 loop
insert into personne (nom, prenom) values (i, 'A'|| i);
end loop;
end;
/


BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'HR',
tabname => 'PERSONNE',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 2'
);
end;
/


SQL> select * from personne where prenom like 'A%';

Execution Plan
----------------------------------------------------------
Plan hash value: 2727463210

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 529 | 5819 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSONNE | 529 | 5819 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PRENOM" LIKE 'A%')

SQL> select * from personne where prenom like 'N%';

Execution Plan
----------------------------------------------------------
Plan hash value: 2727463210

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 363 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSONNE | 33 | 363 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PRENOM" LIKE 'N%')

SQL> select * from personne where prenom like 'V%';

Execution Plan
----------------------------------------------------------
Plan hash value: 593540522

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0
| 1 | TABLE ACCESS BY INDEX ROWID| PERSONNE | 1 | 11 | 3 (0
|* 2 | INDEX RANGE SCAN | I_PERS_PRENOM | 1 | | 2 (0
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PRENOM" LIKE 'V%')
filter("PRENOM" LIKE 'V%')

SQL> select * from personne where prenom like 'A11%';

Execution Plan
----------------------------------------------------------
Plan hash value: 593540522

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0
| 1 | TABLE ACCESS BY INDEX ROWID| PERSONNE | 1 | 11 | 3 (0
|* 2 | INDEX RANGE SCAN | I_PERS_PRENOM | 1 | | 2 (0
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PRENOM" LIKE 'A11%')
filter("PRENOM" LIKE 'A11%')


Oracle ne peut alors pas déterminer avec suffisamment de précision la pertinence de l'index. Il n'a que 2 catégories : une partie où la condition génèrera trop de lignes (compris entre 0 et P) et une où la valeur est suffisamment discriminante (entre Q et z).
La taille spécifiée lors de l'appel à DBMS_STATS.GATHER_TABLE_STATS aura son importance. Cette taille peut être spécifiée de différente manière :
- un nombre correspondant au nombre d'intervalles construits (entre 1 et 254),
- AUTO, dans ce cas, Oracle évalue lui-même le nombre d'intervalles,
- REPEAT : reprend le nombre d'intervalle précédemment définit (par un nombre ou en automatique), si c'est la première fois, aucun histogramme ne sera créé.
- SKEWONLY : indique à Oracle de ne collecter les stats que sur la distributions des données (pas de prise en compte de l'utilisation de la (des) colonne(s))


SQL> select ENDPOINT_NUMBER, ENDPOINT_VALUE from user_histograms where table_name= 'PERSONNE' and column_name = 'PRENOM';

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 3.3849E+35
1 3.3858E+35
2 4.1752E+35

3 rows selected.

alors qu'avec un histogramme de taille 10 :
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 3.3849E+35
1 3.3850E+35
2 3.3852E+35
3 3.3854E+35
4 3.3856E+35
5 3.3858E+35
6 3.3860E+35
7 3.3862E+35
8 3.3864E+35
9 3.3866E+35
10 4.1752E+35

11 rows selected.


On remarque qu'oracle ne stocke pas les histogrammes sous forme d'un tableau comprenant le nombre de données dans chaque intervalle, mais définit les bornes des intervalles pour obtenir une répartition homogène dans chacun d'eux.

SQL> select column_name, HISTOGRAM from all_tab_cols where table_name like 'PERSONNE';


COLUMN_NAME HISTOGRAM
------------------------------ ---------------
DATE_DECES NONE
DATE_NAISSANCE NONE
PRENOM HEIGHT BALANCED
NOM HEIGHT BALANCED
CIVILITE FREQUENCY




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

Catégories