Discussion :

[Ikebukuro]

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!

[Pomalaix]

... 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 ...

Il faut rester prudent sur la signification des informations qu'on peut déduire de cette vue.
Elle est non seulement volatile (repart à vide à chaque redémarrage de la base), mais c'est une vue qui n'assure aucune persistance garantie des entrées, car elles sont soumises à un algorithme de gestion de cache.

Donc un index fondamental, qui sert intensivement à un traitement de nuit, peut tout à fait ne pas ressortir dans votre requête lancée chaque jour à 10 heures.

A l'inverse, MONITORING USAGE ne fournit pas d'information numérique, mais le OUI ou NON qu'on obtiendra dans V$OBJECT_USAGE est censé être parole d'évangile.

[Ikebukuro]

Il faut rester prudent sur la signification des informations qu'on peut déduire de cette vue.
Elle est non seulement volatile (repart à vide à chaque redémarrage de la base), mais c'est une vue qui n'assure aucune persistance garantie des entrées, car elles sont soumises à un algorithme de gestion de cache.

Bonjour Pomalaix,

Peux-tu me dire d'où tu sors ces informations sur cette vue car cela m'intéresse beaucoup. Par avance merci.

[dell68]

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 .

[Pomalaix]

Quand on a un filet à grosses mailles conçu pour attraper des thons, il ne faut pas s'attendre à remonter aussi des sardines !

Avant de choisir telle ou telle vue pour répondre à un certain besoin, il faut déjà savoir ce que contient véritablement cette vue.
Comment elle est alimentée, la persistance de ce qu'elle contient, son caractère exhaustif ou non, la période prise en compte dans ces données.

Si on prend par exemple V$SYSSTAT, on y trouve des statistiques d'activité qui sont des cumuls depuis le démarrage de l'instance.

• Persistance : uniquement en mémoire
• Exhaustivité : tout ce qui a eu lieu dans l'instance est comptabilisé
• Période prise en compte : depuis le démarrage de l'instance jusqu'au moment présent

V$SQL_PLAN_STATISTICS_ALL est une vue basée sur la zone mémoire "shared pool", qui est fondamentalement une zone de cache. Cela veut dire qu'on trouve dans cette zone prioritairement les requêtes le plus fréquemment/récemment utilisées ; les autres sont purgées dès que nécessaire.

• Persistance : uniquement en mémoire
• Exhaustivité : incertaine, aucune garantie
• Période prise en compte : critère non appliquable, puisque la rétention dépend de la popularité de chaque requête et pas seulement d'un critère temporel

DBA_HIST_SQL_PLAN est une vue basée sur le référentiel AWR (soumis à option payante).
A chaque prise de cliché AWR, pour chaque critère pris en compte, les N principales requêtes présentes en "shared pool" vont être ajoutées à cette vue.
N est égal à 30 par défaut (dépend du paramétrage, celui-ci est visible dans DBA_HIST_WR_CONTROL, colonne TOPNSQL).
Les critères sont par exemple : elapsed time, CPU time, etc.

• Persistance : sur disque
• Exhaustivité : seulement les requêtes faisant partie du TOP N sur au moins 1 critère, et qui étaient présentes en shared pool au moment du cliché
• Période prise en compte : ???. Cette vue ne semble pas soumise à la rétention générale des données AWR (une semaine glissante par défaut). Je n'ai pas réussi à trouver d'informations sur ses critères de purge.

Conclusion : ni V$SQL_PLAN_STATISTICS_ALL ni DBA_HIST_SQL_PLAN ne permettent de dire avec certitude si un index a été utilisé, ni combien de fois.
En revanche, pour des requêtes fréquentes, et qui de ce fait auront une bonne persistance en cache, l'information aura une meilleure fiabilité, et aura au moins une valeur indicative, permettant de comparer des ordres de grandeur.