Monitorando o uso dos índices
maio 10, 2011

Existem muitos bancos de dados em que índices estão criados mais não são utilizados. Por exemplo, ter criado um índice para uma determinado procedimento, que é executado somente uma vez  e após seu uso não é removido, ou até mesmo o Oracle perceber que leitura por scans completos pode ser mais vantajosos do que utilizar um determinado índice (isso acontece).

Criar índice em uma base, deve ser algo realmente estudado, pois podem ter impacto negativo sobre o desempenho das operações DML. Além de modificar o valor do bloco da data, também é necessário atualizar o bloco do índice.

Por esse motivo que deve-se notar muito bem a utilização de um índice, caso não seja utilizado prejudica o desempenho do banco de dados.

Abaixo está um exemplo para descobrir se um índice está sendo ou não utilizado

Acompanhe …

SQL> create table t
2 (
3 id number,
4 nome varchar2(50)
5 )
6 ;

Table created.

SQL> create index t_id_idx on t (id);

Index created.
SQL> insert into t values (1, 'Flávio');
1 row created.
SQL> commit; SQL> select index_name, table_name, used from v$object_usage; INDEX_NAME                          TABLE_NAME                USE ------------------------------ ------------------------------ --- T_ID_IDX                             T                         NO SQL> alter index t_id_idx monitoring usage; Index altered. SQL> select * from t where id=1; ID           NOME ---------- -------------------------------------------------- 1            Flávio SQL> select index_name, table_name, used from v$object_usage; INDEX_NAME                       TABLE_NAME                   USE ------------------------------ ------------------------------ --- T_ID_IDX                          T                           YES SQL> alter index t_id_idx nomonitoring usage; Index altered.

Veja que a view v$OBJECT_USAGE, terá cada índice do seu esquema cujo uso está sendo monitorando, se por ventura o índice não for usado, pode ser uma ótima opção exclui-lo.

Índices inutilizáveis
maio 10, 2011

Se um índice torna-se inutilizável, ele sempre deverá ser reparado antes que possa ser usado. Diferentemente de um objeto PL/SQL por exemplo, pois a primeira vez que é acesso o objeto é recompilado automaticamente pelo Oracle.

Mais quando um índice se torna inutilizável?

Se os rowids da tabela forem modificados por qualquer razão, então os índices será marcado como inutilizável, isso por que o rowid é o ponteiro físico para a localização da linha a qual a chave de índice se refere.

Como identificar índices inutilizáveis?

Índices inutilizáveis são detectados utilizando a seguinte consulta.

SQL> select index_name, table_name from dba_indexes where status = 'UNUSABLE';

Versões anteriores do Oracle, quando se executava uma instrução SQL que tentasse usar um índice inutilizável, retornaria um erro e a instrução toda falharia. Com o 10g em diante, a instrução que vou utilizar um índice inutilizável, o banco reverterá para um plano de execução que não necessite de índice, mas isso pode causar uma grande queda de desempenho. Esse comportamento pode ser trocado mudando o parâmetro de inicialização SKIP_UNUSABLE_INDEXES para false, que por padrão é true.

SQL> alter system set skip_unusable_indexes=false;

Como reparar índices inutilizáveis?

Índices são reparados utilizando o comando ALTER INDEX … REBUILD. Isso gerará um novo índice com ponteiros das rowid atualizados para a chave de índice.

Os mais importantes parâmetros do comando rebuild são TABLESPACE, NOLOGGING, ONLINE.

TABLESPACE : Se você deseja construir seu índice em um tablespace diferente da sua tabela.

NOLOGGING: Diz ao Oracle a não gerar redo para a operação de construção de índice. Isso torna a reconstrução muito mais rápida, mais assim o índice não sobreviverá a um dado causado que requeira o uso do restore e recovery. Se for utilizar essa opção, lembre-se sempre de gerar um backup do tablespace, assim você está seguro a falhas.

ONLINE: A geração de índices exige um certo esforço do banco de dados, principalmente se a tabela for muito grande. Por padrão o processo de reconstrução bloqueia todo DML que vier de encontro a tabela no momento da reconstrução, isso pode ser evitado usando ONLINE no comando.

Reparando Índices

Vamos a um exemplo prático, onde será necessário reconstruir um índice.

SQL> create table t
2 (
3     id number
4 ) tablespace users;
5 /

Table created.

SQL> create index id_idx on t(id);

Index created.

Vamos confirmar que o índice está valido.

SQL> select index_name, status from user_indexes;

INDEX_NAME                   STATUS
------------------------------ --------
ID_IDX                            VALID

Agora, vamos movimentar a tabela T do tablespace USERS para o tablespace TB_TESTE. Assim os índices vão perder todas as suas referências, conseqüentemente se tornará inutilizável pelo plano de execução Oracle.

SQL> alter table t move tablespace tb_teste;

Table alterada.

Se verificarmos o status do índice, ele estará com o status alterado para UNUSABLE.

SQL> select index_name, status from user_indexes;

