Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


sexta-feira, 2 de novembro de 2012

Um pouco sobre SecureFile LOBS no Oracle 11g

Por Eduardo Legatti


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



13 comentários:

Fábio Prado disse...

Eduardo, muito bom o artigo. Vc chegou a fazer também algum teste de performance comparando consultas/atualizações?

[]s
Fábio Prado
www.fabioprado.net

Eduardo Legatti disse...

Olá Fábio,

Não. No entanto,
eu fiz várias pesquisas na NET sobre a performance de leitura/gravação, e achei muitos artigos de pessoas que mostraram, na prática, uma melhora significativa na performance.

Abraços e até mais ...

Legatti

Unknown disse...

Fala Legatti..
Velho, que artigo chapado cara... afff. eu nem sabia dessa parada de compressão.. Aprendi muito cara... show de bola.. o mais curioso é: Eu precisei a 15 dias atrás trabalhar com compressão em tableas com LOB (Porém era XMLType) e não observei essa compressão. A diferença é que na situação em questão o comportamento (armazenamento) de BASICFILE para SECUREFILE diferenciava na "edentação/quebra de linha" do código XML, consequentemente "zuou" a aplicação então precisei REMOVER o SECUREFILE, (na verdade criar o campo como BASICFILE) para que o código XML voltasse ao normal. Cara era uma tabela de 1 bilhão de linhas, gigante (2TB). Eu imagino que essa compressão se aplique para o XML também... vc testou isso tb? Cara, show de bola o artigo parabéns mano... Abraço. Vitão

Eduardo Legatti disse...

Olá Vitão,

Tudo bem? Cara, essa compressão realmente é monstro!! Estou em uma fase de homologação de uma base de dados aqui (alguns GB) que, se for pra frente, vai economizar muito espaço em disco. No mais, estranho esse comportamento no XML, hein? Nunca vivenciei esse tipo de situação. 2TB de dados é muita coisa mesmo!!

Estava olhando na documentação do 11g R2 e verifiquei que o Securefile é a bola da vez para XML também. Portanto, no caso da compressão, acredito que se aplique também.

Citação 1:
"SecureFiles, a feature included in Oracle Database 11g, offers a 'best-of-both-worlds' architecture for storing unstructured content, such as documents, spreadsheets and XML files. With SecureFiles Compression, typical files such as documents or XML files, experience a reduction of 2x to 3x times in size."

Citação 2:
"XMLType data that uses the binary XML storage model is stored internally using large objects (LOBs). Prior to Oracle Database 11g Release 2 (11.2.0.2), binary XML data was stored by default using the BasicFile LOB storage option. By default, LOB storage for binary XML data now uses the SecureFile LOB storage option whenever possible."

No mais, obrigado pela visita e pelo comentário.

Abraços

Legatti

Eduardo Legatti disse...

Para quem estiver com problemas ao realizar operações (leitura) em colunas com SecureFile Lobs com a compressão ativada, a Oracle publicou um bug relacionado ao erro "kdliSyncRead";

ORA-00600: código de erro interno, argumentos: [kdliSyncRead], [25053], [26507843], [26507876], [0x084875F40], [0x06A600000], [], [], [], [], [], []

Para maiores informações, acesse o link [ID 1355668.1] no My Oracle Support (Metalink).

Abraços

Legatti

Unknown disse...

Galera, tenho uma dúvida e se puderem me ajudar eu agradeço.
Estou criando um campo clob em meu banco(oracle 11g) direto via PLSql e o mesmo está ficando com o tamanho restrito a 4000 caracteres. Sabe me dizer o que posso fazer para que aceite o tamanho máximo permitido pelo campo do tipo LOB ?
Obrigado.

Eduardo Legatti disse...

Olá Anônimo,

O tipo de dados CLOB armazena até 4 GB. Com certeza o seu código PL/SQL está usando alguma variável que esteja limitando o tamanho.

