Social Icons

17 de mar. de 2011

Analisando o Plano de Execução para tunar instruções SQL


Artigo revisado em 06/01/2017   

Olá Pessoal,
  
     No artigo de hoje irei comentar sobre o que é o Plano de Execução (no Oracle Database) e como utilizá-lo para nos a ajudar a tunar queries, e consequentemente, criarmos aplicações com melhor desempenho.
  
     O Plano de Execução de uma instrução SQL é uma sequência de operações que o Banco de Dados (BD) Oracle realiza para executar uma instrução. Ele é exibido em forma de uma árvore de linhas, que representam passos e que contém as seguintes informações:
  
       - Ordenação das tabelas referenciadas pela instrução;
       - Método de acesso para cada tabela mencionada na instrução;
       - Método join para as tabelas afetadas pelas operações join da instrução;
       - Operações de dados tais como filter, sort ou agregação;
       - Otimização: custo e cardinalidade de cada operação;
       - Particionamento: conjunto de partições acessadas;
       - Se a instrução utilizará execução paralela etc.
  
     O Plano de Execução pode mudar conforme o ambiente em que está sendo executado. Ele pode mudar se for executado em schemas diferentes ou ambientes de Bancos de Dados com custos (volume de dados e estatísticas, parâmetros de servidor ou sessão etc.) diferentes.
  
     Os resultados de um plano de execução permitem visualizar as decisões do Otimizador de Query do Oracle  e analisar a performance de uma query. Através dele é possível verificar se uma query acessa dados através de full table scan (°) ou index lookup (¹), e até mesmo, qual tipo de join ela efetuou: um nested loops join (²) ou um hash join (³).

