30 de jul de 2013

Olá pessoal!

No artigo de hoje falarei sobre um assunto que entendo como uma extensão do artigo Recriação de índices - Rebuild Index que escrevi tempos atrás.

Bom, o assunto é como efetuar uma reorganização dos dados de uma tabela em um banco de dados Oracle. Uma tabela pode se apresentar fragmentada, ou seja, com espaços não utilizados entre os espaços usados pela tabela.

Li um artigo bastante interessante, onde o autor, Mohammad, afirma que existem quatro formas de se reorganizar tabelas fragmentadas:
1) alter table ... move + rebuild indexes
2) export / truncate / import
3) create table as select
4) dbms_redefinition

No post de hoje vamos falar sobre a primeira alternativa.

Para isso vamos criar uma tabela de teste, inserir dados e manipula-los com o objetivo de criarmos lacunas de espaços na tabela. depois vamos realizar uma reorganização dos dados do objeto visando o ganho de espaço, um melhor plano de execução das consultas contra a tabela, além de uma organização melhor dos dados.

Primeiro vamos criar a tabela conforme script abaixo:

CREATE TABLE tbl_reorg
(
   codigo      NUMBER (8) NOT NULL,
   descricao   VARCHAR2 (100) NOT NULL
);

Agora vamos popular a tabela:

BEGIN
   FOR i IN REVERSE 1 .. 1000000
   LOOP
      INSERT INTO tbl_reorg (codigo, descricao) VALUES (i, 'registro da posição: ' || i);
   END LOOP;
   COMMIT;
END;


Verificando o tamanho da tabela após a inserção dos dados com o script abaixo. A projeção dos dados pode ser vista na figura 1.
SELECT SEGMENT_NAME, ROUND (SUM (bytes) / 1024 / 1024, 2) size_mb
    FROM dba_segments
   WHERE segment_name = 'TBL_REORG'
GROUP BY SEGMENT_NAME;

Figura 1: Tamanho da tabela TBL_REORG. (Fonte: autoria própria)

Vamos coletar as estatísticas da tabela pois como pode ser observado pela consulta abaixo, não existem dados estatísticos sobre o objeto.

SELECT owner,
       table_name,
       blocks,
       empty_blocks,
       num_rows,
       TO_CHAR (last_analyzed, 'DD-MM-RRRR HH24:MI:SS') AS "ANALYZE"
  FROM dba_tables
 WHERE table_name = 'TBL_REORG';

O script abaixo coleta as estatísticas da tabela.
exec dbms_stats.gather_table_stats (ownname=>'HOUSEWORK',tabname=>'TBL_REORG',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>6);

Vale lembrar que existem outras formas de coleta de estatísticas. Falei um pouco sobre isso no artigo Estatísticas no banco de dados Oracle.

Agora vamos apagar alguns dados para gerar os espaços em branco na tabela.
delete from tbl_reorg where codigo between 10000 and 20000;
delete from tbl_reorg where codigo between 50000 and 200000;
delete from tbl_reorg where codigo between 400000 and 700000;
delete from tbl_reorg where codigo > 900000;
commit;

Vamos verificar o tamanho da tabela após algumas exclusões. O resultado pode ser visto na figura 2.
SELECT SEGMENT_NAME, ROUND (SUM (bytes) / 1024 / 1024, 2) size_mb
    FROM dba_segments
   WHERE segment_name = 'TBL_REORG'
GROUP BY SEGMENT_NAME;

Figura 2: Tamanho da tabela TBL_REORG após exclusão de alguns registros. (Fonte: autoria própria)
Bom...

Para resolvermos esse problema de fragmentação, basta reconstruir o mapa binário da tabela, para isso vamos usar o comando MOVE.

Não vamos mover a tabela para outra tablespace, vamos move-la na própria tablespace com o script abaixo:
ALTER TABLE tbl_reorg MOVE;

Verificando novamente o tamanho da tabela,  se tem o resultado exibido na figura 3.

Figura 3: Tamanho da tabela TBL_REORG após exclusão de dados e MOVE. (Fonte: autoria própria)
Após a execução do MOVE, a tabela foi reorganizada e o tamanho da tabela caiu de 42MB para 19MB, fazendo apenas uma reconstrução dos extents da tabela.

