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).
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
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.
