Reorganizando o Tablespace ...

Olá,

Dando prosseguimento ao artigo de Março de 2008 sobre introdução ao conceito de tablespaces, sabemos que o armazenamento dentro de um tablespace é organizado em segmentos e extents. Um extent pode ser alocado para um segmento e, portanto, é considerado usado; caso contrário, quando um extent não é alocado a nenhum segmento, ele é considerado livre. Pode ser um pouco complicado de entender, mas com o tempo, nos tablespaces gerenciados por dicionário, a alocação e desalocação dos extents fazia com que o espaço livre se tornasse fragmentado dando origem a fragmentação do tipo "honeycomb" na qual os extents livres ficavam adjacentes uns aos outros, e a fragmentação do tipo "buble" na qual os extents livres ficavam separados uns dos outros pelo espaço usado. A fragmentação "honeycomb" podia ser removida por um processo chamado aglutinação utilizando o comando ALTER TABLESPACE ... COALESCE para aglutinar o espaço livre contíguo explicitamente em um tablespace. Portanto, esta técnica era utilizada porque apenas o espaço contíguo poderia ser usado para alocar os extents. Já para remover a fragmentação do tipo "buble" era necessário fazer uma reorganização completa do tablespace utilizando os utilitários export (com o parâmetro COMPRESS=Y) e import para reorganizar em um único extent grande aqueles objetos que estavam fragmentados em um número grande de extents pequenos. A partir do Oracle 8i, este trabalho se tornou mais fácil com a inclusão do comando ALTER TABLE ... MOVE para mover a tabela dentro do próprio tablespace ou em outro tablespace. Em resumo, não estou querendo dizer que o comando COALESCE foi aposentado, mas com o advento do tablespace gerenciado localmente, foi eliminada a necessidade de aglutinar as extensões livres porque o gerenciamento local de extensões rastreia automaticamente o espaço livre adjacente. De qualquer forma, agora os tablespaces gerenciados por dicionário são coisas do passado.

O motivo de eu estar escrevendo este artigo partiu de uma discussão no fórum OTN da Oracle sobre algumas dúvidas relacionadas ao uso dos comandos ALTER TABLE ... MOVE (disponível a partir do Oracle 8i) e ALTER TABLE ... SHRINK (disponível a partir do Oracle 10g) no que diz respeito a realocação e compactação de tabelas de forma a poder ajustar a "High Water Mark" das mesmas, bem como a de utilizar de forma eficiente o espaço livre no tablespace sem a necessidade de ter que redimensioná-lo toda vez que não houver espaço suficiente para novas tabelas, etc... Outra coisa importante que irei mostrar é como podemos utilizar estes dois comandos (que tem propósitos diferentes) juntos para ajustar a HWM de um arquivo de dados a fim de poder diminuir o seu tamanho evitando receber as famosas mensagens "ORA-01658: não é possível criar a extensão INITIAL para o segmento no espaço de trabalho " ou "ORA-03297: o arquivo contém dados usados além do valor solicitado de RESIZE" ou até mesmo evitando receber o erro "ORA-01652: não é possível estender o segmento temp. em 8 no [tablespace]" ao tentar mover uma tabela. Na verdade, o comando ALTER TABLE ... MOVE pode ser usado para reorganizar as extensões das tabelas realocando os dados em um segmento novo (normalmente o mais próximo possível do início do datafile) no mesmo tablespace ou em outro tablespace eliminando assim problemas de linhas migradas e/ou encadeadas, além de ajustar a HWM. Portanto, o armazenamento existente da tabela não é dropado até que a tabela seja totalmente realocada, e para isso, é necessário um espaço livre no tablespace do tamanho da tabela que está sendo movida. Se não houver este espaço livre, então o erro ORA-01652 será emitido pelo Oracle. Vale a pena salientar que os usuários podem executar consultas na tabela durante a realocação, entretanto, as operações DML tais como aquelas que inserem, atualizam ou excluem dados, não são permitidas até que a realocação esteja completa.

Com relação ao espaço alocado mas não utilizado resultante de operações de update e delete dentro de um segmento é chamado de espaço livre fragmentado. Para reclamar este espaço para ser utilizado por outras tabelas, poderíamos utilizar técnicas que eu chamo de ETI (exportar, truncar e importar a tabela) ou o método CTAS (create table as select ...), entre outros, mas isso não será necessário se você estiver usando o Oracle 10g, porque é aí que o comando SHRINK entra em ação. Este comando realiza duas tarefas: O primeiro passo faz a compactação do segmento movendo as linhas para os blocos livres o máximo possível para o início do segmento e o segundo passo ajusta a "High Water Mark".

