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


segunda-feira, 3 de dezembro de 2012

Migrando para o SecureFile LOBS no Oracle 11g com o DBMS_REDEFINITION

Por Eduardo Legatti

Olá,

No artigo de Novembro/2012 eu demonstrei um pouco sobre o Oracle SecureFile LOBS no Oracle 11g em relação à compressão de dados e deduplicação. Neste artigo irei demonstrar, de forma prática, uma das formas de como poderemos migrar uma coluna LOB, de BASICFILE, para SECUREFILE. Para isso, farei uso de um recurso que já foi  demonstrado no artigo de Abril/2011 que é a redefinição online de uma tabela (Online table redefintion) usando o pacote DBMS_REDEFINITION. Bom, até o momento, não existe uma forma automática ou um comando (ALTER TABLE ...) que altere uma coluna LOB (CLOB/BLOB) de BASICFILE para SECUREFILE. Apesar de podermos utilizar os métodos CTAS, Export/Import, ou até mesmo utilizar um comando UPDATE para atualizar os dados de uma coluna (BASICFILE) para uma nova coluna (SECUREFILE) na mesma tabela, na minha opinião, um ótimo método é fazer uso da redefinição online, pois o mesmo não ocasiona downtime para a aplicação que acessa o banco de dados, além de ser muito prático.

Abaixo, irei demonstrar na prática como poderemos migrar uma tabela que possui uma coluna LOB padrão BASICFILE, para uma coluna LOB SECUREFILE, já com a deduplicação e compressão média ativadas. A tabela original será a mesma utilizada no artigo de Novembro/2012, a diferença que o seu nome será BLOB_TABLE e não mais BLOB_BASIC.

Seguindo o roteiro já demonstrado no artigo de Abril/2011, irei iniciar o procedimento criando uma tabela interina (INTERIM) que será o objeto alvo da redefinição online. Note que a tabela será praticamente idêntica à tabela original, diferenciando-se apenas pela coluna BLOB, que será do tipo SECUREFILE com a deduplicação e compressão média ativadas. Em comparação com o artigo de Abril/2011, naquele artigo eu não tinha definido nenhuma constraint NOT NULL nas colunas da tabela INTERIM, afim de evitar qualquer tipo de problema nos procedimentos de redefinição online da tabela.

Para fins de didática, neste artigo irei definir as colunas como NOT NULL de forma a espelhar a tabela original. A diferença agora é que irei setar o parâmetro "ignore_errors" da procedure COPY_TABLE_DEPENDENTS como TRUE. Esta procedure pertence à package DBMS_REDEFINITION. Vale a pena salientar que o segmento LOB da tabela original possui cerca de 8,63 GB de tamanho e que, ao final da redefinição online, o mesmo terá cerca de 2,81 GB.
C:\ sqlplus system/manager

SQL*Plus: Release 11.2.0.3.0 Production on Seg Dez 3 19: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 scott.blob_table_interim
  2  (
  3    codigo    number(10) not null,
  4    nome      varchar2(255),
  5    documento blob
  6  )
  7  lob (documento) store as securefile
  8         (
  9           tablespace tbs_lob
 10           deduplicate
 11           compress medium
 12         )
 13  tablespace tbs_data;

Tabela criada.
Após a criação da tabela BLOB_TABLE_INTERIM acima, irei iniciar abaixo, o processo de redefinição online.

SQL> begin
  2  dbms_redefinition.start_redef_table
  3  (
  4     uname           => 'SCOTT'
  5   , orig_table      => 'BLOB_TABLE'
  6   , int_table       => 'BLOB_TABLE_INTERIM'
  7   , col_mapping     => null
  8   , options_flag    => dbms_redefinition.cons_use_pk
  9   , orderby_cols    => null
 10   , part_name       => null
 11  );
 12  end;
 13  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:21:09.42

Após a execução do processo acima, irei iniciar abaixo o processo que irá realizar a cópia dos objetos dependentes da tabela original para a tabela interina. Esse objetos incluem, por exemplo, contraints, triggers, valores default, etc...