(°) Full table scan: Caminho de acesso em que os dados são recuperados percorrendo todas as linhas de uma tabela. É mais eficiente para recuperar uma grande quantidade de dados da tabela. 
(¹) Index Lookup: Caminho de acesso em que os dados são recuperados através do uso de índices. É mais eficiente para recuperar um pequeno conjunto de linhas da tabela. 
(²) Nested loop join: Método de acesso de ligação (join) entre 2 tabelas ou origens de dados, utilizado quando pequenos conjuntos de dados estão sendo ligados e se a condição de ligação é um caminho eficiente para acessar a segunda tabela. 
(³) Hash join: Método de acesso de ligação (join) entre 2 tabelas ou origens de dados, utilizado para ligar grandes conjuntos de dados.
 
     O Plano de Execução, por si só, não pode diferenciar instruções SQL bem tunadas (mais otimizadas) daquelas que não apresentam boa performance. O acesso a dados por meio de índices normalmente é mais rápido que acesso full table scan, em ambientes OLTP, porém o fato do Plano de Execução utilizar um índice em algum passo da execução não necessariamente significa que a instrução será executada eficientemente. Em alguns casos, índices podem ser extremamente ineficientes. Segundo a Oracle, quando uma consulta irá retornar mais que 4% dos dados de uma ou mais tabelas ou quando uma consulta irá acessar tabelas pequenas (com poucas linhas), geralmente é mais rápido o acesso full table scan do que o acesso index lookup.
  
     Para analisar o Plano de Execução de uma instrução SQL pode-se utilizar o comando EXPLAIN PLAN, que permite exibir um plano escolhido pelo otimizador de queries do Oracle, para executar as instruções  SQL. Ao executá-lo, o otimizador escolhe um plano de execução e insere os dados descrevendo este plano em uma tabela do BD chamada PLAN_TABLE (é possível também gravar em outras tabelas). Para analisar o plano de execução é necessário escrever uma consulta para pesquisar essa tabela (SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

     Segue abaixo a Imagem 01, com um exemplo de como usar o EXPLAIN PLAN para ver os seus resultados e analisar o plano de execução de uma instrução SQL:


Imagem 01 - Exemplo de um plano de execução
                  
                 Obs.: Se no resultado (PLAN_TABLE_OUTPUT)  você não visualizar a coluna Time, conecte-se no BD através do SQL Plus e execute os comando(s) e script(s) abaixo:
       
                      drop table plan_table purge;
                      commit;
                      @$ORACLE_HOME/rdbms/admin/catplan.sql -- exemplo considerando SO Linux. Substitua $ORACLE_HOME pelo diretório correspondente ao Oracle Home do BD. 

      
     Seguem abaixo algumas regras gerais para analisar um plano de execução:
          - Em geral, a ordem de execução tem início na linha que está identada mais para a direita, seguindo a ordem do primeiro filho do nó raiz da árvore (para compreender melhor este item, consulte as referências ao final do artigo);
          -  O próximo passo a ser executado é o pai da linha encontrada no passo anterior, ou seja, a linha que está identada no nível à esquerda mais próximo;
          -  Se 2 linhas estão identadas igualmente (no mesmo nível), a linha mais acima é executada primeiro;
          -  Avalie as operações que estão sendo executadas (coluna Operation) e as estatísticas dessas operações: a quantidade de bytes (coluna Bytes) e o custo (coluna Cost) de cada passo ou simplesmente o tempo de resposta das operações (coluna Time). 
 
     Para tunar uma query, altere uma instrução SQL inúmeras vezes, analise o plano de execução de cada versão que foi alterada e opte por implementar aquela versão que consome menos recursos (colunas Bytes e Cost (%CPU)) ou que apresenta menor tempo de resposta (coluna Time). Verifique também se as operações que estão sendo executadas em cada passo do plano de execução são adequadas para a quantidade de dados a ser retornada. Se não forem adequadas, quando por exemplo no caso das estatísticas das tabelas acessadas não estarem atualizadas, é possível forçar uma operação que possa ser mais performática através do uso de hints.


CONCLUSÃO

  
   De um modo geral não é muito difícil analisar um plano de execução quando você conhece as operações  estatísticas que podem estar contidas nele e as regras gerais para que você possa analisá-lo. 
  
   É importante entender que o Plano de Execução que foi apresentado aqui é apenas uma estimativa e não o tempo real de execução da query; e que os seus passos e estatísticas podem variar se a instrução SQL for executada em ambientes diferentes (Ex.: Produção e Homologação). Existem outras formas de gerar um Plano de Execução, inclusive o Plano de execução real ou executado de um SQL, que deixo para ensinar nos treinamentos de SQL Tuning.

     SQLs grandes e complexos, como por exemplo, um SQL com dezenas ou centenas de linhas que possui um plano de execução com tamanho similar, são muito difíceis (ou trabalhosos) de se tunar. A gente pode levar horas ou até mesmo dias para analisar estes planos e elaborar um bom diagnóstico (identificar o que está ruim e o que mudar para melhorar o desempenho). Para aumentar a sua produtividade é importante aprender a analisar primeiramente os pontos principais. Como este assunto é longo e não dá para falar aqui, em 1 só artigo, deixo também para ensinar nos treinamentos de SQL Tuning.

           

8 comments:

  1. Olá Fabio, parabéns pelo o artigo.

    Estou com uma duvida, sobre o plano de execução de tabelas particionadas.
    Tenho a seguinte situação: Na query, é informado no filtro um between de datas, que é o campo chave do particionamento, é informado, por exemplo, 01/09/2014 a 19/11/2014 e com isso o Oracle faz um partition range iterator, e nas colunas partition_start e partition_stop aparece exatamente quais são as partições que serão acessadas, porém ao informar um dia a mais, por exemplo, 01/09/2014 a 20/11/2014, o Oracle não informa exatamente quais são as partições que serão acessadas, ou seja, ao invés de aparecer os valores nas colunas partition_start e partition_stop, aparece a palavra Key, o Oracle descobrirá as partições no momento de execução, porém isso, está causando uma grande lentidão, você sabe me dizer, se há algum hint para que o Oracle informe as partições antes da execução? que apareça no plano de execução, os valores reais nas colunas partition_start e partition_stop?
    Muito obrigada.

    ResponderExcluir
    Respostas
    1. Anônima, deduzo que a coluna seja do tipo DATE, certo? Se sim, vc está usando a função TO_DATE nos valores das datas? Ou está usando variáveis bind?

      []s

      Excluir
    2. Olá, a coluna é DATE sim, e está utilizando a função TO_DATE nos valores das datas, valores reais, o estranho que as vezes o Oracle faz um partition range iterator, e dependendo do valor da data, o Oracle não mostra a partition start e stop, ou seja, o Oracle decide quais partições utilizar no momento da execução e isso o torna muito lento.
      Estou desconfiada que seja estatísticas, pois as estatísticas locais estão atualizadas, mas as estatísticas globais não estão.
      Muito obrigada.

      Excluir
    3. Eu nunca passei por este problema, mas o que vc falou tem sentido! É possível sim que algumas partições que contenham os dados solicitados não tenham estatísticas (ou tenham estatísticas desatualizadas) e isso pode estar influenciando negativamente na performance do SQL. Colete estatísticas globais utilizando paralelismo (para coletar mais rápido) e nos dê um feedback posteriormente.

      []s

      Excluir
    4. Muito obrigada Fabio,
      Ok, vou dar sim um feedback. :)
      Duvida: Qual é o percentual minimo para atualizar as estatísticas? Obrigada.

      Excluir
    5. Não existe % mínimo. Você pode coletar na hora em que quiser ou precisar, só não adianta coletar estatísticas quando tiver poucas atualizações (ou nenhuma), pois um pequeno % de atualizações normalmente não afeta o desempenho dos SQLs.

      []s

      Excluir
  2. Olá Fábio! Tudo bem? Fui aluno seu no curso presencial e comprei também um curso pela internet. Ótimos cursos! Recomendo. Estou com uma dúvida. Tenho uma instrução SQL, que me gera relatórios com campos Date na cláusula Where onde passo variáveis bind. A minha pergunta é: dependendo do período que informo as variáveis bind, ele irá gerar um plano de execução diferente? Já que a quantidade de registros de uma relatório diário, é diferente da quantidade de registros para um reletório mensal? Obrigado e abraços.

    ResponderExcluir
    Respostas
    1. Christian, lembro de você, tudo bem?
      Se for 11G ou superior o Otimizador poderá sim gerar planos de execução diferentes se a coluna do filtro tiver índice e histograma. No 10G ele não fazia isso muito bem, ok?

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)