Desfragmentação de tabelas no Oracle (parte 1)

Olá pessoal,

     Como extensão à palestra "Top 5 Tuning for Oracle and SQL Server" apresentada em 6/5/17 no DBA BRASIL 2.0, resolvi escrever este artigo para explicar em mais detalhes o assunto "Fragmentação de dados em tabelas" no Oracle Database, e como eliminá-la, abordando os métodos mais eficientes para realizar esta atividade.

     Para iniciar é importante entender primeiramente o que é fragmentação de dados, e neste caso, para não ter que reinventar a roda, sugiro a leitura do artigo "Fragmentação no banco de dados Oracle : mitos e meias-verdades" do colega José Laurindo Chiappa, assunto que foi o foco de sua palestra "Mitos, meias-verdades, enganos e Fraudes da Fragmentação" apresentada no DBA BRASIL 2.0. Ressalto apenas que Fragmentação em tabelas é muito difícil de ocorrer no Oracle quando elas estão armazenadas em Tablespaces Gerenciados Localmente (LMTs), que são o tipo de tablespace padrão e recomendado no Oracle Database a partir da versão 10G. Para mais informações sobre LMTs e tablespaces em geral, recomendo a leitura da nota Master Note: Overview of Oracle Tablespace Management (Doc ID 1493350.1) no MOS.
  

     Já que a partir da versão 10G, se utilizarmos LMTs, não precisamos mais nos preocupar "tanto" com fragmentação de dados, por que ainda ouvimos falar muito disso? Para obter essa resposta, precisamos entender melhor o que a Oracle define como fragmentação.  Segundo a nota Various Aspects of Fragmentation (Doc ID 186826.1), fragmentação pode ocorrer dentro de Tablespaces, Tabelas Heap e Índices B-Tree, e a própria Oracle diz que existem muitos equívocos sobre os efeitos negativos que ela pode causar (ver Imagem 01). Segundo essa mesma nota, fragmentação dentro de Tabelas Heap ocorre quando linhas são deletadas, atualizadas ou inseridas.

     Bom, a verdade é que, aquilo que normalmente entende-se ainda como problemas de fragmentação de dados, eu diria que não são exatamente problemas, mas sim fenômenos que ocorrem naturalmente em tabelas que sofrem muitos DMLs, e que em poucas ocasiões, representam  ainda problemas aos sistemas que acessam estes objetos. Os problemas/fenômenos são:

         a) Blocos vazios derivados de dados apagados (DELETEs);
         Os blocos vazios surgem quando linhas de uma tabela são apagadas. Estes espaços vazios em blocos formatados dentro do segmento da tabela são naturalmente reutilizados por futuros INSERTs "convencionais".

         b) Linhas encadeadas ou migradas (LEMs) derivadas de INSERTs ou UPDATEs:
         "Linhas migradas" são geradas quando uma linha está sendo atualizada (UPDATE) e os dados não cabem mais no bloco atual em que ela está armazenada. Neste caso, o Oracle migra a linha para outro bloco dentro do mesmo segmento, e cria uma referência ou ponteiro para esse novo bloco dentro do bloco antigo. "Linhas encadeadas" são geradas quando os dados a serem inseridos de uma linha não cabem em um único bloco e portanto ele é gravado em N blocos. Tabelas que normalmente geram LEMs: tabelas com mais de 255 colunas (Oracle quebra ela em partes) ou que possuem colunas com tipos de dados LONG, LONG RAW, BLOB ou CLOB.

Quando os blocos vazios ou LEMs representam problemas?

     Os blocos vazios podem prejudicar a performance de operações de Full Table Scan (FTS), pois o Otimizador estará lendo estes blocos "desnecessariamente", o que irá gerar mais I/O e maior tempo de processamento. Se após apagar uma grande quantidade de dados você souber que novas linhas não serão inseridas em um futuro próximo, recomendo neste caso, que você execute um SHRINK na tabela (veremos mais detalhes disso adiante) visando otimizar as operações de FTS nela. Se foi apagada uma pequena quantidade de linhas ou se você sabe que novas linhas serão inseridas em breve, não se preocupe com os "buracos" (blocos vazios) na tabela, pois eles serão reutilizados naturalmente!

     As LEMs, por sua vez, normalmente ocasionam problemas de performance em operações de "Index Scan" (que realizam single block fecthes), pois no caso de linhas migradas, por exemplo, os índices continuarão referenciando o bloco antigo, e ao acessar estes dados, ao invés do Oracle ler apenas 1 bloco, ele passará a ler 2 blocos. Poucas LEMs normalmente não representam problemas de performance consideráveis em uma tabela, porém já tive problemas com elas em um processo diário que gera o dump full de um determinado BD que administro. Existiam muitas LEMs em uma determinada tabela deste BD, e por causa delas, o "export" que normalmente demorava 1 hora para finalizar, estava demorando muito muito mais e começou a falhar porque não conseguia finalizar dentro do horário janela de 2 horas atribuído àquele processo. Depois de eliminar as LEMs (reinserindo as devidas linhas) tudo voltou ao normal!
       
     Seguem abaixo algumas perguntas e respostas que nos ajudarão a entender melhor este assunto:  

Como identificar os "buracos de uma tabela?

     Existem diversas formas de identificar blocos vazios no segmento de uma tabela. Seguem abaixo algumas que eu indico pela simplicidade de uso ou eficiência:

