Oracle : Vérifier l'utilisation d'un index

Dans un schéma/application, il se peut que certains index soient inutilisés et ce, pour différentes raisons. Par exemple, il peut s'agir d'un index redondant (un autre index similaire à celui-ci) dont l'utilisation est inexistante ou encore parce que le prédicat (clause WHERE) ne fait jamais référence aux colonnes de cet index. Il ne faut jamais oublier qu'il y a un coût (overhead) pour Oracle à entretenir chacun de ces index lors d'opérations DML.

Toutefois, ce n'est pas parce qu'un index n'est pas utiliser pendant une journée ou une semaine qu'il est non nécessaire. Un rapport mensuel peut effectuer des sommaires sur certaines périodes et appeler cet index en apparence inutile. Sans cet index, un « full scan » de la table pourrait ralentir de beaucoup la progression du rapport.

Avec Oracle, il est possible de « monitorer » i.e. suivre l'utilisation d'index précis. Le document [ID 136642.1], accessible via MyOracleSupport (anciennement Metalink), explique une technique que je vais résumer ici.

-- Mettre l'index désiré en mode surveillance
alter index MON_INDEX monitoring usage;

-- Vérifier si l'index en cours de surveillance a été utilisé.
-- Ce sont les colonnes USED et MONITORING qui nous intéressent.
select * from v$object_usage where monitoring='YES' and used='YES';

-- Retirer l'index du mode surveillance
alter index MON_INDEX nomonitoring usage;

Prendre note qu'en activant cette surveillance, il y a une taxe (overhead) pour l'engin Oracle tant que la surveillance demeure active. On peut laisser cette surveillance pendant environ un mois et retirer les index au fur et à mesure qu'on réalise qu'ils sont utilisés. Je suis conscient que nous n'avons pas plus de détail sur l'utilisation (quelle fréquence? par qui? quels énoncés?) mais c'est tout de même un départ.

---

Et maintenant dans Oracle 11g, il est possible de rendre invisible un index aux yeux de l'optimizer d'Oracle et des utilisateurs. Ainsi, si on veut rendre visible à nouveau un index, c'est très rapide et ça ne demande pas de reconstruire l'index (peut être très long pour de gros volumes de données). Par contre, il est important de mentionner que les instructions DML continuent de mettre à jour les index invisibles.

-- Pour rendre invisible/visible un index, voici la commande à exécuter :
alter index MON_INDEX invisible;
alter index MON_INDEX visible;

-- Pour afficher la visibilité d'un index :
SELECT index_name, visibility
FROM user_indexes
WHERE index_name = 'MON_INDEX';

Par défaut, les plans d'exécution n'utiliseront pas les index invisibles. À noter qu'il existe un paramètre Oracle (également valide au niveau de la session) rendant « visible » les index invisibles pour l' « optimizer » :

OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE

Cela vous permet donc de tester dans un environnement de simulation l'effet de l'ajout ou d'une suppression d'un index. Ainsi, vous devriez être plus confiant le moment venu de supprimer un index en production.