Introduction
Si vous avez déjà utilisé l'outil de monitoring des index d'Oracle, vous avez dû être effarés devant la pauvreté de celui-ci. Oracle n'indique en effet que si Oui ou Non un index est utilisé durant la période de monitoring mais pas le nombre de fois qu'on l'utilise. Dans le cas où vous voulez supprimer des index d'une table qui en a trop parce qu'ils ralentissement les INSERT et DELETE, la première chose à faire est de voir si ces index sont utilisés ou non mais aussi combien de fois ils le sont avant de prendre une décision.



Points d'attention
NA.


 
Base de tests
N'importe quelle base Oracle.



Exemples
============================================================================================
Le monitoring des index existant.
============================================================================================

La vue de monitoring des index est V$OBJECT_USAGE et la commande pour déclencher ce monitoring est la suivante :

          SQL> ALTER INDEX <schema>.<index_name> monitoring usage;

          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)

Attention : dans V$OBJECT_USAGE le user n'apparait pas! D'après mes tests, si SYS met en place le monitoring sur un index du schéma HR et fait un SELECT utilisant l'index monitoré (on le voit avec AUTOTRACE), la table V$OBJECT_USAGE n'est pas mise à jour. C'est uniquement si le user HR fait des SELECT utilisant ses index que le monitoring est déclenché. Cela mériterait une analyse plus fine mais ce n'est pas l'objet de ce post.

Faisons un test de monitoring.
          SQL> alter index hr.PK_ZZ01 MONITORING USAGE;
          SQL> select * from hr.zz01 where id < 20;
          SQL> select * from V$OBJECT_USAGE;
          INDEX_NAME    TABLE_NAME    MONITORING    USED    START_MONITORING    END_MONITORING
          -------------------------------------------------------------------------------------------------------------------------------------------------------
          PK_ZZ01             ZZ01                   YES               YES      05/21/2017 10:34:51   

C'est la colonne USED, valant NO par défaut, qui basculera à YES lors de la première utilisation de l'index. Attention, je me répète mais cette table ne dit pas combien de fois l'index sera utilisé mais seulement s'il a été utilisé ou non.

Et c'est donc tout l'objet de ce post : améliorer le monitoring des index en utilisant les infos à notre disposition dans le dictionnaire de données Oracle. Car, vous avez compris, que les infos affichés par Oracle ne sont pas suffisamment pertinentes pour nos besoins! NO signifie : l'index monitoré n'a pas été utilisé durant la période d'observation mais en aucun cas que cet index ne sera jamais utilisé, notamment lors d'un traitement trimestriel, semestriel ou annuel. A l'inverse, YES veut dire que
l'index monitoré a été utilisé durant la période d'observation MAIS combien de fois? S'il est utilisé une fois par mois ou 100 000 fois par jour cela n'a rien à voir!

Dans tous les cas, si on veut supprimer des index, il faut le faire de concert avec les développeurs mais pour cela il faut venir avec des informations plus détaillées que celles par défaut d'Oracle.


============================================================================================
La vue V$SQL_PLAN : là que se trouvent les infos sur l'utilisation des index.
============================================================================================

Le plus gros problème fut de trouver dans le dictionnaire de données une vue comportant l'information comme quoi tel index a bien été utilisé. Au bout d'un moment, je suis tombé sur la vue V$SQL_PLAN, vue qui contient les plans d'exécution générés par Oracle. Là où c'est intéressant, c'est que celle-ci a une colonne OPERATION correspondant au type d'opération du plan d'exécution et ce champ vaut "INDEX" lorsqu'un index est utilisé! C'est donc cette vue que nous allons exploiter :-)

Mon idée de base était de créer un trigger sur les INSERT dans la vue V$SQL_PLAN et de copier les infos créées vers une table miroir, non réinitialisée lors des arrêts/relances de la base.
          SQL> create table zzsql_plan as select * from v$sql_plan where 1 = 10; -- 1=10 pour ne pas copier les  données
          Table créée.