INDEX_NAME                        STATUS
------------------------------ --------
IDX_ID                         UNUSABLE

Para tornar o indice válido, apenas devemos reconstruir os ponteiros das rowids executando o comando rebuild.

SQL> alter index idx_id rebuild online nologging;

Index altered.

SQL> select index_name, status from user_indexes;

INDEX_NAME                   STATUS
------------------------------ --------
ID_IDX                            VALID

Após o comando REBUILD, o índice já estará pronto para ser utilizado novamente.

Index na prática
agosto 10, 2009

Existe no Oracle diversos mecanismos para otimizar a performance do banco de dados, uma dessas formas, é a possibilidade de criar index em tabelas.

Antes de fazermos nosso exemplo prático, deixarei algumas diretrizes que a Oracle recomenda em relação a index. Devemos sempre prestar atenção nessas recomendações afim de realizar um trabalho eficiente. Fique atento com a criação deles, se algum index for mal construído, poderá até afetar a performance do banco dados.

Veja algumas:

Quando criar index

  • Quando a coluna contém um grande range de valores.
  • A coluna contém vários valores null.
  • A tabela deve ser grande.
  • Quando a maioria das consultas, espera recuperar menos do que 2% a 4% das linhas da tabela.
  • Frequentemente, a(s) coluna(s) é usada em WHERE cláusula ou em condições de join.
  • Quando não criar index

  • Quando a coluna não for usada em condições na consulta.
  • A tabela é atualizada frequentemente.
  • Quando tabela for pequena.
  • A maioria das consultas expera recuperar mais do que 2% a 4% das linhas da tabela.
  • Vamos iniciar nosso teste criando um ambiente para ele, execute o seguinte comando para criar a tabela T com os registros da tabela all_objects.

    SQL> create table t as select object_name name, object_id id from all_objects;
    
    Tabela criada.
    
    Vamos dar um pouco mais de carga nessa tabela.
    
    SQL> insert into t select * from t;
    
    49871 linhas criadas.
    
    SQL> select count(*) from t;
    
    COUNT(*)
    ----------
    99742

    Agora com a tabela criada, vamos executar a seguinte query observando seu tempo:

    SQL> set timing on
    SQL> select name
    2 from t
    3 where upper(substr(name,5,2)) in
    4 ('AA','V$','AR','OE','HS','AO','KO','NT','ZO','GQ',
    5 'XH','JO','BS','ML','AL','WW','PX','XX','IU','AO',
    6 'CZ','JX','CQ','CW','CE','CR','CT','CY','CU','CI',
    7 'MT','AH','LV','EG','BB','MO','MY','BQ','AE','AT')
    8 /
    
    ...
    
    NAME
    ------------------------------
    INVENTORY_LIST_TYP
    REJECTION_T
    SUBCATEGORY_REF_LIST_TYP
    SUBCATEGORY_REF_LIST_NESTEDTAB
    CAL_MONTH_SALES_MV
    
    2018 linhas selecionadas.
    
    Decorrido: 00:00:04.32

    O select acima, executou a consulta em 4 segundos e 32 milisegundos. Agora vamos executar a query novamente, mais antes disso, criaremos um index baseado em função na tabela T, e veremos novamente o tempo gasto para retornar a mesma consulta.

    SQL> create index nameidxt
    2 on T
    3 (upper(substr(name, 5,2)));
    
    Índice criado.

    Agora esvaziamos o BUFFER_CACHE, para que o Oracle não apresse a consulta já executada anteriormente.

    SQL> alter system flush buffer_cache;
    
    Sistema alterado.

    Executamos a mesma consulta, mais agora com o index criado na tabela, compare o tempo gasto da primeira vez que executamos, com a segunda vez.

    SQL> select name
    2 from t
    3 where upper(substr(name,5,2)) in
    4 ('AA','V$','AR','OE','HS','AO','KO','NT','ZO','GQ',
    5 'XH','JO','BS','ML','AL','WW','PX','XX','IU','AO',
    6 'CZ','JX','CQ','CW','CE','CR','CT','CY','CU','CI',
    7 'MT','AH','LV','EG','BB','MO','MY','BQ','AE','AT')
    8 /
    
    ...
    
    NAME
    ------------------------------
    INVENTORY_LIST_TYP
    REJECTION_T
    SUBCATEGORY_REF_LIST_TYP
    SUBCATEGORY_REF_LIST_NESTEDTAB
    CAL_MONTH_SALES_MV
    
    2018 linhas selecionadas.
    
    Decorrido: 00:00:00.70

    Se compararmos o tempo gasto nas duas execuções veremos uma diminuição enorme no tempo, de 4.32 para 0.70, ou seja a mesma query executada apenas com a inclusão de um index baseado em função, nos permite estar em um nível maior de performance.

    Apesar do index fornecer para esse tipo de situação, e para outros, notórios tempos de melhorias, é importante saber aonde criar cada um deles, observar atentamente as recomendações citadas acima, para que possa atender a sua real necessidade.