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.
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.
2 comentários:
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
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
Postar um comentário