Monitoreo de Indexes

Esta es una tarea muy importante ya que esto puede generar muchos consumos de CPU y de I/O. Con esto en mente tiene sentido poder identificar y eliminar cuales índices no son usados y así evitar un consumo de recursos inútiles.

Por lo que Oracle nos facilita esta tarea, colocando un parámetro en los índices para que este se encargue de decirnos si los índices se usan o no. Cabe destacar que esta tarea debe hacer en un periodo de tiempo significativo. Para poder tener una evolución real.

La sentencia que se utilizan para activar o desactivar el monitoreo son las siguientes:

ALTER INDEX <> MONITORING USAGE;
ALTER INDEX <> NOMONITORING USAGE;

Para hacer una activación masiva del monitoreo pueden usar el siguiente script:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL monitoring.sql

SELECT 'ALTER INDEX "' i.owner '"."' i.index_name '" MONITORING USAGE;'
FROM dba_indexes i
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

SPOOL OFF

SET PAGESIZE 18
SET FEEDBACK ON

@ monitoring.sql

Para hacer una desactivación masiva del monitoreo pueden usar el siguiente script:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql

SELECT 'ALTER INDEX "' i.owner '"."' i.index_name '" NOMONITORING USAGE;'
FROM dba_indexes i
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

SPOOL OFF

SET PAGESIZE 18
SET FEEDBACK ON

@temp.sql

Para consulta de los resultados del monitoreo puede usar el siguiente Script

SET VERIFY OFF
SET LINESIZE 200

SELECT table_name,
index_name,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE table_name = UPPER('&1')
AND index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));