É interessante também realizar uma nova coleta das estatísticas da tabela após o MOVE, para que seja "traçado" um plano de execução mais otimizado de consultas contra o objeto.

exec dbms_stats.gather_table_stats (ownname=>'HOUSEWORK',tabname=>'TBL_REORG',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>6);

Pois é pessoal...

Esse foi um dos métodos para resolver o problema da fragmentação de uma tabela no banco de dados, vale lembrar que essa situação pode causar perda de performance em um sistema de banco de dados então vale a pena ter um cuidado especial sobre o assunto.

Bom, é isso aí!

Até a próxima!

Referências:

Reis, Bruno - How to do reorg in a table in Oracle Database / Como fazer reorg em uma tabela no Banco de Dados Oracle. Disponível em: http://brunors.com/how-to-do-reorg-in-a-table-in-oracle-database-como-fazer-reorg-em-uma-tabela-no-banco-de-dados-oracle/. Acessado: 29/07/2013.

Almeida, Rodrigo - Entendendo a Marca d’água e fragmentação de tabelas. Disponível em : http://profissionaloracle.com.br/blogs/rodrigoalmeida/2008/10/07/entendendo-a-marca-dagua-e-fragmentacao-de-tabelas/. Acessado: 29/07/2013.

Taj, Mohammad - Table Fragmentation. Disponível em: http://dbataj.blogspot.com.br/2007/07/table-fragmentation.html. Acessado: 29/07/2013.

Raphael Fernandes, terça-feira, julho 30, 2013

2 comentários

22 de jul de 2013

Olá pessoal,

Enquanto termino de escrever um material para postar, seguem algumas tirinhas do site Vida de Programador.

Essas são sobre banco de dados, mas tem várias outras bastante interessante. Acessem lá e confiram!

Segurança da informação é tudo! (Fonte: Vida de Programador)


SID do BD? Quem é esse? (Fonte: Vida de Programador)


Otimizando o banco de dados. (Fonte: Vida de Programador)


Fonte:

Vida de programador /*Linhas de código da vida de um programador*/ - Acesso à base de dados. Disponível em: http://vidadeprogramador.com.br/2013/05/02/acesso-a-base-de-dados/. Acessado em: 21/07/2013.

Vida de programador /*Linhas de código da vida de um programador*/ - SID do Banco. Disponível em: http://vidadeprogramador.com.br/2012/07/01/sid-do-banco/. Acessado em: 21/07/2013.

Vida de programador /*Linhas de código da vida de um programador*/ - Otimização do BD. Disponível em: http://vidadeprogramador.com.br/2012/08/03/otimizacao-do-bd/. Acessado em: 21/07/2013.

Raphael Fernandes, segunda-feira, julho 22, 2013

Sem comentários

3 de jul de 2013

Olá pessoal.

Hoje vou falar sobre algo que pode ser bem interessante para administradores de banco de dados.

Muitas vezes nós DBA's recebemos solicitações para geração de relatórios em ambiente de produção (pelo menos onde trabalho isso é muito comum).

A questão é que sempre utilizamos o SQL*Plus para solicitações dos analistas e, na maioria das vezes, é desejado que a projeção da informação seja salva em planilha.

Para resolver essa situação, podemos utilizar um tipo de saída do SQL*Plus que torna o layout do arquivo gerado mais "apresentável". Na verdade, a projeção será gerada dentro de um código HTML, mas podemos gerá-lo como um XLS por se tratar de uma estrutura em tabelas através do markup html.

O comando, em uma forma bem básica, é o seguinte:

set feed off markup html on
spool c:\temp\relatorio.xls
SELECT * FROM hr.employees;
spool off
set markup html off

O arquivo gerado pode ser aberto no MS-Excel pois o spool foi para um ".xls".

Para se ter uma melhora na apresentação do arquivo, pode-se editá-lo retirando a consulta executada (que deve aparecer no início do arquivo) e o comando "spool off" (que deve aparecer no final do arquivo).

Bom pessoal, fica a dica!

Até a próxima.

Referências:

Oracle – Generating HTML Reports from SQL*Plus. Disponível em: http://docs.oracle.com/cd/B13789_01/server.101/b12170/ch8.htm. Acessado: 102/07/2013.

Raphael Fernandes, quarta-feira, julho 03, 2013

Sem comentários