Après plusieurs mois d’existence, il arrive souvent que l’on constate une augmentation sensible des temps de mise à jour de certains pans de l’application, sans que la restitution des informations ne soit impactée. Et cela sans que les volumes traités n’aient évolué.
On peut alors se poser la question, est ce qu’il n’y a pas trop d’indexes sur certaines tables et surtout, les indexes mis en place sont ils tous utiles.
S’il est facile de retrouver les tables supportant un grand nombre d’indexes, il n’est pas toujours aisé de retrouver les indexes qui ne sont pas utilisés, sauf si on a de bons souvenirs de son cours de Tuning.
Pour ceux qui auraient oublié ou qui n’auraient pas suivi le dit cours, voici la méthode à suivre pour retrouver les indexes inutilisés dans un schéma particulier.
Je parts du principe que les clefs primaires et les clefs uniques ont une utilité fonctionnelle au niveau du schéma de données et sont donc utiles.
Mis en place de l’audit sur les indexes
On commence  par se connecter en sysdba sur la base de données  puis on exécute la requête suivante dans un spool afin de retrouver l’ensemble des indexes mis en place sur les tables non vide. Pour avoir un résultat cohérent, il va de soit que les statistiques doivent être calculées régulièrement.

select 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
from (
select i.owner , index_name
from   dba_indexes i,dba_tables t
where i.owner='MON_SCHEMA'
and  t.owner=i.table_owner
and  t.table_name=i.table_name
and  t.num_rows!= 0
minus
select owner , index_name
from dba_constraints
where owner = 'MON_SCHEMA'
and   CONSTRAINT_TYPE in ('P','U')
);

On jouera ensuite le fichier ainsi généré toujours en tant que sysdba
Le temps durant lequel on va laisser tourner l’audit dépendra de l’activité de l’application, a savoir s’il y a une activité particulière de façon hebdomadaire, mensuelle, trimestrielle … Mais de manière générale, une semaine me semble le minimum. D’autant que l’impact de l’audit sur les performances de la base de données est très faible.
Arrêt du processus d’audit
Une fois la période d’audit terminée, on arrête le processus de collecte des données.
Toujours connecté en tant que sysdba, on spoole  la requête suivante:

select 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;'
from (
select i.owner , index_name
from   dba_indexes i,dba_tables t
where i.owner='MON_SCHEMA'
and  t.owner=i.table_owner
and  t.table_name=i.table_name
and  t.num_rows!= 0
minus
select owner , index_name
from dba_constraints
where owner = 'MON_SCHEMA'
and   CONSTRAINT_TYPE in ('P','U')
);

 
On jouera ensuite le fichier ainsi généré toujours en tant que sysdba
Récupération du résultat
Pour récupérer le résultat de l’audit, il faut lire le contenu de la table v$object_usage. Mais petite subtilité, le nom de schéma ne figurant pas dans la table, il est indispensable de se connecter sur le schéma en question pour pouvoir visualiser le contenu de cette table.

Alter session set current_schema=MON_SCHEMA
SELECT  i.INDEX_NAME , TABLE_OWNER, i.TABLE_NAME , DISTINCT_KEYS ,NUM_ROWS
FROM v$object_usage o,
user_indexes i
WHERE used = 'NO'
and  i.INDEX_NAME=o.INDEX_NAME;

Voila, on a récupéré la liste des indexes n’ayant pas été utilisés lors du requêtage sur la base sur la période de l’audit. Et cela sans avoir au préalable avoir eue besoin de faire des recherches fastidieuse pour avoir a cibler tel ou tel requête dans tel ou tel module applicatif et avoir posé des traces pour analyser un grand nombre de plan d’exécutions.