SQL> var num_errors number
SQL> begin
  2  dbms_redefinition.copy_table_dependents
  3  (
  4      uname            => 'SCOTT'
  5    , orig_table       => 'BLOB_TABLE'
  6    , int_table        => 'BLOB_TABLE_INTERIM'
  7    , copy_indexes     => dbms_redefinition.cons_orig_params
  8    , copy_triggers    => true
  9    , copy_constraints => true
 10    , copy_privileges  => true
 11    , ignore_errors    => true
 12    , num_errors       => :num_errors
 13    , copy_statistics  => true
 14  );
 15  end;
 16  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:00:23.46

Após a execução do procedimento de cópia dos objetos dependentes, irei verificar o resultado da variável num_errors. Se a mesma tiver um valor diferente de zero, então é prudente verificar na view DBA_REDEFINITION_ERRORS, qual ação não foi realizada com sucesso.

SQL> print num_errors

NUM_ERRORS
----------
         1

SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors;

OBJECT_NAME   BASE_TABLE_NAME     DDL_TXT
------------- ------------------- --------------------------------------------------
SYS_C003910   BLOB_TABLE          ALTER TABLE "SCOTT"."BLOB_TABLE_INTERIM" 
                                  MODIFY ("CODIGO" CONSTRAINT "TMP$$_SYS_C0039100"
                                  NOT NULL ENABLE NOVALIDATE)
1 linha selecionada.

Podemos verificar pelo resultado acima que o processo de cópia de objetos dependentes tentou modificar a coluna CODIGO da tabela interina para NOT NULL. Neste caso, poderemos ignorar esse erro. Abaixo está a instrução SQL na qual podemos verificar todos os objetos que foram redefinidos.

SQL> select object_type, object_name, base_table_name, interim_object_name
  2  from dba_redefinition_objects
  3  where base_table_name = 'BLOB_TABLE' order by 1,2,3;

OBJECT_TYPE  OBJECT_NAME         BASE_TABLE_NAME     INTERIM_OBJECT_NAME
------------ ------------------- ------------------- --------------------------
TABLE        BLOB_TABLE          BLOB_TABLE          BLOB_TABLE_INTERIM
CONSTRAINT   PK_BLOB_TABLE       BLOB_TABLE          TMP$$_PK_BLOB_TABLE0
INDEX        PK_BLOB_TABLE       BLOB_TABLE          TMP$$_PK_BLOB_TABLE0
INDEX        IDX_BLOB_TABLE_NOME BLOB_TABLE          TMP$$_IDX_BLOB_TABLE_NOME0

4 linhas selecionadas.

Bom, caso alguma linha da tabela original tenha sofrido algum tipo de alteração DML durante o processo de redefinição online, é prudente sincronizar a tabela interina antes de finalizar o processo de redefinição. Esta fase é importante para não sobrecarregar a fase de finalização "finish_redef_table".

SQL> begin
  2  dbms_redefinition.sync_interim_table
  3  (
  4      uname      => 'SCOTT'
  5    , orig_table => 'BLOB_TABLE'
  6    , int_table  => 'BLOB_TABLE_INTERIM'
  7    , part_name  => null
  8  );
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Pronto. Agora poderemos finalizar o processo de redefinição online.

SQL> begin
  2  dbms_redefinition.finish_redef_table
  3  (
  4      uname      => 'SCOTT'
  5    , orig_table => 'BLOB_TABLE'
  6    , int_table  => 'BLOB_TABLE_INTERIM'
  7    , part_name  => null
  8  );
  9  end;
 10  /

Procedimento PL/SQL concluído com sucesso.

Vale a pena salientar que caso ocorra algum tipo de problema durante o processo de redefinição online ou apenas queiramos abortar o processo por algum motivo, poderemos utilizar a procedure "abort_redef_table" como demonstrado abaixo:

Caso queira abortar o processo
==============================

SQL> begin
  2  dbms_redefinition.abort_redef_table
  3  (
  4      uname=>'SCOTT'
  5    , orig_table=>'BLOB_TABLE'
  6    , int_table=>'BLOB_TABLE_INTERIM'
  7  );
  8  end;
  9  /

Procedimento PL/SQL concluído com sucesso.

