O objetivo deste artigo e apresentar como monitorar o uso de indice no Oracle 11g R2 x86_64. Sera demostrado como identificar indices que nao estao sendo acessados, desta forma elegemos possiveis candidatos para remocao. Primeiro sera mostrado de forma teorica, em seguida utilizaremos um estudo de caso com o esquema HR, mostrando na pratica como o processo e feito.E comum o indice ser criado e apos algumas mudancas de regra de negocio, o indice deixa de ser acessado, isso por que o filtro da consulta ou a propria consulta mudou, e o pior que ninguem percebe, onerando o desempenho de operacoes de escrita (INSERT, UPDATE e DELETE). Isso acontece por que alem de atualizar os dados e necessario atualizar os indices. Outro problema e que esses indices tambem ocupam espaco na tablespace.A seguir vera que e simples a facil monitorar o uso de indice, basicamente um comando para habilitar, outro comando para desabilitar e uma visao para consultar o monitoramento.Para habilitar o monitoramento:ALTER INDEX <INDICE> MONITORING USAGE;Para desabilitar o monitoramento:ALTER INDEX <INDICE> NONMONITORING USAGE;Para consultar o monitoramento:SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORINGFROM V$OBJECT_USAGE;Estudo de caso: Monitorando o uso de indice no esquema HRAgora que voce ja conhece como monitorar o uso de indice, vamos para um exemplo real. Como o esquema HR possui varios indices, vamos gerar os comandos atraves do dicionario de dados do Oracle consultando a visao USER_INDEXES. Sera utilizado o sqlplus neste estudo de caso.Conectando no banco com o usuario HR:[oracle@mytracelog ~]$ sqlplus hr/hr@mytracelogGerando os comandos para habilitar o monitoramento:SQL> SELECT 'ALTER INDEX '||INDEX_NAME||' MONITORING USAGE;' SQL 2 FROM USER_INDEXES 3 WHERE INDEX_TYPE = 'NORMAL' 4 ORDER BY TABLE_NAME, INDEX_NAME; SQL------------------------------------------------------------ALTER INDEX DEPT_ID_PK MONITORING USAGE;ALTER INDEX DEPT_LOCATION_IX MONITORING USAGE;ALTER INDEX EMP_DEPARTMENT_IX MONITORING USAGE;ALTER INDEX EMP_EMAIL_UK MONITORING USAGE;ALTER INDEX EMP_EMP_ID_PK MONITORING USAGE;ALTER INDEX EMP_JOB_IX MONITORING USAGE;ALTER INDEX EMP_MANAGER_IX MONITORING USAGE;ALTER INDEX EMP_NAME_IX MONITORING USAGE;ALTER INDEX JHIST_DEPARTMENT_IX MONITORING USAGE;ALTER INDEX JHIST_EMP_ID_ST_DATE_PK MONITORING USAGE;ALTER INDEX JHIST_EMPLOYEE_IX MONITORING USAGE;ALTER INDEX JHIST_JOB_IX MONITORING USAGE;ALTER INDEX JOB_ID_PK MONITORING USAGE;ALTER INDEX LOC_CITY_IX MONITORING USAGE;ALTER INDEX LOC_COUNTRY_IX MONITORING USAGE;ALTER INDEX LOC_ID_PK MONITORING USAGE;ALTER INDEX LOC_STATE_PROVINCE_IX MONITORING USAGE;ALTER INDEX REG_ID_PK MONITORING USAGE; 18 rows selectedHabilitando o monitoramento dos indices:SQL> ALTER INDEX DEPT_ID_PK MONITORING USAGE; Index alteredSQL> ALTER INDEX DEPT_LOCATION_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX EMP_DEPARTMENT_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX EMP_EMAIL_UK MONITORING USAGE; Index alteredSQL> ALTER INDEX EMP_EMP_ID_PK MONITORING USAGE; Index alteredSQL> ALTER INDEX EMP_JOB_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX EMP_MANAGER_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX EMP_NAME_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX JHIST_DEPARTMENT_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX JHIST_EMP_ID_ST_DATE_PK MONITORING USAGE; Index alteredSQL> ALTER INDEX JHIST_EMPLOYEE_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX JHIST_JOB_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX JOB_ID_PK MONITORING USAGE; Index alteredSQL> ALTER INDEX LOC_CITY_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX LOC_COUNTRY_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX LOC_ID_PK MONITORING USAGE; Index alteredSQL> ALTER INDEX LOC_STATE_PROVINCE_IX MONITORING USAGE; Index alteredSQL> ALTER INDEX REG_ID_PK MONITORING USAGE; Index alteredConsultando o monitoramento:SQL> SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING 2 FROM V$OBJECT_USAGE 3 ORDER BY TABLE_NAME, INDEX_NAME 4 / INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING------------------------------ ------------------------------ ---------- ---- ------------------- -------------------DEPT_ID_PK DEPARTMENTS YES