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  e 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.

           

Referências:
   -  Interpreting Execution Plans
    - Treinamento SQL Tuning FABIOPRADO.NET