Monitorización de índices en base de datos Oracle: El mal necesario

Para aquellos que no están familiarizados con él, monitorizar índices es la forma de la base de datos Oracle de rastrear si se está usando un índice, lo que le permite saber si es necesario o no. O sea, como vengo a decir normalmete si "el índice se ha ido a Parla"

Dos cosas para tener en cuenta:

• No siempre marca un índice como se usa, incluso si se usa. Si no se utiliza en un plan de ejecución, pero se utiliza para aplicar una clave externa o una restricción única, no se marcará como se usa.
• La vista utilizada para ver el uso del índice es específica del esquema. Es posible que esté supervisando índices, pero los índices no se mostrarán en v$object_usage a menos que inicie sesión como propietario del esquema. Es mejor ir directamente a la consulta subyacente para ver todos los índices supervisados (consultar más abajo).

Dado que la monitorización de un índice tiene un costo muy bajo, tiene sentido activarlo para todos los índices candidatos. Los índices de FK e índices únicos funcionan, incluso si no se utilizan en los planes de ejecución, por lo que no son candidatos para descartar. Aquí está la consulta para obtener todos los índices non-unique, non-FK, se asume que no hay PK concatenadas, (Por Tutatis!) si eso ocurre, la consulta se vuelve más complicada:

SELECT 'ALTER INDEX '||ic.index_name||' MONITORING USAGE;'
  FROM all_ind_columns ic, all_indexes i
 WHERE i.uniqueness = 'NONUNIQUE' --no monitoriza índices únicos
   AND i.table_owner = 'NOMBRE_ESQUEMA'
   AND ic.index_owner = i.owner
   AND ic.index_name = i.index_name
   AND ic.position = 1
   AND NOT EXISTS (SELECT 'x' --No monitoriza índices en FK 
                     FROM all_cons_columns cc, all_constraints c
                    WHERE ic.table_name = cc.table_name
                      AND ic.column_name = cc.column_name
                      AND c.constraint_name = cc.constraint_name
                      AND c.constraint_type IN ('R'));

Aquí está la consulta para ver los objetos monitoreados si no se ha iniciado sesión como propietario del esquema:

select d.username, 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,
     dba_users d
where io.owner# = d.user_id
  AND d.username = 'SCHEMA_OWNER_HERE'
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;

Y aquí hay un ejemplo de monitorización de índice en acción, incluido el uso del índice exclusivo que no se marca:

CREATE TABLE test_monitoring AS SELECT level id, dbms_random.value(1,1000) value FROM dual CONNECT BY LEVEL <= 5000;

Table created.

CREATE UNIQUE INDEX test_monitoring_idx ON test_monitoring(id);

Index created.

ALTER INDEX test_monitoring_idx MONITORING USAGE;

Index altered.

Forzamos el índice para la aplicación de PK, este no marca el índice como usado:

INSERT INTO test_monitoring VALUES (100,0);
INSERT INTO test_monitoring VALUES (100,0)
*
ERROR at line 1:
ORA-00001: unique constraint (RAFA.TEST_MONITORING_IDX) violated 

SELECT index_name, used FROM v$object_usage WHERE index_name = UPPER('test_monitoring_idx');

INDEX_NAME                     USE                                              
------------------------------ ---                                              
TEST_MONITORING_IDX            NO                                               

Pero ejecutamos una sentencia "Select" que usará el índice

SELECT * FROM test_monitoring WHERE id = 100;

        ID      VALUE                                                           
---------- ----------                                                           
       100   255.5571                                                           
Y ahora el índice aparece como usado:

SELECT index_name, used FROM v$object_usage WHERE index_name = 'TEST_MONITORING_IDX';

INDEX_NAME                     USE                                              
------------------------------ ---                                              

TEST_MONITORING_IDX            YES 

Tener índices sin usar en la base de datos puede quitarnos espacio necesario y reducir el rendimiento en las sentencias  de borrado o actualización, por eso es importante eliminar aquellos que no son utilizados.