No post de hoje vou mostrar como eliminar "chained rows" ou linhas encadeadas e migradas (LEMs) em uma tabela, visando otimizar a performance de SQLs que executam queries nela, usando como exemplo, um caso real em que atuei faz alguns anos. Se você é novo no mundo Oracle ou tem pouca experiência com tuning, e não sabe muito bem o que são chained rows ou LEMs, sugiro a leitura prévia do artigo Desfragmentando tabelas no Oracle.
O caso citado em que atuei consistia analisar uma query que um DEV reclamou de performance em um banco de dados produção. A query consultava a tabela TAB_TESTE (nome fictício) e ao consultá-la na view DBA_TABLES, após executar o comando ANALYZE TABLE TAB_TESTE COMPUTE STATISTICS, obtive as seguintes estatísticas:
- Qtde. de linhas (NUM_ROWS): 768.545
- Qtde. de blocos (BLOCKS) 22.592
- Qtde. de bloco vazios (EMPTY_BLOCKS): 192
- Qtde. de bloco vazios (EMPTY_BLOCKS): 192
- Qtde. de LEMs (CHAIN_CNT): 470.219
- Qtde. média de linhas por bloco (AVG_ROW_LEN) = 194
ATENÇÃO: É importante ressaltar que o comando ANALYZE TABLE foi executado apenas para obter os valores de EMPTY_BLOCKS e CHAIN_CNT. Não execute-o com o objetivo de atualizar estatísticas de objetos visando otimizar SQLs. Para saber mais sobre este assunto sugiro a leitura do artigo Coletando estatísticas para o otimizador de queries do Oracle.
Conforme as estatísticas apresentadas acima, podemos verificar que existem 192 blocos vazios e 470.219 LEMs na tabela. A quantidade de LEMs é bem grande e isso impacta negativamente na performance não apenas de queries na tabela, mas também na execução de exports e backups, então é necessário investigar o porquê das LEMs terem surgido, tentar evitá-las, e criar alguma rotina de manutenção na tabela com o objetivo de eliminá-las. Em geral, o mínimo que um DBA deve fazer é criar uma rotina de manutenção para eliminá-las. Eu já fiz isso diversas vezes (sempre que necessário), e vou mostrar ao longo deste artigo como executo este procedimento.
Entre as diversas formas de eliminar LEMs, citarei abaixo as principais:
1- Usando a package DBMS_REDEFINITION;
2- Executando CTAS+ rename table;
3- Executando ALTER TABLE MOVE (dentro do mesmo tablespace) ou movendo dados da tabela para outro tablespace e retornando-os dados em seguida para o tablespace original;
4- Fazendo export, TRUNCATE, import;
5- Reinserindo as LEMs usando ANALYZE TABLE CHAINED ROWS.
A última opção (item 5) é a que irei explicar a seguir, e que sempre executo, por ser o procedimento mais rápido de executar, mais fácil de ser automatizado, e que gera o menor impacto no ambiente, ocasionando indisponibilidade temporária mínima apenas nas LEMs (e não na tabela inteira).
O procedimento consiste nos seguintes passos:
1) Criar a tabela CHAINED_ROWS:
Para iniciar o procedimento é necessário criar a tabela
CHAINED_ROWS, executando o script oficial fornecido pela Oracle, chamado utlchain.sql. Esse script, que está incluso em qualquer instalação do banco de dados, cria a tabela CHAINED_ROWS com a estrutura exata necessária para armazenar as LEMs. Você deve executar este script uma única vez no banco de dados.2) Executar ANALYZE TABLE CHAINED_ROWS para identificar LEMs:
Agora você deve executar o comando abaixo para que ele identifique as LEMs dentro da tabela origem e grave as referências dessas linhas na tabela CHAINED_ROWS, criada no passo anterior:
SQL> ANALYZE TABLE TAB_TESTE LIST CHAINED ROWS INTO CHAINED_ROWS;
3) Salvar LEMs em uma tabela clone temporária:
Neste passo você vai criar uma tabela auxiliar clone da tabela TAB_TESTE, chamada AUX_CHAINED, que deverá conter somente as LEMs que foram gravadas na tabela CHAINED_ROWS, no passo anterior:
SQL> create table AUX_CHAINED as
select * from TAB_TESTE
where rowid in (select head_rowid
where rowid in (select head_rowid
from CHAINED_ROWS
where table_name = 'TAB_TESTE');
where table_name = 'TAB_TESTE');
4) Deletar as LEMs na tabela original:
Após gravarmos no passo anterior os dados das LEMs (que inclui o rowid delas) na tabela auxiliar clone, podemos apagar as linhas correspondentes na tabela original:
SQL> delete from TAB_TESTE
where rowid in (select head_rowid
where rowid in (select head_rowid
from CHAINED_ROWS
where table_name = 'TAB_TESTE');
where table_name = 'TAB_TESTE');
5) Reinserir as LEMs na tabela original:
Chegamos ao passo final, que consiste em reinserir as LEMs na tabela original a partir das linhas que foram gravadas na tabela auxiliar clone. Para fazer isso execute o SQL abaixo:
SQL> insert into TAB_TESTE
select * from AUX_CHAINED;
commit;
Pronto, neste momento você eliminou todas ou grande parte da LEMs na tabela TAB_TESTE. Para verificar isso eu executei novamente o comando ANALYZE TABLE TAB_TESTE COMPUTE STATISTICS e o obtive em seguida as seguintes estatísticas:
- Qtde. de linhas (NUM_ROWS): 768.545 - Qtde. de blocos (BLOCKS): 21.363
- Qtde. de bloco vazios (EMPTY_BLOCKS): 189
- Qtde. de bloco vazios (EMPTY_BLOCKS): 189
- Qtde. de LEMs (CHAIN_CNT): 0
- Qtde. média de linhas por bloco (AVG_ROW_LEN) = 191
Observação:
Após finalizar o procedimento de eliminação de LEMs apresentado acima, sugiro executar os comandos abaixo para diminuir o tamanho da tabela e coletar estatísticas da tabela com o package DBMS_STATS:
SQL> ALTER TABLE TAB_TESTE enable row movement;
ALTER TABLE TAB_TESTE SHRINK SPACE;
exec dbms_stats.gather_table_stats(USER,'TAB_TESTE'); -- substitua USER pelo owner da tabela.
exec dbms_stats.gather_table_stats(USER,'TAB_TESTE'); -- substitua USER pelo owner da tabela.
Caso queira aprender mais sobre o assunto sugiro participar comigo na próxima turma do treinamento Oracle Database Performance Tuning da Oramaster.
Por hoje é só!
[ ] s
Fábio Prado
0 comments:
Postar um comentário