Monitoring de l’utilisation des indexes avec V$OBJECT_USAGE (Oracle 9i)

Introduction

Avec Oracle 9i, le monitoring de l’utilisation des indexes est grandement amélioré avec la vue V$OBJECT_USAGE. Cette vue est particulièrement pratique pour déterminer les indexes non utilisés dans les sélections sur des tables, ainsi la pertinence des indexes est analysée plus finement.

La problématique peut être liée à la consommation de redo logs lors des traitements batches. On peut en effet constater une consommation de redo logs et un nombre de switches très important lors de traitements batches transactionnels : la présence de très nombreux indexes sur les tables mises à jour lors de ces traitements (delete / insert / update) peut être la cause de cette consommation excessive, consommation qui peut alors grandement être réduite en éliminant les indexes non utilisés par l’application.

Autres points importants, les indexes inutiles ralentissent les opérations DML et consomment de l’espace.

Activation du monitoring de l’utilisation des indexes et la vue V$OBJECT_USAGE

Activation, désactivation du monitoring de l’utilisation des indexes

La commande ALTER INDEX est utilisée pour activer le monitoring de l’utilisation des indexes :

Activation du monitoring :

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

Désactivation du monitoring :

ALTER INDEX <index_name> nomonitoring usage ;
ALTER INDEX <schema>.<index_name> nomonitoring usage;

A titre d’exemple, voici un script qui génère automatiquement les commandes ALTER INDEX pour le monitoring des indexes pour le schéma SCOTT :

set linesize 500;
set pagesize 1000;

spool monitoring_scott_index.sql

select 'ALTER INDEX '||owner||'.'||index_name||' monitoring usage; '
from dba_indexes where owner='SCOTT';
/

La vue V$OBJECT_USAGE

La vue V$OBJECT_USAGE recense les informations sur l’utilisation des indexes, les colonnes de cette vue sont les suivantes :

Malheureusement, la vue V$OBJECT_USAGE ne donne pas le nombre de fois où l’index a été utilisé pour accéder à une table, on ne peut que savoir si il a été utilisé ou pas.

Voici un exemple d’interrogation de la vue V$OBJECT_USAGE :

select index_name,monitoring,used,start_monitoring,end_monitoring
      from v$object_usage;

INDEX_NAME   MONITORING USED START_MONITORING    END_MONITORING
------------ ---------- ---- ------------------- -------------------
I_PK         YES        NO   28/02/2006 18:29:16

L’exemple ci-dessus montre que le monitoring pour l’index I_PK a été lancé le 28.02.2006 et que le monitoring est encore en cours (MONITORING=YES). Cet index n’a toujours pas été utilisé depuis le début du monitoring pour l’accès à la table correspondante.

La vue V$OBJECT_USAGE ne stocke pas l’information du schéma, c’est à dire le propriétaire de la table et donc de l’index. Aussi il faut être dans le contexte courant du schéma pour avoir les informations de la vue V$OBJECT_USAGE. La commande ALTER SESSION SET CURRENT_SCHEMA = <schema_name> est utilisée pour consulter les informations de la vue V$OBJECT_USAGE pour un schéma.

alter session set current_schema=SCOTT;

select index_name, table_name, monitoring, used from v$object_usage;

Une autre méthode pour éviter l’utilisation de la commande ALTER SESSION consiste à interroger directement la table sys.object_usage et d’utiliser la requête ci-dessous :

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

Recommandations sur l’utilisation de V$OBJECT_USAGE

Période de monitoring

Avant de prendre la décision finale de supprimer un index, il est vraiment important de laisser le monitoring actif pendant quelques semaines, voire quelques mois afin d’éviter de supprimer un index crucial pour un traitement particulier.

Reverse des indexes avec DBMS_METADATA (fonction GET_DDL)

Avant la suppression de l’index, utiliser la fonction GET_DDL du package DBMS_METADATA pour sauvegarder le script de création de l’index en question :

DBMS_METADATA.GET_DDL (
   object_type  IN VARCHAR2,
   name         IN VARCHAR2,
   schema       IN VARCHAR2 DEFAULT NULL,
   version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model        IN VARCHAR2 DEFAULT 'ORACLE',
   transform    IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Exemple :

spool reverse_index.sql

set long 90000;

select dbms_metadata.get_ddl('INDEX', 'I_PK', 'SCOTT') from dual;
/