Como desfragmentar tabelas no Oracle? (parte 2)

Olá pessoal,

     Este artigo é continuação de outro com o título "Como desfragmentar tabelas no Oracle (parte 1)" e nele irei abordar os seguintes tópicos:

         - Como evitar ou minimizar a quantidade de LEMs em uma tabela?
         - Como identificar e eliminar as LEMs?

     No artigo anterior expliquei os conceitos gerais sobre fragmentação de dados, conceitos sobre blocos vazios (popularmente conhecidos como "buracos") e LEMs nas tabelas, e mostrei como identificar e eliminar os "buracos" nelas. Vejamos agora os tópicos que citei no parágrafo anterior.

Imagem 01: Ilustração de uma chained row (linha encadeada)
Fonte: The Secrets of Oracle Row Chaining and Migration, https://www.akadia.com/services/ora_chained_rows.html

Como evitar ou minimizar a quantidade de LEMs em uma tabela?

     Considere as dicas abaixo para evitar ou minimizar a quantidade de LEMs em uma tabela:

     1- Configure apropriadamente os parâmetros PCTFREE e PCTUSED:

     Uma tabela que sofre muitos UPDATEs talvez requeira um aumento no valor de PCTFREE, pois com mais espaço livre nos blocos para futuras atualizações, menor a probabilidade de criação de LEMs. Já em tabelas que sofrem apenas INSERTs e DELETEs, talvez seja melhor configurar um valor de PCTFREE menor (já que não é necessário reservar espaço nos blocos para futuros updates) que o valor padrão de 10 ou até mesmo zerá-lo. PCTUSED também poderia ser configurado para um valor menor, neste caso, mas somente se o segmento estiver configurado no modo "manual automatic segment-space management", caso contrário ele será ignorado.

     2- Configure apropriadamente o tamanho do bloco da tabela:
     Se os dados de uma linha de uma tabela não cabem em único bloco considere configurar um dos parâmetros DB_NK_BLOCK_SIZE de acordo com o tamanho das linhas e crie um tablespace com um tamanho de bloco igual ao do parâmetro configurado para essa tabela. Isso é bastante apropriado em ambientes OLAP. Em OLTP deve ser avaliado cuidadosamente.

Como identificar e eliminar as LEMs?

     Para identificar e eliminar LEMs em um determinado BDs que você administra, sugiro que você execute os passos abaixo:

1- Verificação de LEMs no nível do BD:
     É bem fácil descobrir se o BD possui LEMs em alguma tabela, basta analisar a estatística "table fetch continued row" que indica quantidade de LEMs encontradas nas consultas que já foram efetuadas no nível da sessão ou instância. Essa estatística pode ser consultada nas visões v$sysstat, v$sesstat ou v$mystat.

     Para ver LEMs no nível da instância: 

              select value 
     from   v$sysstat 
     where  name like 'table fetch continued row%';

     Para ver LEMs acessadas por cada sessão do BD: 

          SELECT b.sid, b.value

          FROM   v$statname a, v$sesstat b

          WHERE  a.statistic# = b.statistic#

          AND    lower(a.name) = 'table fetch continued row';

     Para ver LEMs acessadas na sua sessão: 

          SELECT b.value

          FROM   v$statname a, v$mystat b

          WHERE  a.statistic# = b.statistic#

          AND    lower(a.name) = 'table fetch continued row';

     Este método é uma forma mais rápida de identificar se existem LEMs sob uma visão macro do BD. Se o valor da consulta for maior que zero você já saberá que existem LEMs no BD, porém não saberá ainda em quais tabelas elas estão. Para isso, parta para o próximo passo.

2- Identificação das tabelas que possuem LEMs:

     Para identificar as tabelas que possuem LEMs você pode criar um bloco PL/SQL com SQL dinâmico para executar o comando abaixo em cada tabela dos schemas de usuários do BD:

     ANALYZE TABLE ECOMMERCE.PEDIDO COMPUTE STATISTICS;

     Execute em seguida uma consulta na visão DBA_TABLES e veja o valor da coluna CHAIN_CNT. Se for maior que zero, significa que existem LEMs dentro do segmento da tabela:

     select blocks "Blocos Usados", chain_cnt "LEMs",
            num_rows "Total de Linhas"
     from   dba_tables
     where  owner = 'ECOMMERCE'
     AND    table_name='PEDIDO';

     Depois que foi identificado que a tabela possui LEMs, elimine-as através de um simples ALTER TABLE nome_tabela MOVE ou utilizando o pacote DBMS_REDEFINITION. O ALTER TABLE MOVE é bem mais simples, mas gera indisponibilidade para instruções DML, por isso, se você precisa fazer essa manutenção à quente, sem afetar os DMLs, sugiro a 2º opção, ou o método que vou descrever no próximo passo (meu preferido).

     Para quem quiser aprender mais sobre DBMS_REDEFINITION, sugiro a leitura dos seguintes documentos no MOS: 

          - DBMS_REDEFINITION ONLINE REORGANIZATION OF TABLES (Doc ID 149564.1);

          - How to Find Fragmentation for Tables and LOBs (Doc ID 2132004.1).

IMPORTANTE: 

1- Depois que você executou ANALYZE TABLE e terminou de fazer a sua análise de LEMs, colete novamente estatísticas com DBMS_STATS (ver artigo Coletando estatísticas para o otimizador de queries do Oracle);

2- Depois de executar um ALTER TABLE MOVE os índices ficam inválidos, portanto, lembre-se de fazer um REBUILD neles.
  

  

3- LIST CHAINED ROWS:

     Já sabemos qual tabela tem LEMs, então agora é só executar o comando "ANALYZE TABLE nome_tabela LIST CHAINED ROWS" para que algumas informações sobre as LEMs sejam gravadas na tabela CHAINED_ROWS, que deve ser criada previamente através da execução do script $ORACLE_HOME/rdbms/admin/utlchain.sql: 

     ANALYZE TABLE ECOMMERCE.PEDIDO LIST CHAINED ROWS;

     Este método é o meu preferido para identificar LEMs, pois ele grava na tabela CHAINED_ROWS os ROWIDs das LEMs, facilitando depois o processo de eliminação delas. Fazendo um join na CHAINED_ROWS com a a tabela analisada é possível recuperar as os dados completos das linhas que estão encadeadas ou migradas, e armazená-las em uma tabela temporária qualquer que tenha a mesma estrutura da tabela analisada (criando-a a partir de um CTAS, por exemplo), para depois apagar as linhas da tabela original e reinseri-las novamente a partir de um SELECT na tabela temporária. A reinserção através deste método é bastante eficiente para eliminar este tipo de "fragmentação" e é o que causa menor impacto no ambiente (você não precisará se preocupar com locks de DML, estatísticas, índices etc.). Nos treinamentos "Database Performance Tuning" eu compartilho com os alunos alguns scritps que facilitam este trabalho para analisar/eliminar/reinserir as LEMs.
   

     Para finalizar é importante ressaltar que os procedimentos de eliminação de blocos vazios e LEMs descritos neste artigo e no artigo anterior, podem reduzir o tamanho do segmento da tabela (compactação lógica), mas não o tamanho físico do(s) datafile(s) do tablespace em que ela está alocada. Se você precisar também diminuir o tamanho deles, sugiro a leitura do artigo 

Reorganizando o Tablespace ....

FIM