Artigos Banco de Dados Compactando tabelas com o SHRINK em Bancos de Dados Oracle

Neste artigo será comentado sobre um recurso do Oracle Database (que é apresentado nos treinamentos oficiais Oracle Database 11g: Administration Workshop II Ed 2), que tem como objetivo principal, liberar espaço não utilizado nas tabelas (segmentos), além de desfragmentar dados e consequentemente otimizar performance de consultas que realizam Full Table Scan (FTS) e operações que realizam alterações (UPDATE e INSERT).

Para entender o que este comando faz, é necessário entender primeiro o conceito de marca d'água (High Water Mark ou HWM). Marca d’água, em uma tabela, é o ponto que indica onde ela termina, ou seja, o ponto acima do último bloco da tabela. Quando a gente apaga registros de uma tabela, o seu segmento (armazenamento lógico) fica cheio de blocos vazios que são reutilizados apenas quando a gente insere ou atualiza dados (em modo convencional). Se a tabela sofre muitas deleções, ela fica cheia de blocos vazios, abaixo da marca d’água, que podem influenciar negativamente na performance de consultas (Full Table Scan ou FTS), alterações e inserções na tabela, e que ocupam desnecessariamente espaço físico em disco.

Para fazer uma espécie de reorganização da tabela, liberando o espaço dos blocos vazios, movimentando a marca d’água e desfragmentando os dados de uma tabela, um dos métodos atuais mais conhecidos e fáceis de usar, é o SHRINK, que existe no Oracle Database a partir da versão 10G.

Entre os principais benefícios que o SHRINK apresenta, podemos destacar:


Reduz a quantidade de linhas migradas encadeadas e/ou migradas:

Este não é o papel principal do SHRINK e existem outros métodos que são mais indicados para obter melhores resultados ao eliminar linhas encadeadas e/ou migradas de uma tabela, mas o SHRINK também faz este papel. Linhas encadeadas e migradas prejudicam a performance das consultas que precisam acessá-las.

É executado em modo ONLINE e faz atualização dos índices:

É possível executar o SHRINK com o BD em operação (com usuários trabalhando no BD). Essa é uma das maiores vantagens para utilizar este comando, pois existem outros métodos para reorganizar os dados (tais como a movimentação de tabelas entre tablespaces distintos) que precisam ser executados em horários específicos, onde ninguém esteja acessando ou alterando as tabelas em questão.

Não precisa de espaço extra:

Os métodos mais eficientes para efetuar desfragmentação exigem espaço extra para realizar sua tarefa (Ex.: DBMS_REDEFINITION e movimentação de tabelas entre tablespaces distintos), o SHRINK não!

Não dispara triggers:

Como os dados da tabela não são alterados e sim reorganizados através de operações internas de INSERT/DELETE, a operação de SHRINK não dispara triggers.

Apesar do SHRINK apresentar ótimos benefícios, ele também possui algumas limitações ou restrições. Entre elas, podemos destacar as seguintes:

- Não funciona em tabelas clusterizadas, tabelas IOT, tabelas com colunas LONG , alguns tipos de Visões Materializadas (ON-COMMIT e baseadas em ROWID) e tabelas com índices baseados em função;

- Só executa em tablespaces com gerenciamento automático de segmento (Automatic Segment Space Management - ASSM). Não é possível executá-lo em tablespaces com gerenciamento de espaço manual (única opção disponível até a versão 9 do Oracle Database);

- Deve-se habilitar previamente nas tabelas, o recurso ROW MOVEMENT. Este recurso é quem permite reorganizar os dados dentro de uma tabela. Ao criar tabelas ele é desabilitado, por padrão.

Para usar o SHRINK, segue abaixo um passo-a-passo (ver FIGURA 1) utilizando a tabela HR.EMPLOYEES. Para mais informações sobre a tabela HR.EMPLOYEES, leia o artigo Instalando o schema de exemplo HR

Passo-a-passo para executar o SHRINK na tabela HR.EMPLOYEES

Figura 1: Passo-a-passo para executar o SHRINK na tabela HR.EMPLOYEES

PASSO-A-PASSO

Execute os comandos abaixo no SQL Plus, SQL Developer ou ferramenta similar de sua preferência.

Passo 1: Habilitando ROW MOVEMENT:

Neste passo iremos habilitar ROW MOVEMENT para permitir a reorganização da tabela. Comando que deverá ser executado:

Listagem 1: Habilitando ROW MOVEMENT

ALTER TABLE hr.employees ENABLE ROW MOVEMENT;

Passo 2: Executando SHRINK sem ajustar a HWM:

Neste passo iremos reorganizar a tabela sem ajustar a marca d’água, o que não irá eliminar os blocos vazios da tabela. É possível reorganizar a tabela e ajustar a marca d’água ao mesmo tempo (SHRINK SPACE ao invés de SHRINK SPACE COMPACT), porém durante o momento em que a marca d’água está sendo ajustada, a tabela fica bloqueada, por isso, não recomenda-se ajustá-la em horário de produção para não prejudicar os sistemas e usuários que tentarão acessar essa tabela. Recomendo somente compactar a tabela em horário de produção, e ajustar a sua marca d’água em horário noturno ou em um horário em que ninguém estiver acessando a tabela. Comando que deverá ser executado:

Listagem 2: Executando SHRINK sem ajustar a marca d’água

ALTER TABLE hr.employees SHRINK SPACE COMPACT;

Passo 3: Ajustando a HWM:

Neste passo iremos apenas movimentar a marca d’água, visto que, a reorganização já foi efetuada no passo anterior. Se nenhuma reorganização tivesse sido realizada, ela também seria executada neste passo. Como eu havia comentado no passo anterior, esse o comando abaixo em um horário que não prejudique o acesso à tabela pelos usuários e sistemas:

Listagem 3: Executando SHRINK e ajustando a marca d’água

ALTER TABLE hr.employees SHRINK SPACE;

IMPORTANTE: Para deixar bem claro, ressalto novamente que este procedimento pode ser executado em apenas 2 passos se você deseja ao mesmo tempo desfragmentar a tabela e ajustar a HWM. Para isso, basta executar a instrução do Passo 2 sem a palavra COMPACT. Recomenda-se seguir os 3 passos do roteiro, quando for necessário ajustar a HWM no momento em que nenhum usuário ou sistema estiver alterando dados na tabela, pois o ajuste da HWM (3º passo) é o único momento do processo em que a tabela é bloqueada (por um curto período de tempo, mas que poderá causar espera indesejada). Para entender melhor estes passos, veja a Figura 2.

Reorganizando e movimentando a HWM em 2 passos distintos

Figura 2: Reorganizando e movimentando a HWM em 2 passos distintos

Bom pessoal, por é hoje é so!

Referências: