Monitoreo de índices de bases de datos

 

Para aquellos que no están familiarizados con él, el monitoreo de índices es la forma de Oracle de rastrear si se está utilizando un índice, lo que le permite saber si es necesario. 

Dos cosas a tener en cuenta: 

• No siempre marca un índice como usado, incluso si se usa. Si no se usa en un plan de ejecución, pero se usa para hacer cumplir una clave externa o restricción única, no se marcará como usado. 
• La vista utilizada para observar el uso de índices es específica del esquema. Es posible que esté monitoreando índices, pero los índices no aparecerá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 (consulta a continuación).

Dado que la supervisión de índices es de muy bajo costo, tiene sentido activarla para todos los índices candidatos. Los índices en FK y los índices únicos funcionan incluso si no se utilizan en los planes de ejecución, por lo que no son candidatos para descartarse. Aquí está la consulta para obtener todos los índices no únicos, que no son de FK (se supone que no hay PK concatenados; si tiene eso, 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' --don't monitor unique indexes

   AND i.table_owner = 'SCHEMA_OWNER_HERE'

   AND ic.index_owner = i.owner

   AND ic.index_name = i.index_name

   AND ic.position = 1

   AND NOT EXISTS (SELECT 'x' --Don't monitor indexes on FK's

                     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 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 monitoreo de índices en acción, incluido el uso de índice único 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.

--Using index for PK enforcement - does not flag the index as used:

INSERT INTO test_monitoring VALUES (100,0);

INSERT INTO test_monitoring VALUES (100,0)

*

ERROR at line 1:

ORA-00001: unique constraint (BAYPAS.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                                               

--But we run a select that will use the index

SELECT * FROM test_monitoring WHERE id = 100;

        ID      VALUE                                                           

---------- ----------                                                           

       100   255.5571                                                           

--And now the index shows up as used:

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

INDEX_NAME                     USE                                              

------------------------------ ---                                              

TEST_MONITORING_IDX            YES