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_sparano as select * from dba_objects;
Table created.
SQL> analyze table teste_sparano 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_SPARANO';
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_SPARANO';
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.
Export, Import e Drop
Usar o recurso Online Object Redefinition
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_sparano 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_sparano 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_sparano 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_sparano shrink space;
Table altered.
Agora vamos ver a marca d’agua:
SQL> analyze table teste_sparano 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_SPARANO';
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.
Nenhum comentário:
Postar um comentário