Fonte:
http://www.fabioprado.net/2011/03/analisando-o-plano-de-execucao-para.html
Analisando o Plano de Execução para tunar instruções SQL
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).
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 é 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).
O problema maior ao analisar um plano de execução é que pode ser muito trabalhoso analisar o plano de uma instrução SQL longa e complexa (você pode demorar muitas horas para entender o que ela irá fazer), portanto, para aumentar a sua produtividade, é importante entender os pontos principais que devem ser sempre analisados.
Nenhum comentário:
Postar um comentário