Desfragmentando Tabelas no Oracle 10g/11g

Neste Post irei mostrar um dos caminhos para realizar a desfragmentação de tabelas no Oracle 10g/11g.

Quando preciso desfragmentar uma tabela?

Se a sua tabela recebe apenas inserções (insert) de dados, você nao precisa se preocupar porque  ela nunca terá problemas de fragmentação.

O problema da fragmentação aparece quando atualizamos ou apagamos (update/delete)  dados na tabela.

Os espaços que  são liberados durante as operações (update/delete) não são imediatamente re-utilizado. Isto resulta em “espaços livre/blocos de dados espalhados nas tablespaces ”  que são chamados de fragmentação de dados.

Vamos entender como o Oracle gerencia os espaços das tabelas.

Acredito que você ja ouviu falar em HWM (High water mark) nós conhecemos como marca d’agua.

Para entender melhor vamos fazer uma analogia, se você olhar dentro de uma caixa d’agua  você vai ver uma linha (marca) proximo a borda entre o lugar que sempre tem agua e o lugar que nunca tem agua.

Do mesmo jeito acontece no Oracle a HWM define uma linha (marca) entre os blocos que foram utilizados e os blocos que nunca foram utilizados.

A marca dágua – HWM é o limite do número de blocos que uma tabela pode estar utilizando.

Quando realizamos uma leitura completa da tabela (full table scan, FTS), o Oracle sempre ira ler todos os blocos utilizados até a marca d’agua. Se existir muitos blocos livres que já foram utilizados até a marca d’agua, estes espaços livres (blocos) também serão lidos , isto gerar I/O extra e seu ambiente irá ter perda de perfomance durante operações de FTS.

Vamos identificar a marca d’agua na tabela e nós iremos saber se a tabela é candidata a reorganização.

Encontraremos 2 tipos de blocos que devemos nós preocupar:

Unused Space/empty_blocks – Que nunca foram utilizado.

Free Space/Blocks – Blocos sofreram  update e delete

SQL> create table teste_hwm as select * from dba_objects;
Table created.
SQL> analyze  table teste_hwm compute statistics;
Table analyzed.
SQL> select blocks "Usados",
 2  empty_blocks "Nunca Utilizado",
 3  num_rows "Total de Linhas"
 4  from user_tables
 5  where table_name='TESTE_HWM';

 Usados Nunca Utilizado Total de Linhas
---------- --------------- ---------------
 1016               7           69072
SQL> delete from teste_hwm
 2  where owner ='SYS';

30219 rows deleted.

SQL> commit;
Commit complete.

Percebe que não houve diferença nos blocos utilizado.

SQL> select blocks "Usados",
 2  empty_blocks "Nunca Utilizado",
 3  num_rows "Total de Linhas"
 4  from user_tables
 5  where table_name='TESTE_HWM';

 Usados Nunca Utilizado Total de Linhas
---------- --------------- ---------------
 1016               7           38853

Se você tem um ambiente que possui tabelas que sofrem muitas modificações (update/delete), periodicamente você ira precisar desfragementar suas tabelas.

Verifique a necessidade de Reorganização

  • Baixo tempo de resposta (por tabela)
  • Alto numero de linhas encadeadas (na verdade migrada)
  • Tabela está crescendo e espaço antigo nao está sendo liberado

Nota: As consultas que utilizam indices não irá ter muito beneficio comparadas com as consultas que utilizam leitura completa (FTS).

Vamos reorganizar

Nas versões anteriores ao Oracle 10g , tinhamos 3 maneiras  de reorganizar as tabelas.

  1. Export, Import e Drop
  2. Usar o recurso Online Object Redefinition
  3. Usar o comando Alter table move (para outra tablespace ou na mesma)

Já no Oracle 10g conseguimos uma outra  maneira de reorganizar os dados.

O comando Shrink: Este comando é aplicado somente em tabelas armazenado em  Tablespace com AUTO SEGMENT SPACE MANAGEMENT.

Antes de usar o SHRINK deve ser habilitado a opção ROW MOVEMENT  na tabela.

SQL> alter table teste_hwm enable row movement;

Table altered.

Existe 2 maneiras de executar o SHRINK.
1 – Executar em Duas etapas – Se estiver preocupado com disponibilidade da tabela
2 – Executar em Uma etapa – Se não estiver preocupado com disponibilidade da tabela

Eu sempre utilizo este comando em duas etapas.

Primeira etapa iremos realizar a reorganização das linhas da tabelas.
Durante esta etapa todas DML’s continuam ocorrendo. Esta etapa é a mais demorada.
Dependendo do tamanho da sua tabela este processo pode demorar horas.

