Um pouco sobre SecureFile LOBS no Oracle 11g

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