Olá,
No Oracle 11g, podemos escolher dois modos diferentes de criar e armazenar colunas LOB (Large Objects) no banco de dados. O modo BASICFILE é o único disponível até a versão do Oracle 10g. Com a versão do Oracle 11g temos, além do modo BASICFILE, o modo SECUREFILE, que trouxe várias melhorias em relação à performance (leitura/escrita), além de outras como deduplicação, compressão e criptografia para as colunas do tipo BLOB e CLOB.
O Oracle SecureFiles é uma infraestrutura de armazenamento totalmente nova que foi projetada para propiciar melhor desempenho e segurança, além de reduzir o uso de disco com os seus recursos avançados de compressão. O desempenho de leitura e escrita também é mais alto porque ele oferece uma nova camada de rede cliente/servidor permitindo uma transferência de dados mais veloz.
No caso da deduplicação, o Oracle é capaz de reduzir a redundância de dados atráves de um algoritmo interno de forma que LOBS idênticos são fundido em uma única imagem. Em relação à compressão de dados, temos a opção de compressão média (MEDIUM) e alta (HIGH). Em relação à criptografia, existem vários algoritmos que são suportados como 3DES168, AES128 ente outros. No mais, a intenção deste artigo será o de demonstrar e comparar o efeito da compressão de dados em colunas do tipo BLOB e CLOB.
No Oracle 11g, quando criamos uma coluna do tipo LOB (BLOB/CLOB), por padrão o mesmo será criado no antigo formato (BASICFILE). Se quisermos que o padrão seja SECUREFILE, deveremos alterar o valor do parâmetro db_securefile para ALWAYS ou FORCE. Seu valor default é PERMITTED. Vale a pena salientar que esse parâmetro pode ser alterado tanto no nível de sistema quanto no nível de sessão.
SQL> show parameter db_securefile
NAME TYPE VALUE
--------------------- ----------- ---------
db_securefile string PERMITTED
Seguem abaixo os valores possíveis para o parâmetro db_securefile.
- ALWAYS - Criam todos os LOBS como SecureFile, mesmo se BasicFile for especificado. Se a tablespace de destino não foi criada com o ASSM (Automatic Segment Space Management) habilitado, então um erro será emitido caso a palavra chave SECUREFILE não foi especificada.
- FORCE - Criam todos os LOBS como SecureFile, mesmo se BasicFile for especificado. Um erro será gerado caso a tablespace não esteja com a ASSM habilitado.
- PERMITTED - É o valor default e permite a criação de SecureFiles LOBS.
- NEVER - Não será permitida a criação de SecureFile LOBs. Se a palavra chave SECUREFILE for usada, o LOB será criado como BasicFile. Um erro será emitido apenas se alguma opção (deduplicate, compress, encrypt) for utilizada.
- IGNORE - A palavra chave SECUREFILE e todas opções serão ignoradas. Portanto, os LOBS serão criados como BasicFiles.
Para fins de demonstração, irei criar 6 tabelas de forma a comparar o efeito da compressão de dados nos campos BLOB e CLOB. Segue abaixo o layout que utilizarei.
Nome da Tabela Tipo de Dado Tipo de LOB Compressão
----------------------- -------------- ----------- ----------
BLOB_BASIC BLOB BASICFILE NENHUMA
BLOB_SECUREFILE_MEDIUM BLOB SECUREFILE MÉDIA
BLOB_SECUREFILE_HIGH BLOB SECUREFILE ALTA
CLOB_BASIC CLOB BASICFILE NENHUMA
CLOB_SECUREFILE_MEDIUM CLOB SECUREFILE MÉDIA
CLOB_SECUREFILE_HIGH CLOB SECUREFILE ALTA
Abaixo irei criar as tabelas conforme o layout apresentado acima.
C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Sex Nov 2 12:57:53 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table blob_basic
2 (
3 id number(10),
4 nome varchar2(255),
5 documento blob
6 )
7 lob (documento) store as (tablespace tbs_lob)
8 tablespace tbs_data;
Tabela criada.
SQL> create table blob_securefile_medium
2 (
3 id number(10),
4 nome varchar2(255),
5 documento blob
6 )
7 lob (documento) store as securefile (tablespace tbs_lob compress medium)
8 tablespace tbs_data;
Tabela criada.
SQL> create table blob_securefile_high
2 (
3 id number(10),
4 nome varchar2(255),
5 documento blob
6 )
7 lob (documento) store as securefile (tablespace tbs_lob compress high)
8 tablespace tbs_data;
Tabela criada.
SQL> create table clob_basic
2 (
3 id number(10),
4 nome varchar2(255),
5 documento clob
6 )
7 lob (documento) store as (tablespace tbs_lob)
8 tablespace tbs_data;
Tabela criada.
SQL> create table clob_securefile_medium
2 (
3 id number(10),
4 nome varchar2(255),
5 documento clob
6 )
7 lob (documento) store as securefile (tablespace tbs_lob compress medium)
8 tablespace tbs_data;
Tabela criada.
SQL> create table clob_securefile_high
2 (
3 id number(10),
4 nome varchar2(255),
5 documento clob
6 )
7 lob (documento) store as securefile (tablespace tbs_lob compress high)
8 tablespace tbs_data;
Tabela criada.
SQL> select table_name,column_name,segment_name,compression,securefile from user_lobs;
TABLE_NAME COLUMN_NAME SEGMENT_NAME COMPRE SEC
----------------------- -------------- ------------------------- ------ ---
BLOB_BASIC DOCUMENTO SYS_LOB0000038106C00003$$ NONE NO
BLOB_SECUREFILE_MEDIUM DOCUMENTO SYS_LOB0000038111C00003$$ MEDIUM YES
BLOB_SECUREFILE_HIGH DOCUMENTO SYS_LOB0000038114C00003$$ HIGH YES
CLOB_BASIC DOCUMENTO SYS_LOB0000038117C00003$$ NONE NO
CLOB_SECUREFILE_MEDIUM DOCUMENTO SYS_LOB0000038123C00003$$ MEDIUM YES
CLOB_SECUREFILE_HIGH DOCUMENTO SYS_LOB0000038120C00003$$ HIGH YES
6 linhas selecionadas.
Com as tabelas criadas, irei popular as tabelas BLOB_BASIC e CLOB_BASIC de forma que as mesma fiquem com um tamanho de mais ou menos 8,6 GB. Apesar de eu não ter demonstrado a inserção dos dados nas tabelas, o conteúdo da coluna BLOB possui vários documentos binários diferentes (*.doc, *.xls, *.pdf). No caso da coluna CLOB, o seu conteúdo contém textos grandes com caracteres aleatórios. Sobre como carregar arquivos externos para tabelas do banco de dados, aconselho a leitura dos artigos de Dezembro/2009 e Janeiro/2010.
Bom, após o carregamento das tabelas BLOB_BASIC e CLOB_BASIC, irei carregar as demais tabelas utilizando o método abaixo:
insert into BLOB_SECUREFILE_MEDIUM select * from BLOB_BASIC;
insert into BLOB_SECUREFILE_HIGH select * from BLOB_BASIC;
insert into CLOB_SECUREFILE_MEDIUM select * from CLOB_BASIC;
insert into CLOB_SECUREFILE_HIGH select * from CLOB_BASIC;
Após a execução das operações DML acima, irei verificar o tamanho dos segmentos LOB utilizando uma consulta que eu criei e documentei conforme publicação do artigo de Maio/2011.
SQL> select table_name,
2 decode(partitioned,'/','NO',partitioned) partitioned,
3 num_rows,
4 data_mb,
5 indx_mb,
6 lob_mb,
7 total_mb
8 from (select data.table_name,
9 partitioning_type
10 || decode (subpartitioning_type,
11 'none', null,
12 '/' || subpartitioning_type)
13 partitioned,
14 num_rows,
15 nvl(data_mb,0) data_mb,
16 nvl(indx_mb,0) indx_mb,
17 nvl(lob_mb,0) lob_mb,
18 nvl(data_mb,0) + nvl(indx_mb,0) + nvl(lob_mb,0) total_mb
19 from ( select table_name,
20 nvl(min(num_rows),0) num_rows,
21 round(sum(data_mb),2) data_mb
22 from (select table_name, num_rows, data_mb
23 from (select a.table_name,
24 a.num_rows,
25 b.bytes/1024/1024 as data_mb
26 from user_tables a, user_segments b
27 where a.table_name = b.segment_name))
28 group by table_name) data,
29 ( select a.table_name,
30 round(sum(b.bytes/1024/1024),2) as indx_mb
31 from user_indexes a, user_segments b
32 where a.index_name = b.segment_name
33 group by a.table_name) indx,
34 ( select a.table_name,
35 round(sum(b.bytes/1024/1024),2) as lob_mb
36 from user_lobs a, user_segments b
37 where a.segment_name = b.segment_name
38 group by a.table_name) lob,
39 user_part_tables part
40 where data.table_name = indx.table_name(+)
41 and data.table_name = lob.table_name(+)
42 and data.table_name = part.table_name(+))
43 order by table_name;
TABLE_NAME PARTITIONED NUM_ROWS DATA_MB INDX_MB LOB_MB TOTAL_MB
----------------------- ----------- -------- ------- ------- ------ --------
BLOB_BASIC NO 15000 3,5 7 8841 8851,50
BLOB_SECUREFILE_MEDIUM NO 15000 4,5 ,06 5247 5251,56
BLOB_SECUREFILE_HIGH NO 15000 4,44 ,06 5183 5187,50
CLOB_BASIC NO 15000 1,69 27 8841 8869,69
CLOB_SECUREFILE_MEDIUM NO 15000 3,69 ,06 323 326,75
CLOB_SECUREFILE_HIGH NO 15000 3,56 ,06 290 293,62
6 linhas selecionadas.
O resultado acima nos mostra a comparação entre os níveis de compressão dos dados das colunas BLOB e CLOB. Podemos perceber que os níveis de compressão MÉDIA e ALTA são bem próximos. Neste caso, acredito que a compressão MÉDIA é suficiente, além de gerar menos overhead para o sistema. Podemos perceber também que a compressão da coluna CLOB foi muito maior em comparação com a compressão da coluna BLOB.
Um outro teste que realizei foi o de setar a opção DEDUPLICATE em conjunto com a opção COMPRESSION. Segue abaixo o resultado da comparação.
TABLE_NAME PARTITIONED NUM_ROWS DATA_MB INDX_MB LOB_MB TOTAL_MB
----------------------- ----------- -------- ------- ------- ------ --------
BLOB_BASIC NO 15000 3,5 7 8841 8851,50
BLOB_SECUREFILE_MEDIUM NO 15000 4,5 ,06 2880 2884,56
BLOB_SECUREFILE_HIGH NO 15000 4,44 ,06 2808 2812,50
CLOB_BASIC NO 15000 1,69 27 8841 8869,69
CLOB_SECUREFILE_MEDIUM NO 15000 3,69 ,06 320 323,75
CLOB_SECUREFILE_HIGH NO 15000 3,56 ,06 288 291,62
6 linhas selecionadas.
Podemos verificar que, diferentemente da coluna CLOB, na coluna BLOB o nível de compressão teve um aumento bem significativo com o uso das opções COMPRESS+DEDUPLICATE. Para resumir, abaixo está um gráfico demonstrando um comparativo dos níveis de compressão das colunas LOB.
Para finalizar, abaixo eu demonstro que é possível exportar uma tabela que possui uma coluna LOB criada como SECUREFILE no Oracle 11g para um schema de banco de dados no Oracle 10g através da cláusula "version" do utilitário de exportação Export Data Pump.
C:\>expdp system/manager schemas=scott dumpfile=tabelas_secure_version10g version=10.2
Export: Release 11.2.0.3.0 - Production on Sex Nov 2 13:44:52 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********
schemas=scott dumpfile=tabelas_secure_version10g version=10.2
Estimativa em andamento com o método BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o método de BLOCKS: 0 KB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
. . exportou "SCOTT"."BLOB_SECUREFILE_MEDIUM" 0 KB 0 linhas
. . exportou "SCOTT"."CLOB_SECUREFILE_MEDIUM" 0 KB 0 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 é:
E:\ORACLE\ADMIN\BD02\DPDUMP\TABELAS_SECURE_VERSION10G.DMP
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluído com sucesso em 13:46:01
C:\>impdp system/manager schemas=scott dumpfile=tabelas_secure_version10g
Import: Release 10.2.0.5.0 - Production on Sex 2 Nov 2012 13:51:03
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Tabela-mestre "SYSTEM"."SYS_IMPORT_SCHEMA_01" carregada/descarregada com sucesso
Iniciando "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********
schemas=scott dumpfile=tabelas_secure_version10g
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
. . importou "SCOTT"."BLOB_SECUREFILE_MEDIUM" 0 KB 0 linhas
. . importou "SCOTT"."CLOB_SECUREFILE_MEDIUM" 0 KB 0 linhas
O job "SYSTEM"."SYS_IMPORT_SCHEMA_01" foi concluído com sucesso em 13:51:09