Clique aqui para ler esse artigo em PDF.
Clique aqui para ler todos os artigos desta edição
Influenciando o otimizador de consulta baseado em custo do Oracle - Parte 3
Glenn Goodrum
Leitura obrigatória: SQL Magazine 18, Influenciando o otimizador de consulta baseado em custo do Oracle - Parte 1.
Leitura obrigatória: SQL Magazine 20, Influenciando o otimizador de consulta baseado em custo do Oracle - Parte 2.
As partes 1 e 2 desta série de artigos discutiram modos para melhorar planos de consulta mudando as estatísticas que o otimizador do Oracle usa para calcular os custos estimados das consultas. Mas há algumas situações para as quais o otimizador faz pré-suposições implícitas. Não podemos anular estas pré-suposições alterando as estatísticas, mas existem maneiras de influenciar o otimizador. Este artigo discute dois parâmetros ocultos, _like_with_bind_as_equality e _optim_peek_user_binds que podem ser úteis nestas situações. É verdade que palpites podem funcionar, mas uma das suposições nesta série de artigos é que as consultas estão embutidas em uma aplicação que não podemos mudar. Então, o objetivo será fazer com que o otimizador assuma o plano desejado, sem recorrer a palpites.
Predicados de desigualdade que envolvem variáveis de ligação podem fazer com que o otimizador escolha um plano não desejado. Se o otimizador não utilizar a variável de ligação, poderia então utilizar a densidade de coluna ou histograma para determinar um fator de seletividade razoavelmente preciso para o predicado. Mas para predicados na forma COLUMN > :BIND, o Oracle usa um percentual fixo de 5% para a seletividade (semelhante para >=, <, <=, e LIKE). Até pior, um predicado na forma COLUMN BETWEEN :B1 AND :B2 é equivalente a COLUMN >= :B1 AND COLUMN <= :B2, assim a seletividade é de 5% vezes 5%, ou 0.25%. Isto pode conduzir a situações nas quais a cardinalidade calculada é suficientemente inexata para fazer com que o otimizador escolha um plano ineficiente.
Note que histogramas não nos ajudam neste caso. Na ausência dos valores reais para os limites inferior e superior, o otimizador usará simplesmente seu fator de seletividade implícito de 5% ou 0.25%.
É importante entender que não somos contra variáveis de ligação e nem estamos defendendo que sejam abandonadas em favor de constantes embutidas nas consultas. Peritos em Oracle deram ênfase à importância de usar variáveis de ligação ao invés de constantes para os casos nos quais os valores mudam freqüentemente, evitando provocar uma enorme quantidade de análises gramaticais complicadas (hard parses) e inundar a área de SQL com inúmeras cópias de consultas idênticas.
Plataforma de testes
Os testes seguintes foram efetuados
Para todos os testes, computamos estatísticas que usam 100% das linhas, só para ter certeza de que os efeitos não foram provocados por erros de amostragem. Também somamos histogramas em todas as colunas, cujos conteúdos foram deliberadamente criados com uma distribuição distorcida. Além disso, usamos as configurações apresentadas na Listagem 1 para o otimizador.
unnest_subquery false
optimizer_dynamic_sampling 1
optimizer_features_enable 9.2.0
optimizer_index_caching 90
optimizer_index_cost_adj 30
optimizer_max_permutations 79999
optimizer_mode CHOOSE
Listagem 1. Configurações do otimizador.
Exemplo 1: Desigualdade numérica."
[...] continue lendo...