Por que o Otimizador criou um plano de execução ineficiente?

O plano de execução é uma sequência de operações que acessam os dados nas tabelas indicadas na instrução SQL, considerando os filtros especificados na cláusula WHERE, a sequência em que as tabelas são acessadas e o tipo de operação que será utilizado no acesso são determinados pela estimativa de quantidade de linhas que cada operação vai acessar, esta estimativa é chamada de “CARDINALIDADE” (Cardinality). Como base nisso podemos afirmar que a Cardinalidade é um fator decisivo na qualidade do plano de execução e consequentemente no tempo de reposta da instrução SQL.

Respondendo a questão proposta no titulo deste artigo, o Otimizador vai entregar um plano ineficiente quando a cardinalidade das operações do plano de execução for muito diferente da quantidade de registros real que a operação vai acessar. Como o erro de cardinalidade é uma das principais causas dos planos de execução com tempo de resposta insatisfatório, uma técnica de tuning muito utilizada consiste em identificar a causa desse erro e prover a solução para o mesmo.

Neste artigo vamos abordar de forma sucinta as principais causas que levam o Otimizador ao erro de cardinalidade das operações e quais técnicas podem ser utilizadas para contornar esse problema. Nos próximos artigos vamos abordar de forma mais detalhada cada uma dessas causas, mostrar exemplos práticos de como elas ocorrem e como corrigi-las.

Ausência de estatísticas ou falta de atualização

Quando o Otimizador precisa calcular a cardinalidade de uma operação ele utiliza as estatísticas de tabelas, colunas e índices. Essas estatísticas são fundamentais para o calculo da cardinalidade, se elas não existem ou não estão atualizadas o Otimizador utiliza alguns valores padrões o que acaba comprometendo a precisão do calculo e muitas vezes leva o Otimizador a escolher uma operação ineficiente para o plano de execução.

Para coletar ou atualizar as estatísticas de tabelas, colunas e índices devemos utilizar o pacote DBMS_STATS.GATHER_TABLE_STATS.

 

No artigo “Estatísticas: a matéria prima do otimizador” abordamos com mais detalhes os efeitos da ausência ou a falta de atualização das estatísticas na geração do plano de execução pelo Otimizador.

Repetição de dados não uniforme

Quando utilizamos na cláusula WHERE uma coluna de uma tabela que possui poucos valores distintos e a quantidade de registros para cada um desses valores não é uniforme, chamamos os dados dessa coluna de “Data Skew”. Por exemplo se consultarmos o campo cargo da tabela de empregados de uma empresa, provavelmente o numero de operadores vai ser muito superior que o numero de gerentes. Para realizar um calculo de cardinalidade com precisão satisfatória para instruções SQL que utilizam esse tipo de coluna na cláusula WHERE, o Otimizador precisa utilizar histogramas e se eles não existem para esse tipo de coluna o calculo da cardinalidade será impreciso.

Para criar Histogramas devemos utilizar o pacote DBMS_STATS.GATHER_TABLE_STATS.

 

No artigo “Histogramas: Qual importância deles?” abordamos com mais detalhes como a ausência de histogramas em colunas com repetição de dados não uniformes podem influenciar na qualidade do plano de execução gerado pelo Otimizador.

Múltiplas colunas de uma tabela na cláusula WHERE

Quando utilizamos duas ou mais colunas para uma tabela na cláusula WHERE, o Otimizador tende a subestimar a cardinalidade de linhas que serão acessadas nesta tabela, isto ocorre pois ele desconhece qualquer relação que existe entre os dados dessas colunas, nesse caso para ajudar o Otimizador a estimar a cardinalidade com maior precisão precisamos criar uma estatistica de grupo de colunas utilizando o pacote (DBMS_STATS.CREATE_EXTENDED_STATS) e coletar as estatísticas da tabela novamente.

No artigo “O Otimizador do Oracle precisa da nossa ajuda?” abordamos com mais detalhes como a presença de múltiplas colunas de uma tabela na cláusula WHERE podem influenciar na qualidade do plano de execução gerado pelo Otimizador.

Colunas com função na cláusula WHERE

A existência de função para uma coluna na cláusula WHERE faz com que o Otimizador despreze a existência de um Histograma para essa coluna pois o Otimizador não sabe como essa função vai afetar os dados, como consequência o Otimizador estima a cardinalidade da operação como 1% do total de registros da tabela. Para melhorar a precisão da cardinalidade nestes casos podemos criar estatísticas da coluna com a função utilizando o pacote (DBMS_STATS).

No artigo “Funções na cláusula WHERE podem confundir o Otimizador?” abordamos com mais detalhes como as colunas com função na cláusula WHERE podem influenciar na qualidade do plano de execução gerado pelo Otimizador.

Múltiplas colunas utilizadas na junção de tabelas

Quando utilizamos duas ou mais colunas para junção de duas tabelas na cláusula WHERE, o Otimizador utiliza um algorítimo que em algumas situações estima a cardinalidade da operação de forma imprecisa, neste caso para ajudar o Otimizador a estimar a cardinalidade com maior precisão precisamos criar estatísticas para as colunas utilizadas na junção das duas tabela utilizando o pacote (DBMS_STATS.CREATE_EXTENDED_STATS) e coletar as estatísticas das tabelas novamente.

Expressões complexas

Expressões complexas na cláusula WHERE envolvendo operadores como “>” ou “<” impossibilitam a utilização de estatísticas de grupo de colunas pelo otimizador, neste caso podemos utilizar “Dynamic sampling” para melhorar a estimativa de cardinalidade da operação no plano de execução.

Conclusão

Utilizando as técnicas descritas acima conseguimos alimentar o Otimizador com informações suficientes para que ele consiga calcular a cardinalidade das operações com maior precisão, desta forma ele consegue escolher o método de acesso mais eficiente e a melhor sequência para as operações visando retornar o resultado da instrução SQL no menor intervalo de tempo possível.

Referências

http://gavinsoorma.com/wp-content/uploads/2011/03/top_tips_for_optimal_sql_execution.pdf