Algumas implicações do uso da cláusula COMPRESS [Y/N] disponível no utilitário de exportação tradicional (exp)
Recentemente, me deparei com uma absurda lentidão na importação das estruturas de tabelas e índices contidas em um arquivo dump de exportação (exportado com a cláusula full=y rows=n) gerado por um cliente através do já obsoleto, mas ainda, muito usado, utilitário de exportação tradicional (exp).
A idéia era manter a mesma estrutura lógica de tablespaces do banco de dados de origem, então, iniciei a extração dos comandos DDL de criação das tablespaces à partir do arquivo dump de exportação. A única coisa que eu aproveitei foi o nome das tablespaces, ou seja, o tamanho e o caminho de destino dos arquivos de dados foram alterados, pois não faria sentido utilizar o tamanho dos arquivos de dados de origem (que eram de muitos gigabytes), apenas para armazenar estruturas de tabelas e índices. No entanto, foi esse o motivo da lentidão, pois eu criei os arquivos de dados com um tamanho inicial de 200 MB e com extensão automática habilitada de 50 MB. Está Curioso? Então preste atenção nos exemplos abaixo, pois neste artigo eu irei falar sobre a cláusula COMPRESS disponível no utilitário de exportação (exp).
Sabemos que um segmento de banco de dados, tal como uma tabela ou índice, consiste na extensões (extents) que são alocadas a ele. A figura abaixo mostra as estruturas lógicas de armazenamento de um banco de dados Oracle.
Bom, à medida que uma tabela ou índice cresce, e os extents existentes são preenchidos, cada vez mais extents são alocados a ela. Eventualmente, a tabela pode crescer em um número grande de extents como demonstrado no exemplo abaixo, na qual irei criar uma tabela com 10 milhões de registros.
C:\>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sáb Mai 1 16:46:34 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> create table t1 as select level id from dual
2 connect by level <=10000000;
Tabela criada.
SQL> select segment_name,bytes,extents,initial_extent from user_segments;
SEGMENT_NAME BYTES EXTENTS INITIAL_EXTENT
------------------------------ ---------- ---------- --------------
T1 134217728 87 65536Após a criação da tabela T1 com os 10 milhões de registros, podemos perceber acima que a mesma alocou 134217728 bytes (128 MB) de tamanho em 87 extents, e que o tamanho do extent inicial INITIAL_EXTENT foi de 65 KB.
Abaixo poderemos perceber que a soma da coluna BYTES da view de dicionário de dados USER_EXTENTS dá exatamente o valor de 128 MB, que é o tamanho do segmento T1.
SQL> break on report
SQL> compute sum of bytes on report
SQL> select segment_name,extent_id,bytes,blocks from user_extents;
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
------------------------------ ---------- ---------- ----------
T1 0 65536 8
T1 1 65536 8
T1 2 65536 8
T1 3 65536 8
T1 4 65536 8
T1 5 65536 8
T1 6 65536 8
T1 7 65536 8
T1 8 65536 8
T1 9 65536 8
T1 10 65536 8
T1 11 65536 8
T1 12 65536 8
T1 13 65536 8
T1 14 65536 8
T1 15 65536 8
T1 16 1048576 128
T1 17 1048576 128
T1 18 1048576 128
T1 19 1048576 128
T1 20 1048576 128
T1 21 1048576 128
T1 22 1048576 128
T1 23 1048576 128
T1 24 1048576 128
T1 25 1048576 128
T1 26 1048576 128
T1 27 1048576 128
T1 28 1048576 128
T1 29 1048576 128
T1 30 1048576 128
T1 31 1048576 128
T1 32 1048576 128
T1 33 1048576 128
T1 34 1048576 128
T1 35 1048576 128
T1 36 1048576 128
T1 37 1048576 128
T1 38 1048576 128
T1 39 1048576 128
T1 40 1048576 128
T1 41 1048576 128
T1 42 1048576 128
T1 43 1048576 128
T1 44 1048576 128
T1 45 1048576 128
T1 46 1048576 128
T1 47 1048576 128
T1 48 1048576 128
T1 49 1048576 128
T1 50 1048576 128
T1 51 1048576 128
T1 52 1048576 128
T1 53 1048576 128
T1 54 1048576 128
T1 55 1048576 128
T1 56 1048576 128
T1 57 1048576 128
T1 58 1048576 128
T1 59 1048576 128
T1 60 1048576 128
T1 61 1048576 128
T1 62 1048576 128
T1 63 1048576 128
T1 64 1048576 128
T1 65 1048576 128
T1 66 1048576 128
T1 67 1048576 128
T1 68 1048576 128
T1 69 1048576 128
T1 70 1048576 128
T1 71 1048576 128
T1 72 1048576 128
T1 73 1048576 128
T1 74 1048576 128
T1 75 1048576 128
T1 76 1048576 128
T1 77 1048576 128
T1 78 1048576 128
T1 79 8388608 1024
T1 80 8388608 1024
T1 81 8388608 1024
T1 82 8388608 1024
T1 83 8388608 1024
T1 84 8388608 1024
T1 85 8388608 1024
T1 86 8388608 1024
----------
sum 134217728Agora vem a questão. Se nós exportarmos apenas a estrutura desta tabela, qual será o valor de INITIAL_EXTENT no comando DDL de criação da tabela dentro do arquivo dump de exportação? Veja o exemplo abaixo:
C:\>exp scott/tiger file=t1_compress.dmp tables=t1 rows=n
Export: Release 10.2.0.1.0 - Production on Sáb Mai 1 16:49:13 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Exportação executada no conjunto de caracteres de WE8MSWIN1252 e no conjunto de
caracteres de AL16UTF16 NCHAR OBS: dados (linhas) da tabela não serão exportados
Sobre exportar tabelas especificadas ... via Caminho Convencional ...
. . exportando tabela T1
Exportação encerrada com sucesso, sem advertências.
C:\>findstr /C:"STORAGE" T1_COMPRESS.DMP
CREATE TABLE "T1" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 134217728 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" LOGGING NOCOMPRESSComo esperado, o valor é exatamente o tamanho do segmento, ou seja 134217728 bytes (128 MB). Sabemos que a cláusula COMPRESS do utilitário de exportação tem como padrão o valor “Y”, ou seja, sua função é a de acumular a soma do tamanho de todos os extents do segmento de forma que na importação através do utilitário (imp), o mesmo possa criar o segmento em um único extent grande, quando for possível.
Agora imagine o seguinte cenário. Foi criada uma tablespace contendo um datafile de 200 MB com a opção de extensão automática habilitada de 50 MB. O DBA está aguardando um arquivo dump de exportação contendo apenas as estruturas de tabelas e índices de todos os schemas de um banco de dados imenso. Agora imagine que o DBA (do cliente) que gerou o arquivo dump de exportação tenha utilizado o valor padrão COMPRESS=Y. Agora vamos supor que a maioria dos segmentos contidos no arquivo dump de exportação tenha o valor da cláusula STORAGE (INITIAL) com o tamanho de 50 GB. Já imaginou? Isso quer dizer que ao importar uma única tabela, o meu tablespace que foi criado com um valor inicial de 200 MB, teria que se auto estender 250 vezes (de 50 em 50 megabytes) até conseguir acomodar o segmento de 50 GB!! Isso ainda, se houver espaço em disco disponível! Isso com certeza iria demorar um bocado de tempo …
Portanto, realizando a exportação utilizando a cláusula COMPRESS setada para “N” resolveríamos o problema. Veja o exemplo abaixo.
C:\>exp scott/tiger file=t1_nocompress.dmp tables=t1 rows=n compress=n
Export: Release 10.2.0.1.0 - Production on Sáb Mai 1 16:55:32 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Exportação executada no conjunto de caracteres de WE8MSWIN1252 e no conjunto de
caracteres de AL16UTF16 NCHAR OBS: dados (linhas) da tabela não serão exportados
Sobre exportar tabelas especificadas ... via Caminho Convencional ...
. . exportando tabela T1
Exportação encerrada com sucesso, sem advertências.
C:\>findstr /C:"STORAGE" T1_NOCOPRESS.DMP
CREATE TABLE "T1" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" LOGGING NOCOMPRESSPodemos perceber que o valor de STORAGE (INITIAL) não é mais 134217728 bytes (128 MB), e sim 65536 (65 KB) que é valor padrão.
Abaixo, ao realizar a importação utilizando o primeiro dump gerado (T1_COMPRESS.DMP), podemos perceber que o segmento foi criado com uma alocação de 128 MB sem nem mesmo conter um único registro na tabela.
C:\>imp scott/tiger file=t1_compress.dmp full=y
Import: Release 10.2.0.1.0 - Production on Sáb Mai 1 16:58:25 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Arquivo de exportação criado por EXPORT:V10.02.01 via caminho convencional
importação realizada nos conjuntos de caracteres WE8MSWIN1252 e NCHAR AL16UTF16
. importando objetos de SCOTT para SCOTT
. importando objetos de SCOTT para SCOTT
Importação encerrada com sucesso, sem advertências.
C:\>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sáb Mai 1 17:02:07 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select segment_name,bytes,extents,initial_extent from user_segments;
SEGMENT_NAME BYTES EXTENTS INITIAL_EXTENT
------------------------------ ---------- ---------- --------------
T1 134217728 16 134217728Abaixo, poderemos perceber que não foi possível alocar um único extent, mas 16 para acomodar o segmento T1 na tablespace padrão do usuário SCOTT.
SQL> break on report
SQL> compute sum of bytes on report
SQL> select segment_name,extent_id,bytes,blocks from user_extents;
SEGMENT_NAME EXTENT_ID BYTES BLOCKS
------------------------------ ---------- ---------- ----------
T1 0 8388608 1024
T1 1 8388608 1024
T1 2 8388608 1024
T1 3 8388608 1024
T1 4 8388608 1024
T1 5 8388608 1024
T1 6 8388608 1024
T1 7 8388608 1024
T1 8 8388608 1024
T1 9 8388608 1024
T1 10 8388608 1024
T1 11 8388608 1024
T1 12 8388608 1024
T1 13 8388608 1024
T1 14 8388608 1024
T1 15 8388608 1024
----------
sum 134217728
16 linhas selecionadas.Após a importação, qual seria a alternativa para desalocar este espaço não utilizado? Poderíamos mover o segmento através do comando ALTER TABLE MOVE … ou utilizar o comando TRUNCATE TABLE. Neste caso, ambos teriam o mesmo efeito.
SQL> alter table t1 move storage (initial 1);
Tabela alterada.
SQL> select segment_name,bytes,extents,initial_extent from user_segments;
SEGMENT_NAME BYTES EXTENTS INITIAL_EXTENT
------------------------------ ---------- ---------- --------------
T1 65536 1 16384No caso acima, o valor de INITIAL EXTENT caiu de 128 MB para apenas 16 KB. Agora, vem uma outra questão. E se a tabela contiver registros? Veja o exemplo abaixo. Bom, após realizar a criação da tabela T1 novamente com as 10 milhões de linhas e exportar a mesma … (Lembrando de que COMPRESS=Y é o default).
C:\>exp scott/tiger file=t1.dmp tables=t1
Export: Release 10.2.0.1.0 - Production on Sáb Mai 1 17:18:31 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Exportação executada no conjunto de caracteres de WE8MSWIN1252 e no conjunto de
caracteres de AL16UTF16 NCHAR
Sobre exportar tabelas especificadas ... via Caminho Convencional ...
. . exportando tabela T1 10000000 linhas exportadas
Exportação encerrada com sucesso, sem advertências.Após dropar a tabela T1, irei importar o arquivo dump de exportação gerado.
C:\>imp scott/tiger file=t1.dmp full=y
Import: Release 10.2.0.1.0 - Production on Sáb Mai 1 17:20:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Arquivo de exportação criado por EXPORT:V10.02.01 via caminho convencional
importação realizada nos conjuntos de caracteres WE8MSWIN1252 e NCHAR AL16UTF16
. importando objetos de SCOTT para SCOTT
. importando objetos de SCOTT para SCOTT
. . importando table "T1" 10000000 linhas importadas
Importação encerrada com sucesso, sem advertências.
C:\>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sáb Mai 1 17:23:19 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select segment_name,bytes,extents,initial_extent from user_segments;
SEGMENT_NAME BYTES EXTENTS INITIAL_EXTENT
------------------------------ ---------- ---------- --------------
T1 134217728 16 134217728Pronto. Agora irei reinicializar o valor de STORAGE (INITIAL) com o menor valor possível.
SQL> alter table t1 move storage (initial 1);
Tabela alterada.Atenção! É bom lembrar de que, dependendo do tamanho do segmento, o armazenamento existente da tabela não será dropado até que a tabela seja totalmente realocada (movida) e, para isso, será 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.
SQL> select segment_name,bytes,extents,initial_extent from user_segments;
SEGMENT_NAME BYTES EXTENTS INITIAL_EXTENT
------------------------------ ---------- ---------- --------------
T1 134217728 87 16384Bom, após uma nova exportação, o valor de INITIAL EXTENT a ser armazenado no arquivo dump de exportação para o segmento T1 será de 16384 (16 KB) e não mais 128 MB. Vale a pena salientar que após a realocação do segmento, o número de extents mudou de 16 para 87.
É importante lembrar também de que sempre após a execução do comando ALTER TABLE MOVE, sempre será necessário reconstruir os índices porventura existentes e dependentes da tabela em questão.
Por fim, eu não poderia deixar de comentar de que o uso do utilitário de exportação Export Data Pump (expdp) possui cláusulas que resetam qualquer tipo de configuração STORAGE dos segmentos, ou seja, eliminaria qualquer tipo de “problema” envolvendo alocação de espaço, etc… durante uma importação. Isto porque o parâmetro TRANSFORM do utilitário Import Data Pump (impdp) na qual pode ter o valor SEGMENT_ATTRIBUTES ou STORAGE, além de ser utilizado para fazer com que as tabelas também sejam criadas em um outro tablespace de destino, também permite que o valor STORAGE (transform=storage:N:table) remova a cláusula de armazenamento (STORAGE) dos comandos DDL (Data Definition Language) embutidas nos comandos de criação dos segmentos.