Souvent chez les clients la problématique de la performance et les demandes inhérentes à son amélioration sont au cœur de débats assez animés. En ce sens, le monitoring de l’utilisation des indexes peut être une piste plutôt intéressante à suivre afin de déterminer les indexes non-utilisés par les opérations de lecture sur les tables.
Premièrement et à titre d’exemple, une présence très accrue d’index sur des tables dont la mise à jour est fréquentes lors de traitements peut être la cause de ralentissements, qui peuvent alors grandement être réduit en éliminant les indexes non utilisés par l’applicatif.
Aussi, les indexes inutiles ralentissent les opérations DML et consomment de l’espace.
L’exemple suivant montre l’un des moyen de « monitorer » les indexes via la vue V$OBJECT_USAGE :

La démonstration se fait sur le schéma SCOTT.
Si ce-dernier n’existe pas sur votre base, il est alors possible de l’installer selon la méthode suivante :

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
SQL> select username from dba_users;
USERNAME
 ------------------------------
 OUTLN
 SYS
 SYSTEM
 SCOTT
 DBSNMP
 APPQOSSYS
 WMSYS
 DIP
USERNAME
 ------------------------------
 ORACLE_OCM
12 rows selected.
SQL>exit

Pour la petite démonstration qui va suivre nous prendrons comme exemple l’index PK_EMP sur la table EMP référençant la colonne EMPNO :

 SQL> set lines 130
 SQL> col COLUMN_NAME form a20
 SQL> col INDEX_NAME form a20
 SQL> col TABLE_NAME form a20
 SQL> select b.uniqueness, a.index_name, a.table_name, a.column_name
 from all_ind_columns a, all_indexes b
 where a.index_name=b.index_name
 and a.table_name = upper('emp')
 order by a.table_name, a.index_name, a.column_position
UNIQUENES INDEX_NAME TABLE_NAME COLUMN_NAME
 --------- -------------------- -------------------- --------------------
 UNIQUE PK_EMP EMP EMPNO

Notez que la vue V$OBJECT_USAGE ne stocke pas l’information du schéma propriétaire de l’index.
Par conséquent, il faut veiller à bien se placer dans le contexte courant du schéma pour avoir les informations de la vue V$OBJECT_USAGE pour un schéma :

ALTER SESSION SET CURRENT_SCHEMA = <schema_name>
$ sqlplus / as sysdba
SQL> conn scott/tiger
Connected.
SQL> alter session set current_schema=SCOTT;
Session altered.
SQL> select sys_context('USERENV','SESSION_SCHEMA') from dual;
SYS_CONTEXT('USERENV','SESSION
 ------------------------------
 SCOTT
SQL> alter index PK_EMP monitoring usage;
Index altered.
SQL> set lines 130
 SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
 7369 SMITH CLERK 7902 17-DEC-80 800 20
 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
 7566 JONES MANAGER 7839 02-APR-81 2975 20
 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
 7839 KING PRESIDENT 17-NOV-81 5000 10
 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
 7900 JAMES CLERK 7698 03-DEC-81 950 30
 7902 FORD ANALYST 7566 03-DEC-81 3000 20
 7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MON USE START_MONITORING END_MONITORING
 ------------------------------ --- --- ------------------- -------------------
 PK_EMP YES NO 03/19/2013 16:11:52

Nous allons maintenant exécuter une requête filtrée par la colonne non indexée (DEPTNO) :

SQL> select * from emp where deptno = 30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
 7900 JAMES CLERK 7698 03-DEC-81 950 30
6 rows selected.
SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
SQL> select index_name, table_name, used from v$object_usage;
INDEX_NAME TABLE_NAME USE
 ------------------------------ ------------------------------ ---
 PK_EMP EMP NO

Le résultat nous confirme la non utilisation de l’index PK_EMP.
Normal !
Nous allons maintenant exécuter d’autres requêtes filtrées par la colonne indexée (EMPNO) :

SQL> select * from emp where empno < 7400;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
select index_name, table_name, used from v$object_usage;
INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
PK_EMP                         EMP                            YES

Par contre, nous constatons bien une utilisation de l’index pour cette requête :

SQL> explain plan for
select * From emp where empno is null;
Explained.
SQL> set lines 130
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3896240783
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
1 - filter(NULL IS NOT NULL)
14 rows selected.

Nous constatons que même si la précédente requête n’utilise pas du tout l’index, le monitoring affiche tout de même la dernière utilisation de l’index :

SQL> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
PK_EMP YES YES 03/19/2013 16:11:52

Afin de se défaire du monitoring il suffit juste d’exécuter la requête suivante :

SQL> alter index PK_EMP nomonitoring usage;
Index altered.

Recommandations:
En somme, avant toute prise de décision finale de supprimer un index, il est important de laisser le monitoring actif pendant quelques semaines, voire quelques mois afin d’éviter de supprimer un index crucial pour un traitement particulier.
Attention, avant la suppression de l’index, utilisez la fonction GET_DDL du package DBMS_METADATA pour réaliser un « rollback » de l’index éventuellement supprimé :

SQL> set long 20000 pagesize 0
SQL> select dbms_metadata.get_ddl('INDEX','PK_EMP','SCOTT') from dual;
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA_BOPFSERV"

Contournement et Astuce:
On peut, afin d’éviter l’utilisation de la commande ALTER SESSION, consulter directement la table sys.object_usage :

select
io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from
sys.obj$ io,
sys.obj$ t,
sys.ind$ i,
sys.object_usage ou
where
io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj# and t.obj# = i.bo#;