Bom, após a execução de todas as operações acima, irei verificar o tamanho dos segmentos LOB do schema SCOTT 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_TABLE          NO             15000      4,5      ,06     2880   2884,56
BLOB_TABLE_INTERIM  NO             15000      3,5        7     8841   8851,50

2 linhas selecionadas.

Podemos verificar que após o processo de redefinição online, a tabela original BLOB_TABLE foi renomeada para BLOB_TABLE_INTERIM e vice versa. Portanto, não precisaremos mais da tabela interina e poderemos dropá-la com segurança. Perceba a diferença de tamanho do segmento LOB após a redefinição online.

SQL> drop table blob_table_interim purge;

Tabela eliminada.

Por fim, mostrarei abaixo um teste em relação ao comando DML (INSERT), de forma a demonstrar a diferença de performance entre SECUREFILE e BASICFILE em uma coluna CLOB.

Oracle 10g
==========

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

5 linhas selecionadas.

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> declare
  2    l_clob clob := rpad('x', 10000, 'x');
  3  begin
  4    for i in 1 .. 100000 loop
  5      insert into clob_basic values (i,'teste',l_clob);
  6    end loop;
  7    commit;
  8  end;
  9  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:10:46.94

Podemos verificar acima que, no Oracle 10g, o processo de inserção de 100.000 linhas em uma tabela com uma coluna CLOB (BASICFILE) demorou cerca de 10:46 minutos.

Oracle 11g
==========

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

5 linhas selecionadas.

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> declare
  2    l_clob clob := rpad('x', 10000, 'x');
  3  begin
  4    for i in 1 .. 100000 loop
  5      insert into clob_basic values (i,'teste',l_clob);
  6    end loop;
  7    commit;
  8  end;
  9  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:01:19.60

Podemos verificar acima que, no Oracle 11g, o processo de inserção de 100.000 linhas em uma tabela com uma coluna CLOB (BASICFILE) demorou cerca de 01:19 minutos.

SQL> create table clob_securefile
  2  (
  3    id         number(10),
  4    nome       varchar2(255),
  5    documento  clob
  6  )
  7  lob (documento) store as securefile (tablespace tbs_lob)
  8  tablespace tbs_data;

Tabela criada.

SQL> declare
  2    l_clob clob := rpad('x', 10000, 'x');
  3  begin
  4    for i in 1 .. 100000 loop
  5      insert into clob_securefile values (i,'teste',l_clob);
  6    end loop;
  7    commit;
  8  end;
  9  /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:01:56.56

Podemos verificar acima que, no Oracle 11g, o processo de inserção de 100.000 linhas em uma tabela com uma coluna CLOB (SECUREFILE) demorou cerca de 01:56 minutos.

Portanto, de acordo com os resultados demonstrados pelos testes acima, podemos comprovar que realmente houve uma melhora considerável de performance (pelo menos no INSERT), comparando o Oracle 10g (BASICFILE) e o Oracle 11g (SECUREFILE). No entanto, podemos verificar que o BASICFILE no Oracle 11g não foi apenas superior ao BASICFILE do Oracle 10g, como também foi superior ao próprio SECUREFILE. Neste caso, concluo que o SECUREFILE é vantajoso quando utilizamos outras opções, sejam elas compressão, deduplicação ou criptografia.

Google+

2 comentários:

Victor disse...

Show de artigo Legatti

COmo eu tinha te falado, eu precisei usar o BASICFILE um tempo atrás... até pq na criação do SECUREFILE os dados (XML) ficaram "endentados" com quebra de linha...

É possível como migrar de SECUREFILE para BASICFILE?

Abs véio
Vitão

Eduardo Legatti disse...

Olá Vitão,

Não realizei nenhum teste, mas em teoria é possível sim. É só fazer a operação inversa. Imagine o cenário na qual eu tenha uma tabela T1 com a coluna A (Securefile) e crio uma coluna nova B (Basicfile).

A operação UPDATE T1 SET B=A; teria que funcionar. Em teoria, seriam migrados apenas os dados, e não estruturas internas do LOB.

Abraços e até mais ...

Legatti

Postagens populares