Malheureusement, il est impossible de créer un trigger sur une vue appartenant à SYS. Cela se comprend car un trigger sur vue remplace l'ordre SQL au lieu de s'exécuter en plus, comme c'est le cas pour une table. N'oubliez pas : pas de mot clé AFTER ou BEFORE pour un trigger sur vue mais INSTEAD OF, ce qui signifie "A LA PLACE DE" --> si j'avais pu créer ce trigger sur l'évènement INSERT, la vue V$SQL_PLAN n'aurait plus jamais été renseignée par Oracle car les infos auraient été insérées dans ma table. A noter que le message d'erreur apparait sous SQL*Plus mais pas sous SQL Developer.
          SQL> CREATE OR REPLACE TRIGGER TRIGGER1 INSTEAD OF INSERT ON V_$SQL_PLAN BEGIN dbms_output.put_line ('TEST'); END;
          2 /
          CREATE OR REPLACE TRIGGER TRIGGER1 INSTEAD OF INSERT ON V_$SQL_PLAN BEGIN dbms_output.put_line ('TEST'); END;
          *
          ERREUR à la ligne 1 :
          ORA-04089: impossible créer déclencheurs sur objets appartenant à SYS


============================================================================================
Le trigger pour renseigner la table miroir de V$SQL_PLAN
============================================================================================

OK, on va alors créer ce trigger sur l'évènement système AFTER LOGON. Il aurait été plus sur de créer une procédure stockée puis de l'appeler dans des triggers AFTER LOGON, BEFORE LOGOFF et BEFORE SHUTDOWN pour ne perdre aucune des informations insérées dans V$SQL_PLAN mais ce post est là pour donner des idées, des pistes, et non pas pour récupérer 100% de toutes les infos de V$SQL_PLAN. On peut très bien décider de supprimer un index en ayant le nombre de fois grosso modo où il est utilisé plutôt que le nombre de fois exact.

Voici donc le code du trigger. Vous aurez remarqué l'astuce : on ne stocke que la ligne où le champ OPERATION vaut "INDEX" car c'est cette ligne là du plan d'exécution qui dit si un index est utilisé et lequel il s'agit. Nous excluons les plans d'exécution générés par Oracle et nous ne copions dans la table miroir que les enregistrements dont la date est supèrieure à la dernière date de la table miroir.
          create or replace TRIGGER TRIG_AFTER_LOGON
          AFTER LOGON ON DATABASE
          BEGIN

                    INSERT INTO HR.ZZSQL_PLAN
                        SELECT *
                        FROM SYS.V$SQL_PLAN
                        WHERE SYS.V$SQL_PLAN.OBJECT_OWNER = 'HR' AND
                    SYS.V$SQL_PLAN.OPERATION = 'INDEX' AND
                    TO_CHAR(TIMESTAMP,'DD/MM/YYYY HH24:MI:SS') >
                    (SELECT NVL(MAX (TO_CHAR(TIMESTAMP,'DD/MM/YYYY HH24:MI:SS')), SYSDATE)
                     FROM HR.ZZSQL_PLAN) ;
 
            COMMIT;  

            EXCEPTION
              WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Erreur ' || TO_CHAR(SQLCODE) || ' - ' || SQLERRM);
          END;


============================================================================================
Les tests du trigger pour alimenter V$SQL_PLAN
============================================================================================

Les tables de test appartiennent au schéma HR; nous utiliserons comme PK un champ remplit avec la pseudo colonne rownum pour être sûr d'avoir des valeurs uniques :
          SQL> CREATE TABLE ZZ01 AS SELECT OBJECT_NAME, OBJECT_TYPE, rownum AS ID FROM DBA_OBJECTS;
          SQL> CREATE TABLE ZZ03 AS SELECT OWNER, table_name, tablespace_name, rownum AS ROWNUM_TAB FROM DBA_TABLES;

         
SQL> desc zz01

          Nom                                       NULL ?       Type
          ----------------------------------------- -------- ----------------------------
          OBJECT_NAME                                        VARCHAR2(128)
          OBJECT_TYPE                                         VARCHAR2(23)
          ID                                        NOT NULL     NUMBER

          SQL> desc zz03
          Nom                                       NULL ?          Type
          ----------------------------------------- -------- ----------------------------
          OWNER                                 NOT NULL      VARCHAR2(128)
          TABLE_NAME                        NOT NULL      VARCHAR2(128)
          TABLESPACE_NAME                                   VARCHAR2(30)
          ROWNUM_TAB                                            NUMBER

Les index sont ceux créés par Oracle sur les PK de chacune des tables, correspondant aux champs ID et ROWNUM_TAB.
          SQL> select INDEX_NAME, TABLE_NAME from user_indexes where index_name like '%ZZ%';
          INDEX_NAME           TABLE_NAME
          ------------------------------ ------------------------------
          PK_ZZ01                        ZZ01
          PK_ZZ03                        ZZ03

