Páginas

3 de abr. de 2016

Quando devo reconstruir ou "fazer REBUILD" de índices?


Olá pessoal,
 
     No artigo de hoje vou comentar sobre um assunto polêmico, que é muito frequente em fóruns de discussão, e que muitos alunos me perguntam nas aulas de SQL Tuning: quando devo reconstruir índices no Oracle Database?

     Há muitos profissionais afirmando que nunca se deve reconstruir (REBUILD) índices, outros que o fazem com frequência, e outros que não sabem muito bem quando fazer isso e o impacto desta operação. É importante ressaltar que estamos falando de REBUILD em índices do tipo b-tree.
   
    Um doc antigo do MOS (Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1)) dizia que eles deveriam ser reconstruídos quando mais que 20% de suas linhas fossem apagadas ou quando eles tivessem um nível de galhos (blevel) maior que 4. Esse doc tinha como referência as versões de 7.3 até 10.2 do Oracle Database, e foi atualizado em 04/02/2016, informando que nas versões mais recentes do Oracle Database devemos considerar o doc Index Rebuild, the Need vs the Implications (Doc ID 989093.1).

My Oracle Support - Doc ID 989093.1

     O doc ID 989093.1 informa que é raro necessitarmos reconstruir índices b-tree, pois eles são auto-gerenciados e auto-balanceados, mas mesmo assim, muitos profissionais justificam o REBUILD nas seguintes situações:
         1- Quando o índice está fragmentado;
         2- Quando o índice cresce muito e o espaço de linhas deletadas não é reutilizado;
         3- Quando há um alto fator de clusterização.

     De um modo geral a Oracle afirma que um REBUILD resolveria o problema de fragmentação (item 1) e espaço (item 2) nos índices apenas temporariamente, pois após a execução de muitos novos INSERTs, UPDATEs e DELETEs na tabela relacionada, o índice teria novamente alguma fragmentação e blocos vazios, e você precisaria executar outro REBUILD para ter novamente o índice em uma "estrutura perfeita". A Oracle recomenda que na maior parte dos casos você não execute este ciclo vicioso de REBUILDs e deixe o índice alcançar o seu equilíbrio naturalmente. Quanto ao item 3, é inútil reconstruir um índice quando ele possui um alto fator de clusterização. Para diminui-lo é necessário reordenar os dados na tabela, e não no índice!

     O REBUILD de índices deve ser evitado sempre que possível, pois ele consome muita CPU e I/O, impactando de forma negativa no desempenho dos sistemas em produção. Além do mais, para analisar índices e verificar quais deveriam sofrer REBUILD, muitos profissionais executam o comando ANALYZE INDEX ... VALIDATE STRUCTURE, que apresenta um sério problema (principalmente em grandes índices): ele faz um lock exclusivo na tabela e isso pode gerar um impacto negativo muito grande no desempenho das operações DML que estiverem ocorrendo nela durante a execução do comando.
  
     De um modo geral, ao invés de um REBUILD, prefira executar o comando ALTER INDEX ... COALESCE. pois ele não tem a mesma sobrecarga do REBUILD, podendo deste modo, ser executado em modo online (com os sistemas em execução). Apesar do COALESCE não fazer uma reestruturação completa dos índices (como o REBUILD faz), ele faz uma desfragmentação combinando os dados dos blocos das folhas, liberando desse modo, blocos que poderão ser usados para novas entradas nos índices, prevenindo-os de crescerem demasiadamente. Existe também o SHRINK. Para mais informações sobre este assunto veja a apresentação Oracle B-Tree Index Internals:Rebuilding The Truth de Richard Foote.
  
     Vimos nos parágrafos anteriores que o REBUILD deve ser evitado na maior parte dos casos, então quando é que ele realmente deve ser executado? Ele deve ser executado em situações bem específicas, como por exemplo:
   
         a) Quando você precisar mover o índice para um novo tablespace: ALTER INDEX ... REBUILD TABLESPACE ...;
  
         b) Quando você tiver um cenário em que uma aplicação removeu, por exemplo, uma grande quantidade de dados da tabela e não há previsão de que novos dados sejam inseridos nela. Para tornar este exemplo mais concreto, pense em uma tabela que tem 1.000.000 de linhas e um determinado sistema removeu 900.000 linhas. Dentro do período de 1 ano você prevê que ninguém mais irá inserir dados nessa tabela. Neste caso faça um REBUILD, pois com a estrutura atual contendo 90% dos blocos vazios, este índice não será eficiente. Além do mais, executar um COALESCE, neste caso, não seria bom. O custo dele ao tentar otimizar a estrutura de um índice em que a quantidade de blocos vazios é maior que a quantidade de blocos ocupados seria bem maior que o custo de um REBUILD. 

     Para verificar se um índice realmente precisa de um REBUILD você pode executar o script compartilhado no doc ID 989186.1 - Script to investigate a b-tree index structure do MOS. Se você quiser aprender mais sobre índices, assista a palestra que irei apresentar no evento DBA BRASIL 1.0 (que irá ocorrer em 16/4/16) ou participe dos meus treinamentos de SQL Tuning.
 
   
