Réponse à la question du monitoring des index : combien de fois mon index est utilisé
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:
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:
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:
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 :P
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 :P:P:P:P:P
Code:
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 :ptdr:.
Code:
select * from V$OBJECT_USAGE where USED = 'NO' order by table_name, index_name ;
Voilà, j'espère que cela vous sera utile!
Réponse à la question du monitoring des index : combien de fois mon index est utilisé
Ton script est intéressant .
au lieu de requêter sur v$sql_plan_statistics_all, regarde les vues historiques
du référentiel AWR sur un interval de temps avec DBA_HIST_SQL_PLAN .
