Monitorando o uso de índices

Introdução

por Rodrigo Salviatto

Quando desenhamos um sistema, implementamos índices em tabelas para obtermos uma performance no uso da aplicação, porém, por muitas vezes, estes índices não são utilizados e acabam atrapalhando a performance das aplicações que utilizam o banco de dados Oracle, utilizam espaço em disco desnecessário, aumentam o everhead das operações DML.

Diante disso, é possível identificarmos se um índice criado em uma tabela está sendo utilizado ou não. Vejamos como fazer isto.

 A verificação da utilização de um índice em uma determinada tabela é efetuada através da ativação do monitoramento do índice, onde identificamos se um determinado índice está ou não sendo utilizado. A sintaxe utilizada para ativar o monitoramento de um índice é:

ALTER INDEX INDEX_NAME MONITORING USAGE

A partir deste ponto, o Oracle irá armazenar todas as vezes que o índice foi utilizado, nesta sessão. Para visualizar se o índice está sendo utilizado na sessão, utilize a view do dicionário de dados  denominada V$OBJECT_USAGE. Exemplo:

SQL> SELECT  INDEX_NAME, USED

FROM V$OBJECT_USAGE;

INDEX_NAME          USED

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

IDX_EMP_01          YES

IDX_DEPT_01         NO

Observe que a consulta efetuada na sessão onde os índices IDX_EMP_01 e IDX_DEPT_01 foram ativados, apresentam os valores YES e NO. Desta forma, foi possível identificar que o índice IDX_EMP_01 está sendo utilizado pelas consultas executadas na sessão durante o monitoramente, porém o índice IDX_DEPT_01 não foi utilizado.

Toda vez que iniciar a monitoração de um índice, a view V$OBJECT_USAGE será redefinida, ou seja, os dados referentes monitorações de índices anteriores serão excluídos.

Para desativar o monitoramento do índice ( não se esqueça de efetuar esta operação ), execute a seguinte sintaxe:

ALTER INDEX INDEX_NAME NOMONITORING USAGE;

Antes de tomar qualquer ação no banco de dados, certifique-se que o período monitorado foi suficiente para colher as informações necessárias de verificação de uso do índice! Mantenha sempre seu backup do banco de dados atualizado e testado, assim como os scripts de criação dos objetos( índices por exemplo ), caso você tenha que restaurá-lo em uma situação emergencial!

Abraços e até a próxima!