O objetivo deste artigo é apresentar como monitorar o uso de índice no Oracle 11g R2 x86_64. Será demostrado como identificar índices que não estão sendo acessados, desta forma elegemos possíveis candidatos para remoção. Primeiro será mostrado de forma teórica, em seguida utilizaremos um estudo de caso com o esquema HR, mostrando na prática como o processo é feito.
É comum o índice ser criado e após algumas mudanças de regra de negócio, o índice deixa de ser acessado, isso por que o filtro da consulta ou a própria consulta mudou, e o pior que ninguém percebe, onerando o desempenho de operações de escrita (INSERT, UPDATE e DELETE). Isso acontece por que além de atualizar os dados é necessário atualizar os índices. Outro problema é que esses índices também ocupam espaço na tablespace.
A seguir verá que é simples a fácil monitorar o uso de índice, basicamente um comando para habilitar, outro comando para desabilitar e uma visão para consultar o monitoramento.
Para habilitar o monitoramento:
ALTER INDEX <ÍNDICE> MONITORING USAGE;
Para desabilitar o monitoramento:
ALTER INDEX <ÍNDICE> NONMONITORING USAGE;
Para consultar o monitoramento:
SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING FROM V$OBJECT_USAGE;
Estudo de caso: Monitorando o uso de índice no esquema HR
Agora que você já conhece como monitorar o uso de índice, vamos para um exemplo real. Como o esquema HR possui vários índices, vamos gerar os comandos através do dicionário de dados do Oracle consultando a visão USER_INDEXES. Será utilizado o sqlplus neste estudo de caso.Conectando no banco com o usuário HR:
[oracle@mytracelog ~]$ sqlplus hr/hr@mytracelog
Gerando 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 selected
Habilitando o monitoramento dos índices:
SQL> ALTER INDEX DEPT_ID_PK MONITORING USAGE; Index altered SQL> ALTER INDEX DEPT_LOCATION_IX MONITORING USAGE; Index altered SQL> ALTER INDEX EMP_DEPARTMENT_IX MONITORING USAGE; Index altered SQL> ALTER INDEX EMP_EMAIL_UK MONITORING USAGE; Index altered SQL> ALTER INDEX EMP_EMP_ID_PK MONITORING USAGE; Index altered SQL> ALTER INDEX EMP_JOB_IX MONITORING USAGE; Index altered SQL> ALTER INDEX EMP_MANAGER_IX MONITORING USAGE; Index altered SQL> ALTER INDEX EMP_NAME_IX MONITORING USAGE; Index altered SQL> ALTER INDEX JHIST_DEPARTMENT_IX MONITORING USAGE; Index altered SQL> ALTER INDEX JHIST_EMP_ID_ST_DATE_PK MONITORING USAGE; Index altered SQL> ALTER INDEX JHIST_EMPLOYEE_IX MONITORING USAGE; Index altered SQL> ALTER INDEX JHIST_JOB_IX MONITORING USAGE; Index altered SQL> ALTER INDEX JOB_ID_PK MONITORING USAGE; Index altered SQL> ALTER INDEX LOC_CITY_IX MONITORING USAGE; Index altered SQL> ALTER INDEX LOC_COUNTRY_IX MONITORING USAGE; Index altered SQL> ALTER INDEX LOC_ID_PK MONITORING USAGE; Index altered SQL> ALTER INDEX LOC_STATE_PROVINCE_IX MONITORING USAGE; Index altered SQL> ALTER INDEX REG_ID_PK MONITORING USAGE; Index altered
Consultando 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 NO 09/15/2011 22:17:45 DEPT_LOCATION_IX DEPARTMENTS YES NO 09/15/2011 22:17:45 EMP_DEPARTMENT_IX EMPLOYEES YES NO 09/15/2011 22:17:45 EMP_EMAIL_UK EMPLOYEES YES NO 09/15/2011 22:17:45 EMP_EMP_ID_PK EMPLOYEES YES NO 09/15/2011 22:17:45 EMP_JOB_IX EMPLOYEES YES NO 09/15/2011 22:17:45 EMP_MANAGER_IX EMPLOYEES YES NO 09/15/2011 22:17:45 EMP_NAME_IX EMPLOYEES YES NO 09/15/2011 22:17:45 JHIST_DEPARTMENT_IX JOB_HISTORY YES NO 09/15/2011 22:17:45 JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY YES NO 09/15/2011 22:17:45 JHIST_EMPLOYEE_IX JOB_HISTORY YES NO 09/15/2011 22:17:45 JHIST_JOB_IX JOB_HISTORY YES NO 09/15/2011 22:17:45 JOB_ID_PK JOBS YES NO 09/15/2011 22:17:45 LOC_CITY_IX LOCATIONS YES NO 09/15/2011 22:17:45 LOC_COUNTRY_IX LOCATIONS YES NO 09/15/2011 22:17:45 LOC_ID_PK LOCATIONS YES NO 09/15/2011 22:17:45 LOC_STATE_PROVINCE_IX LOCATIONS YES NO 09/15/2011 22:17:45 REG_ID_PK REGIONS YES NO 09/15/2011 22:17:45 18 rows selected
Repare que acabamos de habilitar o monitoramento, todos os índices estão com NO no campo USED.
Vamos fazer um consulta utilizando o índice EMP_DEPARTMENT_IX:
SQL> SELECT FIRST_NAME, LAST_NAME 2 FROM EMPLOYEES 3 WHERE DEPARTMENT_ID = 100; FIRST_NAME LAST_NAME -------------------- ------------------------- Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman Luis Popp 6 rows selected
Agora verificamos o uso do índice:
SQL> SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING 2 FROM V$OBJECT_USAGE 3 WHERE INDEX_NAME = 'EMP_DEPARTMENT_IX'; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING ------------------------------ ------------------------------ ---------- ---- ------------------- ------------------- EMP_DEPARTMENT_IX EMPLOYEES YES YES 09/15/2011 22:17:45
O campo USED está com YES, isso quer dizer que o índice está sendo utilizado.
Consultando o índice EMP_EMAIL_UK:
SQL> SELECT FIRST_NAME, LAST_NAME 2 FROM EMPLOYEES 3 WHERE EMAIL = 'TGATES'; FIRST_NAME LAST_NAME -------------------- ------------------------- Timothy Gates
Verificando o uso do índice:
SQL> SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING 2 FROM V$OBJECT_USAGE 3 WHERE INDEX_NAME = 'EMP_EMAIL_UK'; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING ------------------------------ ------------------------------ ---------- ---- ------------------- ------------------- EMP_EMAIL_UK EMPLOYEES YES YES 09/15/2011 22:17:45
Agora com o monitoramento habilitado é só acompanhar. O tempo de monitoramento varia de acordo com o banco, cada banco possui um ciclo de vida diferente. Antes de mais nada, o DBA deve conhecer muito bem o seu banco, deve saber quais são os processos que são rodados, a frequência e a duração de cada um. O ciclo de vida termina quando todos os tipos de processos foram rodados pelos menos 1 vez, isso pode levar alguns dias, semanas, ou meses, depende da regra de negócio. É importante definir muito bem o tempo de monitoramento para que não se tenha um resultado falso. O índice pode simplesmente não ter nenhum acesso por que o processo que utiliza esse índice ainda não foi rodado.
Após completar um ciclo de vida, desabilite o monitoramento e analise o resultado. Índices que não estão sendo acessados, são possíveis candidatos para remoção.
Gerando os comandos desabilitar o monitoramento:
SQL> SELECT 'ALTER INDEX '||INDEX_NAME||' NOMONITORING USAGE;' SQL 2 FROM USER_INDEXES 3 WHERE INDEX_TYPE = 'NORMAL' 4 ORDER BY TABLE_NAME, INDEX_NAME; SQL -------------------------------------------------------------- ALTER INDEX DEPT_ID_PK NOMONITORING USAGE; ALTER INDEX DEPT_LOCATION_IX NOMONITORING USAGE; ALTER INDEX EMP_DEPARTMENT_IX NOMONITORING USAGE; ALTER INDEX EMP_EMAIL_UK NOMONITORING USAGE; ALTER INDEX EMP_EMP_ID_PK NOMONITORING USAGE; ALTER INDEX EMP_JOB_IX NOMONITORING USAGE; ALTER INDEX EMP_MANAGER_IX NOMONITORING USAGE; ALTER INDEX EMP_NAME_IX NOMONITORING USAGE; ALTER INDEX JHIST_DEPARTMENT_IX NOMONITORING USAGE; ALTER INDEX JHIST_EMP_ID_ST_DATE_PK NOMONITORING USAGE; ALTER INDEX JHIST_EMPLOYEE_IX NOMONITORING USAGE; ALTER INDEX JHIST_JOB_IX NOMONITORING USAGE; ALTER INDEX JOB_ID_PK NOMONITORING USAGE; ALTER INDEX LOC_CITY_IX NOMONITORING USAGE; ALTER INDEX LOC_COUNTRY_IX NOMONITORING USAGE; ALTER INDEX LOC_ID_PK NOMONITORING USAGE; ALTER INDEX LOC_STATE_PROVINCE_IX NOMONITORING USAGE; ALTER INDEX REG_ID_PK NOMONITORING USAGE; 18 rows selected
Desabilitando o monitoramento:
SQL> ALTER INDEX DEPT_ID_PK NOMONITORING USAGE; Index altered SQL> ALTER INDEX DEPT_LOCATION_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX EMP_DEPARTMENT_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX EMP_EMAIL_UK NOMONITORING USAGE; Index altered SQL> ALTER INDEX EMP_EMP_ID_PK NOMONITORING USAGE; Index altered SQL> ALTER INDEX EMP_JOB_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX EMP_MANAGER_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX EMP_NAME_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX JHIST_DEPARTMENT_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX JHIST_EMP_ID_ST_DATE_PK NOMONITORING USAGE; Index altered SQL> ALTER INDEX JHIST_EMPLOYEE_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX JHIST_JOB_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX JOB_ID_PK NOMONITORING USAGE; Index altered SQL> ALTER INDEX LOC_CITY_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX LOC_COUNTRY_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX LOC_ID_PK NOMONITORING USAGE; Index altered SQL> ALTER INDEX LOC_STATE_PROVINCE_IX NOMONITORING USAGE; Index altered SQL> ALTER INDEX REG_ID_PK NOMONITORING USAGE; Index altered
Consultando e analisando 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; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING ------------------------------ ------------------------------ ---------- ---- ------------------- ------------------- DEPT_ID_PK DEPARTMENTS NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 DEPT_LOCATION_IX DEPARTMENTS NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 EMP_DEPARTMENT_IX EMPLOYEES NO YES 09/15/2011 22:17:45 09/15/2011 23:12:15 EMP_EMAIL_UK EMPLOYEES NO YES 09/15/2011 22:17:45 09/15/2011 23:12:15 EMP_EMP_ID_PK EMPLOYEES NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 EMP_JOB_IX EMPLOYEES NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 EMP_MANAGER_IX EMPLOYEES NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 EMP_NAME_IX EMPLOYEES NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 JHIST_DEPARTMENT_IX JOB_HISTORY NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 JHIST_EMPLOYEE_IX JOB_HISTORY NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 JHIST_JOB_IX JOB_HISTORY NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 JOB_ID_PK JOBS NO NO 09/15/2011 22:17:45 09/15/2011 23:12:15 LOC_CITY_IX LOCATIONS NO NO 09/15/2011 22:17:45 09/15/2011 23:12:16 LOC_COUNTRY_IX LOCATIONS NO NO 09/15/2011 22:17:45 09/15/2011 23:12:16 LOC_ID_PK LOCATIONS NO NO 09/15/2011 22:17:45 09/15/2011 23:12:16 LOC_STATE_PROVINCE_IX LOCATIONS NO NO 09/15/2011 22:17:45 09/15/2011 23:12:16 REG_ID_PK REGIONS NO NO 09/15/2011 22:17:45 09/15/2011 23:12:16 18 rows selected
Neste estudo de caso foram monitorados 18 índices, o monitoramento iniciou dia 15/09/2011 as 22:17 e terminou no dia 15/09/2011 as 23:12, e detectou que apenas os índices EMP_DEPARTMENT_IX e EMP_EMAIL_UK foram acessados durante o monitoramento.
Conclusão
Como foi demostrado neste artigo, o processo de monitoramento do uso de índice é uma tarefa simples e fácil que pode trazer boas informações para o DBA tomar decisão. Desde alteração dos índices até a própria remoção. Foi apresentado os passos de forma teórica seguida de um estudo de caso com o esquema HR.Gostou do artigo? Ajude a divulgá-lo clicando no botão +1
0 comentários:
Postar um comentário
Gostou do artigo ? Então comente...