Il faut maintenant exécuter des SELECT qui utiliseront les index; donc on évitera de faire des SELECT sans clause WHERE :-)
Dans certains cas (selon la sélectivité de la requête), le CBO décidera oui ou non d'utiliser un index.
          SQL> select * from zz01 where id = 10;
          SQL> select * from zz03 where rownum_tab > 10 and rownum_tab < 50;
          SQL> select * from zz03;
etc etc

Et attention, pour alimenter la table pérenne ZZSQL_PLAN, il faut qu'un user se connecte pour déclencher le trigger AFTER LOGON.
A ce moment là, il n'y a rien dans la table d'historisation alors qu'il y a des infos dans V$SQL_PLAN : sept SELECT ont utilisé un index de HR.
          SQL> select count(*) from  HR.ZZSQL_PLAN ;
          COUNT(*)
          ----------
                   0

          SQL> SELECT count(*)
          FROM SYS.V$SQL_PLAN
          WHERE SYS.V$SQL_PLAN.OBJECT_OWNER = 'HR'
          AND SYS.V$SQL_PLAN.OPERATION = 'INDEX'  ;
          COUNT(*)
          ----------
                   7

Un utilisateur se connecte : le trigger est déclenché et la table HR.ZZSQL_PLAN est renseignée avec les infos sur les index que nous recherchons.
          SQL> select count(*) from  HR.ZZSQL_PLAN ;
          COUNT(*)
          ----------
                   7

Les infos détaillées maintenant.
          SQL> SELECT TO_CHAR(S.TIMESTAMP, 'DD/MM/YYYY HH:MI:SS') AS "DATE ACCESS", I.TABLE_NAME AS "TABLE", S.object_name AS "INDEX", S.OPTIONS, S.object_type AS "TYPE INDEX"
          FROM HR.ZZSQL_PLAN S, DBA_INDEXES I
          WHERE S.OBJECT_NAME = I.INDEX_NAME
          ORDER BY OBJECT_NAME, "INDEX", "DATE ACCESS";

          DATE ACCESS                   TABLE    INDEX        OPTIONS                      TYPE INDEX
          -----------------------------------------------------------------------------------------------------------------------------------------

          20/05/2017 02:39:13              ZZ01    PK_ZZ01    UNIQUE SCAN              INDEX (UNIQUE)
          20/05/2017 12:58:14              ZZ01    PK_ZZ01    UNIQUE SCAN              INDEX (UNIQUE)
          20/05/2017 12:59:35              ZZ01    PK_ZZ01    RANGE SCAN              INDEX (UNIQUE)
          20/05/2017 02:40:41              ZZ03    PK_ZZ03    RANGE SCAN              INDEX (UNIQUE)
          20/05/2017 02:43:42              ZZ03    PK_ZZ03    FAST FULL SCAN         INDEX (UNIQUE)
          20/05/2017 02:43:43              ZZ03    PK_ZZ03    FULL SCAN                  INDEX (UNIQUE)
          20/05/2017 02:46:28              ZZ03    PK_ZZ03    UNIQUE SCAN              INDEX (UNIQUE)

J'arrête et redémarre ma base : les infos dans SYS.V$SQL_PLAN pour les plans d'exécution concernant le user HR sont perdues; normal puisque V$SQL_PLAN est une vue dynamique de performance (un synonyme en réalité), réinitialisée lors des arrêts/relances. Ces infos sont définitivement perdues, d'où l'intérêt de les avoir sauvegardées dans une table miroir.
          SQL> startup
          Base de données pluggable ouverte.

          SQL> SELECT count(*) FROM SYS.V$SQL_PLAN WHERE SYS.V$SQL_PLAN.OBJECT_OWNER = 'HR' AND SYS.V$SQL_PLAN.OPERATION = 'INDEX';
          COUNT(*)
          ----------
                   0


