Monitoreo de indices

El uso de los índices es de gran ayuda para mejorar el rendimiento de la aplicaciones el momento de filtrar los datos, pero, como podemos saber si los índices que tenemos en nuestra base de datos están verdaderamente siendo usados por nuestras aplicaciones?

¿Porque puede pasar que los índices no se usen?

La aplicación sea adquirida y no desarrollada. Esto implica que ya viene con sus índices pre-establecidos.

El funcionamiento o políticas de la empresa cambien lo que también afecta al funcionamiento de nuestras aplicaciones.

La creación de un nuevo índice sea más óptima en costo con el que ya existía. Ya que el CBO tomará el índice con menor costo para su planificación.

Para todos estos casos, esto nos generaría tener índices inútiles que ocupan espacio y hacen más costos las operaciones de insert, delete y update.

Oracle introdujo desde la versión 10g el monitoreo de índices lo cual nos permite determinar que índices se usan o no se usan.

Acción	Sentencia
Activar monitoreo	ALTER INDEX indice_01 MONITORING USAGE;
Desactivar monitoreo	ALTER INDEX indice_01 NOMONITORING USAGE;

EJEMPLO

/*Creamos una tabla*/ CREATE TABLE mi_tablas AS   SELECT * FROM all_tables; /*Creamos el indice*/ CREATE INDEX mi_indice_01    ON mi_tablas (table_name);

Veamos la información

/*Vemos la informacion del indice*/   SELECT iu.name owner, io.name index_name, T.name table_name,        DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') MONITORING,        DECODE(bitand(ou.flags, 1), 0, 'NO', NULL, NULL, 'YES') used,        ou.start_monitoring,        ou.end_monitoring FROM    sys.obj$ io,         sys.obj$ T,         sys.ind$ i,         sys.USER$ iu,         sys.USER$ tu,         sys.object_usage ou,         dba_segments s WHERE  io.owner# = iu.USER# AND    i.obj# = io.obj# AND    io.obj# = ou.obj# (+) AND    T.obj# = i.bo# AND    T.owner# = Tu.USER# AND    i.type# not in (4, 8, 9) AND    iu.name = user --Usuario dueño del objeto AND    bitand(io.flags, 128) <> 128 AND     S.SEGMENT_NAME=io.name AND     S.OWNER=iu.name

.

Descripción de las columnas
OWNER	Dueño del índice
INDEX_NAME	Nombre del índice
TABLE_NAME	Tabla asociada al índice
MONITORING	YES/NO si está o no siendo monitoreado
USED	YES/NO si fue usado o no el índice
START_MONITORING	Fecha desde que inició el monitoreo
END_MONITORING	Fecha que finalizó el monitoreo

Vemos que en la columna MONITORING el valor es NO lo cual indica que el índice no está siendo monitoreado.

Ahora activemos el monitoreo del índice.

/*Activar el monitoreo del indice*/ ALTER INDEX mi_indice_01 MONITORING USAGE;

Ejecutamos la consulta para ver información del índice

Aquí vemos que en la columna MONITORING ya indica que el índice está siendo siendo monitoreado, en la columna USED en cambio indica NO lo cual indica que desde que se activo el monitoreo no se ha hecho uso del índice.

Ahora ejecutemos una consulta usando como filtro el campo de la tabla asociada al índice.

/*Selecionamos los datos usando el indice*/ select * from mi_tablas where table_name='DATO';

Veamos la consulta de información del índice.

Ya con esto vemos que cuando ejecutamos una consulta que hace uso del campo asociado al índice el monitoreo registra que el índice fue usado de tal manera que ya podemos determinar si utilizo o no.

El tiempo que monitoreemos los índices dependerá de la frecuencia de uso de los objetos, como por ejemplo si tenemos consultas que solo se hacen uso a fines, en este escenario debemos esperar hasta que todos los procesos o consultas se ejecuten durante el periodo. Con esto garantizaremos que si eliminamos el índice estemos 100% seguro de que no se use por ningún proceso o consulta.