CONCLUSÃO:
     Cuidado com o REBUILD de índices, evite-o sempre que possível! A maior parte dos índices não precisa de manutenções, ou precisam apenas de um COALESCE!


Bom pessoal, por hoje é só!
Espero que o artigo lhe seja útil! Se tiver alguma dúvida, deixe um comentário!
  
[]s


Referências:
     - Script: Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1)My Oracle Support

13 comentários:

  1. Fábio parabéns, excelente artigo!

    Eliézio Mesquita

    ResponderExcluir
  2. Fabio, parabéns por esclarecer minhas dúvidas. Queria compartilhar uma situação que tenho hoje.. após nosso fornecedor fazer uma de uma trace que enviamos, ele está solicitando que façamos o rebuild de 2 tabelas (cor) de nosso ERP. Eles alegam que o trace se faz necessário, pois estamos com uma grande lentidão durante a geração de uma obrigação fiscal.

    ResponderExcluir
  3. *Eles alegam que o Rebuild se faz necessário, escrevi errado ali em cima*

    ResponderExcluir
    Respostas
    1. Rafael, é muito comum fornecedores de software pedirem isso. Normalmente o conhecimento do pessoal neste assunto é superficial e muitos pedem para fazer rebuild e/ou coletar estatísticas de objetos, quase sempre sem ter uma análise fundamentada e sem necessidade!

      []s

      Excluir
  4. Excelente artigo Professor!!!
    Vou trocar umas ideias com o pessoal responsavel pelo SGBD onde trabalho e seu artigo será citado :)
    Obrigado
    []s

    ResponderExcluir
  5. Fábio, Os índices que tenham um Clustering_factor ruim em relação ao número de blocos de uma tabela, não seriam beneficiados ? Sei que o consumo de CPU, é significativo, mas fica aqui a pergunta. Numa edição ORACLE SE em relação aos múltiplas features que não podem ser habilitadas e que dão ganho de performance, como paralelismo, Table partition,etc. Não seria um recurso a ser considerado ?

    ResponderExcluir
    Respostas
    1. Fazer rebuild não melhora clustering factor, eu cito isso no artigo e isso a Oracle também explica no MOS Doc ID 989093.1.

      Não entendi direito a 2a pergunta, mas me parece que vc quis perguntar que se pela falta de features no Standard não seria uma boa fazer Rebuilds? Bom... se for essa a pergunta, a resposta é a mesma que já expliquei no artigo, em poucas situações o Rebuild é benéfico (falo sobre as situações no final do artigo).

      []s

      Excluir
  6. Sempre sendo cirúrgico nas explicações! Parabéns

    ResponderExcluir