Et, bingo : les données sont toujours dans ma table miroir!
          SQL> SELECT TO_CHAR(S.TIMESTAMP, 'DD/MM/YYYY HH:MI:SS') AS "DATE ACCESS", I.TABLE_NAME AS "TABLE", S.object_name AS "INDEX", S.OPTIONS, S.object_type AS "TYPE INDEX"
          FROM HR.ZZSQL_PLAN S, DBA_INDEXES I
          WHERE S.OBJECT_NAME = I.INDEX_NAME AND
          I.TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'HR')

          ORDER BY OBJECT_NAME, "INDEX", "DATE ACCESS";

          DATE ACCESS          TABLE   INDEX        OPTIONS                     TYPE INDEX
          ------------------------------------------------------------------------------------------------------------------------------

          20/05/2017 02:39:13    ZZ01    PK_ZZ01    UNIQUE SCAN             INDEX (UNIQUE)
          20/05/2017 12:58:14    ZZ01    PK_ZZ01    UNIQUE SCAN             INDEX (UNIQUE)
          20/05/2017 12:59:35    ZZ01    PK_ZZ01    RANGE SCAN              INDEX (UNIQUE)
          20/05/2017 02:40:41    ZZ03    PK_ZZ03    RANGE SCAN              INDEX (UNIQUE)
          20/05/2017 02:43:42    ZZ03    PK_ZZ03    FAST FULL SCAN         INDEX (UNIQUE)
          20/05/2017 02:43:43    ZZ03    PK_ZZ03    FULL SCAN                  INDEX (UNIQUE)
          20/05/2017 02:46:28    ZZ03    PK_ZZ03    UNIQUE SCAN             INDEX (UNIQUE)


============================================================================================
Les nouvelles stats de monitoring des index
============================================================================================

A partir de là, nous pouvons faire nos stats sur l'utilisation des index pour les deux tables de test ZZ01 et ZZ03.
Le résultat est OK mais il manque les stats sur les index non utilisés.

          SQL> SELECT I.TABLE_NAME AS "TABLE", S.object_name AS "INDEX", COUNT(*) AS "NOMBRE D'ACCES"
          FROM HR.ZZSQL_PLAN S, DBA_INDEXES I
          WHERE S.OBJECT_NAME = I.INDEX_NAME AND
          I.TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'HR')
          GROUP BY I.TABLE_NAME, S.object_name
          ORDER BY "TABLE", "INDEX";

         TABLE               INDEX                 NOMBRE D'ACCES
         ----------------------------------------------------------------------------------
         ZZ01                  PK_ZZ01                                 10
         ZZ03                  PK_ZZ03                                  4

Nous ajoutons deux colonnes à nos tables de test, col_ind01 et col_ind03 de type NUMBER ainsi qu'une contrainte unique sur ces colonnes.
         SQL> alter table HR.zz01 add constraint UNIQ01 UNIQUE (col_ind01);
         SQL> alter table HR.zz03 add constraint UNIQ03 UNIQUE (col_ind03);

Oracle a bien créé deux index sur ces colonnes.
        SQL> select INDEX_NAME, TABLE_NAME from dba_indexes where owner = 'HR' order by 2, 1;
        INDEX_NAME                     TABLE_NAME         
        ------------------------------ --------------------
        PK_ZZ01                        ZZ01                
        UNIQ01                         ZZ01                
        PK_ZZ03                        ZZ03                
        UNIQ03                         ZZ03         

Et maintenant, même les index non utilisés apparaissent! Avec ces données détaillées, on peut discuter avec les responsables d'applications et les développeurs de la suppression d'index.      
         COL TABLE format A10
         COL INDEX format A10
         SQL> SELECT I.TABLE_NAME AS "TABLE", S.object_name AS "INDEX", COUNT(*) AS "NOMBRE D'ACCES"
                  FROM HR.ZZSQL_PLAN S, DBA_INDEXES I
                   WHERE S.OBJECT_NAME = I.INDEX_NAME AND
                  I.TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'HR')
                   GROUP BY I.TABLE_NAME, S.object_name
         UNION
         SELECT D.TABLE_NAME, D.INDEX_NAME, 0
         FROM DBA_INDEXES D
         WHERE D.OWNER = 'HR' AND
                   D.INDEX_NAME NOT IN (SELECT S.OBJECT_NAME FROM HR.ZZSQL_PLAN S)
                   ORDER BY "TABLE", "NOMBRE D'ACCES" DESC, "INDEX";

         TABLE      INDEX              NOMBRE D'ACCES
         ---------- ---------- -------------------------------------------------
         ZZ01       PK_ZZ01               10
         ZZ01       UNIQ01                  0
         ZZ03       PK_ZZ03                4
         ZZ03       UNIQ03                  0