a) ANALYZE TABLE: 

     Execute o comando "ANALYZE TABLE nome_tabela COMPUTE STATISTICS" e depois consulte na visão DBA_TABLES o valor da coluna EMPTY_BLOCKS. Se for maior que zero, existem blocos vazios dentro do segmento da tabela. Este é um método simples, porém ainda assim, eficiente. Exemplo:

  

    ANALYZE TABLE ECOMMERCE.PEDIDO COMPUTE STATISTICS;

    select  blocks "Blocos Usados", empty_blocks "Blocos vazios", 

            num_rows "Total de Linhas" 

    from    dba_tables 

    where   owner = 'ECOMMERCE'

    AND     table_name='PEDIDO';

b) Procedure DBMS_SPACE.SPACE_USAGE:

     O pacote DBMS_SPACE possui diversos procedures para avaliar o espaço dentro dos segmentos. Recomendo o uso do procedure SPACE_USAGE dentro dele. Ele fornece mais informações sobre o uso dos blocos de um segmento do que o ANALYZE TABLE. As principais informações são: total de blocos cheios, total de blocos não-usados e que nunca foram formatados para uso, e total de blocos com espaço livre entre diversas faixas percentuais (de 0 a 25%, de 25 a 50%, de 50 a 75% livres e acima de 75%). Segue abaixo um bloco PL/SQL de exemplo para executar o procedure DBMS_SPACE.SPACE_USAGE e ver os seus resultados:

set serveroutput on

declare

   v_unformatted_blocks number;

   v_unformatted_bytes number;

   v_fs1_blocks number;

   v_fs1_bytes number;

   v_fs2_blocks number;

   v_fs2_bytes number;

   v_fs3_blocks number;

   v_fs3_bytes number;

   v_fs4_blocks number;

   v_fs4_bytes number;

   v_full_blocks number;

   v_full_bytes number;

begin

   dbms_space.space_usage ('<schema>', '<table name>', 'TABLE',   

         v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, 

         v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes,               v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

   dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

   dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

   dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

   dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

   dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

   dbms_output.put_line('Full Blocks = '||v_full_blocks);

end;

/

  

Como eliminar os "buracos de uma tabela?

     Se você já sabe que a tabela está cheia de buracos e prevê que tão cedo eles não serão preenchidos novamente por inserções convencionais, acho então que vale a pena executar algo para eliminá-los. Entre os diversos procedimentos disponíveis para realizar essa tarefa de manutenção, citarei os métodos mais antigos, tais como: CTAS, ALTER TABLE MOVE, EXPORT/TRUNCATE/IMPORT; e os mais modernos, que indico atualmente, tais como: SHRINK e DBMS_REDEFINITION.

     Indico primeiramente o SHRINK porque entre todos os métodos disponíveis ele é o que causa menor impacto no ambiente de produção, permitindo executar a rotina de manutenção com o BD online e impacto quase zero nos DMLs que estiverem ocorrendo durante a sua execução. Ele não é o método mais eficaz (que elimina 100% dos buracos), mas funciona bem e recomendo usá-lo sempre que possível. Ele possui algumas restrições de uso, portanto, nem sempre você conseguirá utilizá-lo. Para aprender mais sobre o SHRINK recomendo a leitura do artigo "Compactando tabelas com o SHRINK em Bancos de Dados Oracle". É importante ressaltar também que o SHRINK, em alguns casos poderá aumentar o fator de clusterização de índices pré-existentes, e nestes casos, poderia ser necessário fazer um REBUILD neles (ver mais informações em Defragmentation Can Degrade Query Performance).

     Em segundo lugar, recomendo o uso do pacote DBMS_REDEFINITION, se for um pré-requisito ter a menor indisponibilidade possível, ou um ALTER TABLE MOVE se for possível gerar indisponibilidade do objeto enquanto o procedimento de manutenção estiver sendo executado. Particularmente não gosto muito de usar o DBMS_REDEFINITION, pois o que ele faz é criar uma nova tabela e recriar nela as constraints, índices e outros objetos dependentes. Nessa recriação ele perde o nome original das constraints NOT NULL e muitas vezes não consegue recriar constraints do tipo PK e FK, por isso, prefiro usar o ALTER TABLE MOVE se houver a possibilidade de deixar a tabela indisponível para DMLs (até o 11G) durante a execução deste processo de manutenção. Este último recria dentro do mesmo tablespace, um novo segmento para a tabela! Para aprender mais sobre o ALTER TABLE MOVE recomendo o artigo Reorganizando o Tablespace... do colega Eduardo Legatti. No 12c este recurso evoluiu e agora é possível fazer o ALTER MOVE ONLINE, que ainda possui algumas restrições, mas não gera mais indisponibilidade para os DMLs convencionais. Para aprender mais sobre o pacote DBMS_REDEFINITION recomendo a leitura da nota DBMS_REDEFINITION ONLINE REORGANIZATION OF TABLES (Doc ID 149564.1), mas em breve escreverei outro artigo comentando mais sobre ele.

     CLIQUE AQUI para ver a 2ª parte deste artigo onde explano sobre as questões abaixo:
          - Como evitar ou minimizar a quantidade de LEMs em uma tabela?
          - Como identificar e eliminar LEMs?
  

Espero que você tenha gostado e que este artigo lhe seja útil!

Se você tem dúvidas deixe um comentário! Se você gostou também deixe um comentário, pois é muito importante para mim o seu feedback para eu saber se estou escrevendo sobre algo que está enriquecendo o conhecimento dos leitores deste blog!

[]s

Referências:

   - Difference between chained rows and migrated rows, Oracle Database Tips by Burleson Consulting, April 31, 2015
   - Various Aspects of Fragmentation (Doc ID 186826.1), MOS