- ALTER TABLE ... ENABLE ROW MOVEMENT -- habilita a movimentação de linhas (ROWID's)
- ALTER TABLE ... SHRINK SPACE COMPACT -- realiza apenas o primeiro passo
- ALTER TABLE ... SHRINK SPACE -- realiza os dois passos (compactação e ajuste da HWM)

A opção CASCADE pode ser utilizada para compactar objetos dependentes como por exemplo os índices. É importante lembrar que para reorganizar um segmento usando o comando SHRINK é necessário que o tablespace use o gerenciamento automático de segmento ou ASSM (Automatic Segment Space Management). Enfim, para um melhor entendimento sobre estes comandos, vamos a um exemplo prático.

SQL> connect / as sysdba
Conectado.

-- Irei criar um usuário e um tablespace de teste com o tamanho inicial de 1 MB
SQL> create tablespace tbs_teste
  2  datafile '/u01/oradata/BD01/teste01.dbf' size 1m
  3  extent management local
  4  segment space management auto;

Tablespace criado.

SQL> create user teste identified by teste
  2  default tablespace tbs_teste quota unlimited on tbs_teste;

Usuário criado.

SQL> grant connect,resource to teste;

Concessão bem-sucedida.

SQL> connect teste/teste
Conectado.

-- Irei criar e popular uma tabela que chamarei de T
SQL> create table t (id  number);

Tabela criada.

SQL> insert into t select level from dual connect by level <= 45000; 
45000 linhas criadas.
SQL> commit;
Validação completa.
-- Irei deletar todas as linhas desta tabela
SQL> delete from t;
45000 linhas deletadas.
SQL> commit;
Validação completa.
-- Irei analisar esta tabela a fim de gerar algumas informações sobre o segmento
SQL> analyze table t compute statistics;

Tabela analisada.

SQL> select
  2      a.blocks, b.blocks hwm, b.empty_blocks
  3    from
  4      user_segments a, user_tables b
  5    where
  6      a.segment_name=b.table_name and b.table_name='T';

    BLOCKS        HWM EMPTY_BLOCKS
---------- ---------- ------------
        80         73            7

Podemos ver acima que foram alocados 80 blocos para o segmento T sendo que 73 blocos foram utilizados e 7 não foram utilizados, ou seja, 7 blocos que estão acima da HWM. Podemos concluir que mesmo após o comando DELETE, podemos ver que o espaço nas extensões continuam alocados para o segmento T.

SQL> select extent_id,blocks,block_id from dba_extents
  2  where segment_name='T' and owner='TESTE';

 EXTENT_ID     BLOCKS   BLOCK_ID
---------- ---------- ----------
         0          8          9
         1          8         17
         2          8         25
         3          8         33
         4          8         41
         5          8         49
         6          8         57
         7          8         65
         8          8         73
         9          8         81

10 linhas selecionadas.

Para complementar, executando uma consulta na view DBA_EXTENTS podemos ver que 10 extents foram alocados para segurar os 80 blocos, ou seja, cada extent contém 8 blocos de dados. Para uma melhor visualização, veja como ficou o mapa do tablespace TBS_TESTE

-- Irei agora criar e popular uma tabela que chamarei de X
SQL> create table x (id number);

Tabela criada.

SQL> insert into x select level from dual connect by level <= 20000; 
20000 linhas criadas.
SQL> commit;

Validação completa.

Após a criação e inserção de dados na tabela T, podemos ver abaixo no mapa do tablespace TBS_TESTE, que o Oracle alocou novos extents para a tabela X, ou seja, mesmo que a tabela T esteja "vazia", ela ainda assim deterá os 80 blocos para futuras inserções, a menos que a HWM seja ajustada com o comando TRUNCATE, ou com os métodos ETI, CTAS ou com o comando MOVE, ou com o comando SHRINK que irei mostrar mais adiante.

SQL> alter table t move;
alter table t move
*
ERRO na linha 1:
ORA-01658: não é possível criar a extensão INITIAL para o segmento no espaço de
trabalho TBS_TESTE

Como o tablespace está com o AUTOEXTEND desabilitado, podemos ver acima que o comando MOVE falhou porque não há espaço disponível no tablespace para acomodar uma cópia temporária do segmento T que está sendo movido.

SQL> alter table t enable row movement;

Tabela alterada.

SQL> alter table t shrink space;

Tabela alterada.

Após executar o comando SHRINK, podemos ver no mapa do tablespace TBS_TESTE abaixo que a HWM do segmento T foi ajustada, e o espaço livre foi liberado para uso para outros segmentos.

SQL> select extent_id,blocks,block_id from dba_extents where segment_name='T'
  2  and owner='TESTE';

 EXTENT_ID     BLOCKS   BLOCK_ID
---------- ---------- ----------
         0          8          9

SQL> analyze table t compute statistics;

Tabela analisada.

SQL> select
  2      a.blocks, b.blocks hwm, b.empty_blocks
  3    from
  4      user_segments a, user_tables b
  5    where
  6      a.segment_name=b.table_name and b.table_name='T';

    BLOCKS        HWM EMPTY_BLOCKS
---------- ---------- ------------
         8          1            7

Podemos confirmar acima que realmente a HWM do segmento T foi resetada.

-- Irei mostrar o que acontecerá se movermos a tabela X
SQL> alter table x move;

Tabela alterada.

Podemos perceber no mapa do tablespace abaixo, que a tabela X foi movida o máximo possível para o início do arquivo de dados, possibilitando assim que o tamanho do mesmo possa ser redimensionado para baixo caso necessário. Por exemplo, de 1 MB para 500 KB.

SQL> drop table t;

Tabela eliminada.

SQL> drop table x;

Tabela eliminada.

Bem, de vez em quando é comum ouvir questões como alguém que não está conseguindo criar uma tabela nova ou algum segmento que não está conseguindo alocar um extent por falta de espaço no tablespace, e para piorar, não é possível aumentar o tamanho do arquivo de dados ou criar um novo arquivo e dados por falta de espaço em disco. Então, de acordo com o exemplo prático acima, mostrarei abaixo o que é possível fazer com estes dois comandos (SHRINK e MOVE) juntos, de forma a tirar melhor proveito do espaço desperdiçado dentro de um tablespace. Também irei mostrar como é possível tentar diminuir o tamanho de um arquivo de dados que está ocupando muito espaço em disco, mesmo quando o erro ORA-03297 é emitido pelo Oracle.

-- Irei habilitar a extensão automática do arquivo de dados
SQL> alter database datafile '/u01/oradata/BD01/teste01.dbf' autoextend on;

Banco de dados alterado.

-- Irei criar uma tabela nova A
SQL> create table a (id number);

Tabela criada.

SQL> insert into a select level from dual connect by level <= 100000; 
100000 linhas criadas.
-- Irei criar uma tabela nova B
SQL> create table b as select * from a;
Tabela criada.
-- Irei criar uma tabela nova C
SQL> create table c as select * from b;

Tabela criada.

Após a criação das 3 tabelas acima, o tablespace ficou com um tamanho final de 6272 KB e abaixo podemos ver como ficou o mapa de blocos do tablespace:

SQL> connect / as sysdba
Conectado.

-- Irei desabilitar a extensão automática do arquivo de dados
SQL> alter database datafile '/u01/oradata/BD01/teste01.dbf' autoextend off;

Banco de dados alterado.

-- Obtendo informações do arquivo de dados para fins de redimensionamento. De agora
-- em diante, farei referência a este SQL usando o nome resize_information;
SQL> set verify off
SQL> column value new_val blksize
SQL> select value from v$parameter where name = 'db_block_size';

       VALUE
------------
        8192

SQL> select a.file_id,
  2         a.file_name,
  3         ceil((nvl(hwm,1)*&&blksize)/1024) smallest,
  4         ceil(blocks*&&blksize/1024) currsize,
  5         ceil(blocks*&&blksize/1024) -
  6         ceil((nvl(hwm,1)*&&blksize)/1024) savings
  7  from dba_data_files a,
  8       (select file_id, max(block_id+blocks-1) hwm
  9        from dba_extents where owner='TESTE'
 10        group by file_id) b
 11  where a.file_id = b.file_id;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u01/oradata/BD01/teste01.dbf            6208       6272         64

Podemos verificar acima que o tamanho atual do arquivo de dados é de 6272 KB e que poderemos diminuir o tamanho do mesmo para no máximo 6208 KB, já que 64 KB é o espaço livre disponível acima da HWM do arquivo de dados.

SQL> alter database datafile 15 resize 6208k;

Banco de dados alterado.

Após reduzir o tamanho do arquivo de dados, podemos ver na figura abaixo como ficou o mapa de blocos do tablespace:

-- Irei agora deletar as linhas das tabelas A e B de forma a restar apenas 1 linha
-- para cada tabela
SQL> connect teste/teste
Conectado.

SQL> delete from a where rownum <= 99999; 
99999 linhas deletadas.
SQL> delete from b where rownum <= 99999; 
99999 linhas deletadas.
SQL> commit;
Validação completa.
SQL> connect / as sysdba
Conectado.

-- Obtendo informações do arquivo de dados para fins de redimensionamento
SQL> @resize_information;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u01/oradata/BD01/teste01.dbf            6208       6208          0

Podemos notar como já era esperado, que os blocos utilizados pelas linhas deletadas não foram desalocados após as operações de delete, isso porque que a HWM não é ajustada por comandos DML. Portanto, estes blocos ainda fazem parte das extensões pertencentes a estes segmentos (A e B), então este "espaço livre" só poderá ser utilizado pelos mesmos. Neste caso, como fazer então para que estes blocos livres fiquem disponíveis para outros segmentos? Abaixo irei demonstrar o uso do comando SHRINK.

-- Irei compactar (fazer com que os blocos fiquem adjacentes/contíguos) e
-- opcionalmente ajustar a HWM dos segmentos A e B
SQL> connect teste/teste
Conectado.

SQL> alter table a enable row movement;

Tabela alterada.

SQL> alter table a shrink space cascade;

Tabela alterada.

SQL> alter table b enable row movement;

Tabela alterada.

SQL> alter table b shrink space cascade;

Tabela alterada.

Acima eu utilizei a opção CASCADE apenas para demonstrar que a mesma pode ser utilizada para compactar objetos dependentes, como por exemplo, os índices caso existam. Então, após realizar a compactação dos dados e ajustar a HWM dos segmentos A e B, poderemos ver abaixo como ficou o mapa de blocos do tablespace:

Eu deixei a tabela C por último e propositalmente pelo seguinte motivo: Apesar de eu não ter realizado nenhuma operação DML na tabela C, podemos ver abaixo que a mesma está de alguma forma fragmentada pelo fato de o tablespace ter sido criado com a opção padrão AUTOALLOCATE, o que significa que toda vez que o segmento precisar de espaço para crescer a extensão alocada será de 1 MB, o que significa que foi alocado mais espaço do que o necessário como mostrado abaixo:

SQL> analyze table c compute statistics;

Tabela analisada.

SQL> select
  2      a.blocks, b.blocks hwm, b.empty_blocks
  3    from
  4      user_segments a, user_tables b
  5    where
  6      a.segment_name=b.table_name and b.table_name='C';

    BLOCKS        HWM EMPTY_BLOCKS
---------- ---------- ------------
       256        165           91

Deste modo, poderemos reclamar algum espaço livre compactando a tabela C como mostrado abaixo:

SQL> alter table c enable row movement;

Tabela alterada.

SQL> alter table c shrink space cascade;

Tabela alterada.

SQL> analyze table c compute statistics;

Tabela analisada.

SQL> select
  2      a.blocks, b.blocks hwm, b.empty_blocks
  3    from
  4      user_segments a, user_tables b
  5    where
  6      a.segment_name=b.table_name and b.table_name='C';

    BLOCKS        HWM EMPTY_BLOCKS
---------- ---------- ------------
       168        165            3

Após compactar a tabela C, poderemos ver abaixo como ficou o mapa de blocos do tablespace

Dando prosseguimento ao exemplo prático, e após compactar a tabela C, podemos verificar abaixo que o tamanho atual do arquivo de dados é de 6208 KB e que poderemos diminuir o tamanho do mesmo para no máximo 5504 KB, já que 704 KB é o espaço livre disponível acima da HWM do arquivo de dados.

SQL> connect / as sysdba
Conectado.

SQL> @resize_information;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u01/oradata/BD01/teste01.dbf            5504       6208        704

SQL> alter database datafile 15 resize 5504k;

Banco de dados alterado.

Após reduzir o tamanho do arquivo de dados, podemos ver na figura abaixo como ficou o mapa de blocos do tablespace:

Após todas estas operações, irei verificar realmente quanto de espaço alocado existe para as tabelas A, B e C utilizando o SQL abaixo:

SQL> break on report
SQL> compute sum of blocks on report
SQL> compute sum of extents on report
SQL> compute sum of kbytes on report
SQL> select
  2     owner,
  3     segment_name,
  4     blocks,
  5     extents,
  6     bytes/1024 KBYTES
  7  from
  8     dba_segments
  9  where
 10     tablespace_name = 'TBS_TESTE';

OWNER                SEGMENT_NAME               BLOCKS    EXTENTS     KBYTES
-------------------- ---------------------- ---------- ---------- ----------
TESTE                A                               8          1         64
TESTE                B                               8          1         64
TESTE                C                             168         17       1344
                                            ---------- ---------- ----------
sum                                                184         19       1472

De acordo com o resultado acima, podemos notar que as tabelas A, B e C estão utilizando um total de 1472 KB de dados em 184 blocos. Será possível reduzir o tamanho do arquivo de dados para 1472 KB?

SQL> alter database datafile 15 resize 1472k;
alter database datafile 15 resize 1472k
*
ERRO na linha 1:
ORA-03297: o arquivo contém dados usados além do valor solicitado de RESIZE

Apesar de haver espaço livre suficiente no arquivo de dados para podermos reduzir o seu tamanho, vimos que não foi possível fazê-lo pelo fato deste espaço livre estar fragmentado dentro do arquivo de dados. Realmente, o que está impedindo a realização desta tarefa é o último bloco (HWM) utilizado pela tabela C. Neste caso, não adiantará utilizar novamente o comando SHRINK pelo fato de a mesma já estar totalmente compactada. É agora que entra em ação o comando ALTER TABLE ... MOVE para resolver este problema e mover os segmentos para o mais próximo possível do início do arquivo de dados.

SQL> alter table a move;

Tabela alterada.

SQL> alter table b move;

Tabela alterada.

SQL> alter table c move;

Tabela alterada.

SQL> @resize_information;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u01/oradata/BD01/teste01.dbf            2432       5504       3072

Após realocarmos as tabelas, podemos verificar acima que o tamanho atual do arquivo de dados é de 5504 KB e que poderemos diminuir o tamanho do mesmo para no máximo 2432 KB, já que 3072 KB é o espaço livre disponível acima da HWM do arquivo de dados.

Podemos notar na figura acima, que as tabelas foram realocadas para o início do arquivo de dados, mas por algum motivo a tabela C não ficou alocada como eu esperava. Bem, isto apenas comprova que não temos controle sobre onde o Oracle alocará os blocos. Neste caso, irei reduzir o tamanho do arquivo de dados o máximo possível com o tamanho de 2432 KB.

SQL> alter database datafile 15 resize 2432k;

Banco de dados alterado.

SQL> @resize_information;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u04/oradata/BD01/teste01.dbf            2432       2432          0

Pronto, podemos verificar que pelo menos conseguimos reduzir o tamanho do arquivo de dados de 6272 KB para 2432 KB apenas utilizando os comandos SHRINK e MOVE. Ao final de tudo, o mapa de blocos do tablespace ficou como mostrado na figura abaixo:

Para finalizar, no Oracle Enterprise Manager Database Control existe um utilitário chamado Supervisor de Segmento que pode ser utilizado para fornecer dicas sobre o fato de um objeto ser ou não um bom candidato para uma operação de compactação com base no nível de fragmentação de espaço de um objeto. Neste caso, o supervisor de segmento determina se os objetos possuem espaço não usado que pode ser liberado levando em consideração requisitos de espaço futuro. O cálculo de espaço futuro estimado é baseado em um histórico de tendências. O supervisor também gera relatórios sobre a tendência de crescimento histórico dos segmentos. É possível ainda utilizar usar essas informações para o planejamento da capacidade e para chegar a uma decisão informada sobre quais segmentos deverão ou não ser compactados.

Algumas considerações sobre o comando SHRINK no Oracle 10g:

Só é possível realizar a compactação de tabelas utilizando o comando SHRINK em tablespaces com ASSM.

Os tipos mais comuns de segmentos passíveis de compactação são:

- Tabelas heap e tabelas organizadas por índice
- Índices
- Partições e subpartições
- Views materializadas e logs de views materializadas
- Segmentos LOB

Não é possível compactar:

- Segmentos de UNDO

- Segmentos temporários (No oracle 11g já é possível compactar o tablespace temporário)

- Tabelas clusterizadas
- Tabelas que contenham colunas do tipo LONG

Com relação ao comando ALTER TABLE ... MOVE, vale a pena salientar que após finalizar a realocação da tabela, será necessário reconstruir os índices da tabela (rebuild) , caso os mesmos existam.

Maiores informações podem ser encontradas na documentação oficial da Oracle: Shrinking Database Segments Online e Moving a Table to a New Segment or Tablespace