c’est quoi un histogram ?
Un histogram est une structure oracle qui contient pour la colonne sur lequel il porte, le nombre d’occurrence pour chaque valeur de cette colonne, ceci permettra à l’optimiseur Oracle de savoir la répartition de chacune des valeurs, et ainsi de mieux décider de faire un scan total de la table ou utiliser un index pour l’accès à une valeur.
Voilà un exemple qui montre comment l’optimiseur se comporte vis a vis d’une colonne dont la distribution des valeurs n’est pas uniforme.
1- Sans Histogram
DROP TABLE t;
CREATE TABLE t(x NUMBER);
create index t_idx on t (x);
DECLARE i NUMBER;
BEGIN
DELETE FROM t;
FOR i in 1.. 15
LOOP
INSERT INTO t VALUES(2);
END LOOP;
FOR i in 1..100000
LOOP
INSERT INTO t VALUES(8);
END LOOP;
COMMIT;
END;
/
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'HR',
tabname => 'T',
estimate_percent => 100
);
end;
/
set autotrace traceonly explain
Prompt select x from t where x = 8;
select x from t where x = 8;
Prompt select x from t where x = 2;
select x from t where x = 2;
Résultats
select x from t where x = 8
Plan d'execution
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50008 | 146K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50008 | 146K| 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=8)
select x from t where x = 2
Plan d'execution
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50008 | 146K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50008 | 146K| 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=2)
2- avec Histogram
DROP TABLE t;
CREATE TABLE t(x NUMBER);
create index t_idx on t (x);
DECLARE i NUMBER;
BEGIN
DELETE FROM t;
FOR i in 1.. 15
LOOP
INSERT INTO t VALUES(2);
END LOOP;
FOR i in 1..100000
LOOP
INSERT INTO t VALUES(8);
END LOOP;
COMMIT;
END;
/
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'HR',
tabname => 'T',
estimate_percent => 100
);
end;
/
begin
dbms_stats.gather_table_stats
( ownname => 'HR',
tabname => 'T',
method_opt => 'for all indexed columns size 254',
cascade => TRUE
);
end;
/
set autotrace traceonly explain
Prompt select x from t where x = 8;
select x from t where x = 8;
Prompt select x from t where x = 2;
select x from t where x = 2;
Résultats
select x from t where x = 8
Plan d'execution
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99988 | 292K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 99988 | 292K| 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=8)
select x from t where x = 2
Plan d'execution
----------------------------------------------------------
Plan hash value: 2946670127
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 54 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_IDX | 18 | 54 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=2)
Est-il intéressant d’avoir un histogram sur une colonne non indexée ?
Un histogram n’est pas seulement utile pour des colonnes contenant des indexes, il peut l’être aussi pour des colonnes non-indexées, dans ce cas, ceci aidera l’optimiseur à mieux décider des ordres de jointures.
Un histogram est-il toujours utile sur une colonne ?
Non, un histogram n’est utile que s’il y a une grande disparité entre le nombre d’occurences des valeurs d’une colonnes.
Une colonne sur laquelle il y a une contrainte d’unicité ne nécessite pas un histogram puisque toutes les valeurs sont réparties uniformément : une occurrence par valeur.
Par contre les colonnes contenant des valeurs dont le nombre d’occurrences peut varier d’une facon significative, sont très sujettes à utiliser un histogram.
Comment décider de mettre ou pas un histogram sur une colonne ?
1- Il faut tout d’abord être sûr que la colonne est, ou susceptible d’être interrogée par un prédicat de filtrage, exemple : select x from t where x = 8;
pour cela on peut interroger la vue sys.col_usage$
SELECT o.object_name,
c.column_name,
u.equality_preds,
u.like_preds,
u.range_preds,
u.null_preds
FROM sys.col_usage$ u,
all_objects o,
all_tab_columns c
WHERE u.obj# = o.object_id
AND o.object_name = 'T'
AND c.column_id = u.intcol#
AND c.TABLE_NAME = o.object_name
si au moin l’un des champs : u.equality_preds, u.like_preds, u.range_preds, u.null_preds
remonte une valeur >0 ; cela veut dire que la colonne est candidate.
2- vérifier que le nombre d’occurrence des valeurs n’est pas uniformément distribué dans la colonne , pour cela , on peut calculer l’écart-type des occurrences de valeurs
SELECT ROUND(STDDEV(y) *100 / SUM(y), 0)
FROM
(SELECT COUNT(x) y
FROM t
GROUP BY x)
Si le resultats est superieur a 50, l’histogram est utile