SQL> alter table teste_hwm shrink space compact;

Table altered.
Elapsed: 00:00:02.89

Segunda etapa iremos resetar a marca d’agua (HWM).
Nesta etapa a tabela não pode sofrer DML, porém esta etapa é muito rapida.

SQL> alter table teste_hwm shrink space;

Table altered.
Elapsed: 00:00:00.27

Ou Podemos executar este processo em uma etapa. O Oracle irá realizar a reorganização das linhas (rows ) e ira resetar o HWM.

SQL> alter table teste_hwm shrink space;

Table altered.

Agora vamos ver a marca d’agua:

SQL> analyze  table teste_hwm compute statistics;
Table analyzed.
SQL> select blocks "Usados",
 2  empty_blocks "Nunca Utilizado",
 3  num_rows "Total de Linhas"
 4  from user_tables
 5  where table_name='TESTE_HWM';

 Usados Nunca Utilizado Total de Linhas
---------- --------------- ---------------
590                   26           38853

A diferença entre o anterior é agora é de aproximadamente 50%, isto significa aumento de performance no meu ambiente, pois não precisarei ler blocos desnecessários durante o FTS.

Cuidados a serem tomados :
– Executando o shrink space sem o compact a tabela não poderá ser acessada durante o processo de desfragmentação.
– Caso você execute o shrink space compact e demore muito tempo para executar o shrink space para resetar o HWM, a tabela já poderá estar desfragmentada e este ultimo comando irá demorar mais que o esperado, pois ele irá primeiro desfragmenta os dados depois realizar o reset.
– Dependendo do tamanho da tabela você tem que se preocupar com o Tamanho da Tablespace de UNDO e com o parametrô UNDO_RETENTION.

Vantagens:
– Diferente do “alter table move” os indices não estarão com o status UNUSABLE.
Depois do comando shrink os indices são atualizados também.

– Não precisaremos de downtime para realizar a reorganização das tabelas.

– Não requer espaço extra para executar este processo.

Aproveite !!!

Advertisements

12 Comments on “Desfragmentando Tabelas no Oracle 10g/11g”

  1. Olá,

    Muito bom o artigo, parabéns.

    Abs,
    Henrique

    Like

  2. Márcio says:

    Salvou minha vida! Devo um elogio do meu gerente a você, hahahaha!

    Muito bom!

    Like

  3. Helio Soares says:

    Caro Levi,
    Meu ambiente é em 11.2.03 com OracleRac, tentei fazer o shrink e deu erro, parece que não funciona em ambiente em cluster. Seria isso mesmo? O simples fato de mover a tabela ela já se desfragmenta?
    Obrigado.

    Like

    • Levi Pereira says:

      Olá Helio,
      Não existe restrição da utilização do Shrink em ambiente RAC ou Standalone. O Shrink é suportado em ambos ambiente. Qual foi a mensagem de erro?

      Mover table é mais trabalhoso, porque irá causar indisponibilidade da tabela, irá invalidar todos objetos dependentes (procedures,index,etc.) dependendo do tamanho da tabela irá gerar uma quantidade enorme de archivelog, no final você precisará executar a analise das tabelas com a opçao cascade true.

      Abraços,
      Levi Pereira

      Like

  4. José says:

    Não é preciso desabilitar o row movement no final do processo?

    ex.: alter table teste_hwm disable row movement;

    Like

    • Levi Pereira says:

      row movement enable/disable é apenas uma permissão para alterar o rowid se você fizer uma operação que poderia mudar o rowid das linhas da sua tabela (operações como flashback,compact, update partition_key, etc).

      É preciso desabilitar? Depende da sua aplicação, se você tem alguma aplicação que espera que o rowid seja sempre o mesmo para uma determinada linha na sua tabela então deixe desativado caso contrario não tem problema deixar ativado.

      Atenciosamente,
      Levi Pereira

      Like

      • Jose Sampaio Filho says:

        OK. Entendi. Obrigado.

        Em 31 de janeiro de 2013 15:41, Levi Pereira

        Like

  5. hugo says:

    Muito Bom, Parabéns!

    Like

  6. LoadingXp says:

    Levi,
    Mas como eu vejo qual tabela devo fragmentar? E qual o percentual fragmentado?

    Like

  7. This is really fascinating, You’re a very skilled blogger.
    I’ve joined your feed and look forward to
    looking for more of your great post. Also,
    I’ve shared your website in my social networks

    Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s