Les index sont une arme redoutable à double tranchant. De sorte qu’une maladie assez répandue sur les bases de données Oracle est la sur-indexation ! Dans cet article, vous trouverez quelques réflexions à ce sujet et notamment :
- Des exemples de conséquences négatives de l’utilisation d’index
- Le monitoring des index pour lutter contre la sur-indexation
- L’impact de la collecte des statistiques avec
DBMS_STATSsur l’indexation - Une étude de l’impact du monitoring des index sur l’activité
Evidemment, ça ne vous permettra pas de construire le système optimal, mais c’est un petit pas pour le DBA…
Des conséquences négatives de l’indexation
Si on devait écrire une antologie des idées fausses de DBA, un bon tier serait surement dédié aux index. Rien qu’aujourd’hui, j’ai entendu, par exemple :
- toutes les tables doivent être indexées
- 2 index mono-colonnes sont plus efficaces qu’un index multi-colonnes
- il faut placer les colonnes de cardinalité les plus fortes en tête d’index
- il faut placer les colonnes de cardinalité les plus faibles en tête d’index pour améliorer la performance de l’algorithme INDEX SKIP SCAN
Bref, les index ressemblent pour moi à un remake de « destination finale »… Je peux vous dire à l’avance que je vais bientôt entendre une nouvelle horreur. Si vous ne devez retenir qu’une chose, retenez la parole de Tom Kyte : « it depends« .
Je ne ferai donc pas généralités. Je vais simplement vous montrer quelques exemples qui vous feront peut-être réfléchir à la pertinence des index.
Exemple 1: Insertions
Voici un script que j’ai exécuté sur mon ordinateur plusieurs fois. Toutes les données sont en cache, histoire d’éviter de me trainer des I/O d’un système sous optimal. J’utilise une base 11.2 sur Linux
drop table t purge;
create table t(col1 number,
col2 number);
-- décommenter pour les cas 1,2 ou 3 index
-- create index t_col1 on t(col1);
-- décommenter pour les cas 2 ou 3 index
-- create index t_col2 on t(col2);
-- décommenter pour le cas 3 index
-- create index t_cols on t(col2,col1);
set timing on
begin
for i in 1..100000 loop
insert into t(col1,col2) values (i,mod(i,100));
end loop;
commit;
end;
/
Voici un tableau récapitulatif des temps moyens en fonction du nombre d’index dans mon cas, quasiment de 1 à 3 :
| Nombre d’Index | Temps (secondes) |
|---|---|
| 0 | 3.42 |
| 1 | 5.85 |
| 2 | 6.64 |
| 3 | 9.51 |
Exemple 2 : Update
Ce second exemple est assez similaire au premier sauf qu’il met en oeuvre un update plutôt qu’un insert :
set timing off
drop table t purge;
create table t(col1 number,
col2 number);
begin
for i in 1..100000 loop
insert into t(col1,col2) values (i,mod(i,100));
end loop;
commit;
end;
/
-- décommenter pour les cas 1,2 ou 3 index
-- create index t_col1 on t(col1);
-- décommenter pour les cas 2 ou 3 index
-- create index t_col2 on t(col2);
-- décommenter pour le cas 3 index
-- create index t_cols on t(col2,col1);
set timing on
update t set col1=200000-col1, col2=200-col2;
set timing off
commit;
Voici un tableau récapitulatif des temps moyens en fonction du nombre d’index dans mon cas, quasiment de 1 à 4 :
| Nombre d’Index | Temps (secondes) |
|---|---|
| 0 | 2.54 |
| 1 | 4.40 |
| 2 | 8.31 |
| 3 | 11.79 |
Exemple 3 : Fragmentation, Rebuild et ESTIMATION a priori
Ce dernier exemple illustre que la fragmentation des index peut aller très vite. Pour cela, on reprend le script de l’exemple précédent :
set timing off
drop table t purge;
create table t(col1 number,
col2 number);
begin
for i in 1..100000 loop
insert into t(col1,col2) values (i,mod(i,100));
end loop;
commit;
end;
/
create index t_cols on t(col2,col1);
set timing on
update t set col1=200000-col1, col2=200-col2;
set timing off
commit;
Si on observe la taille effective de notre index à l’aide de DBMS_SPACE.SPACE_USAGE, on voit que celui-ci fait quasiment 6M :
variable unf number;
variable unfb number;
variable fs1 number;
variable fs1b number;
variable fs2 number;
variable fs2b number;
variable fs3 number;
variable fs3b number;
variable fs4 number;
variable fs4b number;
variable full number;
variable fullb number;
begin
dbms_space.space_usage(user,'T_COLS',
'INDEX',
:unf, :unfb,
:fs1, :fs1b,
:fs2, :fs2b,
:fs3, :fs3b,
:fs4, :fs4b,
:full, :fullb);
end;
/
select (:unfb+:fs4b+:fs3b+:fs2b+:fs1b+:fullb) / 1024 /1024 sizemb
from dual;
SIZEMB
--------
5.84375
Or si on estime la taille d’un index créé de zéro, le segment associé est de 3Mo comme vous pouvez le voir ci-dessous :
exec dbms_stats.gather_table_stats(user,'T');
exec dbms_space.create_index_cost ('create index t_cols on t(col2,col1)',:ub,:ab);
select :ab/ 1024 /1024 sizemb
from dual;
SIZEMB
------
3
Notes :
Au passage, il est très facile d’estimer le bénéfice d’un rebuild d’index. Il faut simplement utiliserdbms_space.create_index_costqui donne la taille de l’index lors de la création que vous pouvez comparer à la taille actuelle.
Il est nécessaire de collecter les statistiques avant d’estimer le taille de l’index puisqu’Oracle les utilise pour son estimation.
La fonction donne la taille du segment alors queDBMS_SPACE.SPACE_USAGEdonne les blocs effectivement utilisés. Vous pouvez regarder la colonnesBYTESdeDBA_SEGMENTSpour comparer des mesures réellement comparables.
Nous allons vérifier les hypothèses en reconstruisant l’index et en mesurant sa taille après cette défragmentation :
alter index t_cols rebuild online;
variable unf number;
variable unfb number;
variable fs1 number;
variable fs1b number;
variable fs2 number;
variable fs2b number;
variable fs3 number;
variable fs3b number;
variable fs4 number;
variable fs4b number;
variable full number;
variable fullb number;
begin
dbms_space.space_usage(user,'T_COLS',
'INDEX',
:unf, :unfb,
:fs1, :fs1b,
:fs2, :fs2b,
:fs3, :fs3b,
:fs4, :fs4b,
:full, :fullb);
end;
/
select (:unfb+:fs4b+:fs3b+:fs2b+:fs1b+:fullb) / 1024 /1024 sizemb
from dual;
SIZEMB
-------
2.1875
select bytes/1024/1024 sizemb
from user_segments
where segment_name='T_COLS';
SIZEMB
-------
3
Monitoring des index
Pour superviser l’utilisation des index, il suffit de lancer la commande alter index ... monitoring usage comme ci-dessous :
alter index t_cols monitoring usage;
Pour désactiver la supervision, exécutez :
alter index t_cols nomonitoring usage;
La vue V$OBJECT_USAGE vous indique instantanément quand un index est utilisé par un plan d’exécution. Pour les détails d’utilisation, regarder Oracle 11.2 Administrator’s Guide.
Notes :
La vueV$OBJECT_USAGEest assez mal faîtes ; elle ne peut être utilisée qu’à partir de l’utilisateur propriétaire du schéma. Lisez le blog d’Alex Gorbachev à ce sujet. Outre la possibilité d’interroger directement la tableSYS.OBJECT_USAGEplutôt queV$OBJECT_USAGEpour contourner le fonctionnement d’Oracle, vous pouvez également créer une fonctionAUTHID DEFINER(qui est la clause par défaut)
Pour la curiosité,V$OBJECT_USAGEest quasiment la seule vueV$qui ne soit pas une « fixed view » ; vous obtenez le SQL associés dansDBA_VIEWSet pas dansV$FIXED_VIEW_DEFINITION.
Idées fausses
Il circule plusieurs idées fausses à propos de la fonction de monitoring des index. Pour démontrer que ce n’est pas le cas, nous allons créer une table avec un index créé implicitement par la clé primaire :
drop table t purge;
create table t(col1 number,
col2 number);
begin
for i in 1..100000 loop
insert into t(col1,col2) values (i,mod(i,100));
end loop;
commit;
end;
/
alter table t add constraint t_pk primary key(col1);
alter index t_pk monitoring usage;
D’abord, tous les cas d’utilisation d’un index dans un plan et pas seulement ceux impliquant un accès par l’index sont supervisés comme vous pouvez le voir ci-dessous :
select count(*) from t;
col index_name format a10
select index_name, used
from v$object_usage;
INDEX_NAME USE
---------- ---
T_PK YES
Ensuite et c’est important pour vos analyses, la collecte des statistiques n’est pas incluse dans la supervision, même si l’index est accédé de fait :
alter index t_pk nomonitoring usage;
alter index t_pk monitoring usage;
exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
exec dbms_stats.gather_index_stats(user,'T_PK');
col index_name format a10
select index_name, used
from v$object_usage;
INDEX_NAME USE
---------- ---
T_PK YES
Quel est l’impact ?
Une question légitime est la question de l’impact de cette fonctionnalité en production. Là aussi, je vous laisse l’évaluer dans votre contexte mais voici quelques idées et tests généraux.
D’abord la collecte de l’information est réalisée lors d’un hard parse de la requête. La meilleur façon de le vérifier est d’utiliser les traces SQL (events 10046) et de vérifier le SQL imbriqué. Enfin toujours est-il que pour mesurer le minimum de ce qu’on peut mesurer, vous pouvez exécuter les 2 scripts suivants :
- Script soft-parse utilise toujours la même chaine et
to_char(1)
set timing on
declare
curid number;
query varchar2(1000);
begin
for i in 1..100000 loop
query:='select /* '||to_char(1)||' */ * from t where col1=1';
curid := dbms_sql.open_cursor();
dbms_sql.parse(curid, query,dbms_sql.native);
dbms_sql.close_cursor(curid);
end loop;
end;
/
- Script hard-parse utilise systématiquement des chaines différents et
to_char(i)
set timing on
declare
curid number;
query varchar2(1000);
begin
for i in 1..100000 loop
query:='select /* '||to_char(i)||' */ * from t where col1=1';
curid := dbms_sql.open_cursor();
dbms_sql.parse(curid, query,dbms_sql.native);
dbms_sql.close_cursor(curid);
end loop;
end;
/
Les résultats obtenus illustrent encore une fois l’intérêt de limiter le nombre de hard-parses mais aussi que l’impact du monitoring n’est pas forcément insupportable s’il est utilisé intelligeamment. Voici mon tableau de résultat :
| Soft Parses | Hard Parses | |
|---|---|---|
| NoMonitoring | 2.50 secs | 53.09 secs |
| Monitoring | 2.50 secs | 61.14 secs |
Conclusion
Evidemment, détecter les index non-utilisés n’est pas la panacée ; ça ne répond pas à la question que quel est l’intérêt d’un index qui pourrait être utilisé très rarement ou de quels index pourraient être avantageusement remplacés, même avec une petite dégradation du temps de réponse.
Il existe des approches plus fines qui s’appuient sur de l’échantillonnage et l’observation de V$SQL_PLAN ou de son historisation. Quoiqu’il en soit le monitoring est très simple à mettre en oeuvre et a un impact limité alors pourquoi pas le systématiser ? En première approche au moins.
Supprimez votre table pour en finir avec cet exemple :
drop table t purge;
