Bonjour,

Je pose beaucoup beaucoup de questions ces derniers jours et des âmes très généreuses me répondent et en plus prennent le temps de faire des réponses détaillées.

A mon tour d'aider les gens en répondant à une question non posée dans ce forum mais que beaucoup ont dû souvent se poser : comment voir si un index est utilisé ou non ET, SURTOUT, COMBIEN DE FOIS IL EST UTILISE!

J'ai galéré sur Internet, pour finalement trouver ici LA requête que je voulais :
http://arunkaushikoracle.blogspot.fr...ally-when.html

Je vais donc vous faire une synthèse de ce que j'ai lu.

Monitorer un index permet de voir s’il est utilisé ou non et donc, à terme, de décider sa suppression ou non.
La commande pour déclencher le monitoring sur un index est :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
ALTER INDEX <schema>.<index_name> monitoring usage;
Pour monitorer tous les index d’une base, sauf ceux des users système, on génère les commandes SQL avec l’ordre suivant puis on exécute le fichier sql créé :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
SET HEADING OFF FEEDBACK OFF PAGESIZE 0
spool 'P:\SPOOL_MONITORING_INDEX.sql';
SELECT 'ALTER INDEX "' || owner || '"."' || index_name || '" MONITORING USAGE;'
FROM   dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM') order by owner;
spool off;
La table de monitoring renseignée est V$OBJECT_USAGE.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
DESC V$OBJECT_USAGE
 
Name                                                  Null?                     Type                        
----------------------------------------- -------- ----------------------------
INDEX_NAME                                  NOT NULL            VARCHAR2(30)                
TABLE_NAME                                   NOT NULL            VARCHAR2(30)                
MONITORING                                                            VARCHAR2(3)                 
USED                                                                      VARCHAR2(3)                 
START_MONITORING                                                  VARCHAR2(19)                
END_MONITORING                                                     VARCHAR2(19)
C’est la colonne USED, qui est à NO par défaut, qui basculera à YES lors de la première utilisation de l’index.
Attention, cette table ne dit pas combien de fois l’index sera utilisé mais seulement s’il a été utilisé ou non. C'est justement ce point qui m'a fait galéré car je voulais absolument savoir si un index est beaucoup utilisé ou non.

Pour avoir le nombre de fois où un index a été utilisé, on peut utiliser l’ordre SQL suivant. A noter qu'on peut utiliser le champ timestamp disant quand l'ordre a été exécuté et donc calculer la fréquence mensuelle d’utilisation d'un index

Dans mon cas, le propriétaire de la table est ACCD_FRA et le nom de l'index est PK_IS_LOG (oui, je sais, c'est pas un beau nom...). J'ai fais deux select sur la table avec dans la clause where le champ indexé. Le résultat affiché est bien 2; coooooooooooooooool
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
select sum(executions), object_name 
from v$sql_plan_statistics_all
where operation = 'INDEX' and object_owner = 'ACCD_FRA' and object_name = 'PK_IS_LOG'
group by object_name
order by object_name
 
SUM(EXECUTIONS) OBJECT_NAME                   
--------------- ------------------------------
              2 PK_IS_LOG                     
1 row selected.

Une fois la période écoulée, on identifiera les index non utilisés avec la commande suivante et on décidera de leur sort .
Code : Sélectionner tout - Visualiser dans une fenêtre à part
select * from V$OBJECT_USAGE where USED = 'NO' order by table_name, index_name ;

Voilà, j'espère que cela vous sera utile!