Abraços

Legatti

Unknown disse...

Boa Tarde Eduardo.

Obrigado pela atenção.
Não estou criando o campo via linha de comando, na ferramenta eu vou na tabela que quero e com o botão direito acesso a opção de editar, dou um nome para a coluna e escolho o tipo, mais nada. Não tenho nem a opção de escolher o tamanho quando o tipo é CLOB ou BLOB. Quando vou nas propriedades ele está com a opção DATA_LENGTH = 4000 e não me deixa inserir mais de 4000 caracteres.

Atc, Ricardo Xavier

Eduardo Legatti disse...

Olá Ricardo,

Agora eu entendi do que você está falando. A coluna DATA_LENGTH vem das views de dicionário de dados DBA/ALL/USER_TABLES e indica o tamanho máximo da coluna. No caso das colunas do tipo de LOB (BLOB, CLOB), por padrão, essa informação será 4000 o que indica que a coluna armazenará no máximo 4000 bytes na própria linha da tabela. O excedente será criado em um segmento próprio do tipo LOB na tablespace. No exemplo abaixo, eu criei uma coluna CLOB e armazenei 4050 bytes sem problemas.

SQL> select column_name,data_type,data_length
2 from user_tab_columns where table_name='T1';

COLUMN_NAME --> TEXTO
DATA_TYPE --> CLOB
DATA_LENGTH --> 4000

SQL> select length(TEXTO) from T1;

LENGTH(LOB)
-----------
4050

Abraços

Legatti

Unknown disse...

Olá Eduardo.
Entendi a colocação. No entanto ficou uma dúvida, na hora do insert na tabela eu não consigo passar uma string com mais de 4000 caracteres, me parece que isto é uma restrição do Oracle com relação ao tamanho de uma string. Para fazer o que preciso foi necessário criar uma Procedure que faça isto para mim onde criei um varchar2 (32767) onde passo o meu texto para uma variável e depois insiro no banco.

CREATE OR REPLACE PROCEDURE SP_ATUDESATV(PESTPOS IN NUMBER,
PPOSTRA IN VARCHAR2,
PDESATV IN VARCHAR2,
PNUMCTR IN NUMBER,
vRetorno OUT varchar2) IS
aDesAtv varchar2(32767) := PDESATV;
begin
UPDATE VETORHT.USU_T017POS
SET USU_DESATV = aDesAtv
WHERE USU_ESTPOS = PESTPOS
AND USU_POSTRA = PPOSTRA
AND USU_NUMCTR = PNUMCTR;
end;

Aí consegui passar dos 4k.

SQL> select length(usu_desatv)
2 from usu_t017pos
3 where usu_numctr = 2
4 and usu_postra = '000002002002'
5 and usu_estpos = 1;
LENGTH(USU_DESATV)
------------------
5463

Atc,

Eduardo Legatti disse...

Olá Ricardo,


Na linguagem PL/SQL o tamanho do tipo VARCHAR pode ser até 32K.

Uma das novidade do Oracle 12c foi em relação aos tipos de dados VARCHAR2, NVARCHAR2 e RAW que tiveram seus tamanhos máximos alterados de 4000 bytes para 32767 bytes.

Abraços

Legatti

Leonardo A. Souza disse...

Boa tarde Eduardo. Acompanho de vez em quando suas publicações. Está de parabéns.

Para usar o SECURE FILE com COMPRESSION (MEDIUM/HIGH) é preciso ter o ADVANCED COMPRESSION?

Leonardo Souza

Eduardo Legatti disse...

Olá Leonardo,

Tanto a deduplicação quanto a compressão utilizam o Oracle Advanced Compression.

Para maiores detalhes você pode acessar o link abaixo:

http://www.oracle.com/technetwork/database/options/compression/faq-092157.html#What_are_SecureFiles_What_is_the

Abraços e até mais...

Legatti

Postagens populares