N style="mso-spacerun: yes">capasql23.jpg

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 em um PC dedicado de potência média, rodando Oracle 9.2.0.1 e Windows XP. Desejamos que os testes ilustrem como o tempo de execução e as E/S lógicas são reduzidos quando o otimizador escolhe um plano melhorado.

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.

Neste exemplo, temos dois operadores BETWEEN para compor o efeito de fator de seletividade muito baixo (ver Listagem 2).

 

select    c.type customer_type,
          i.type item_type,
          count (*)
from      customer c
join      ord o        on (o.cust_id = c.cust_id)
join      line_item li on (li.order_num = o.order_num)
join      item i       on (i.item_id = li.item_id)
where     li.amount between :a1 and :a2
and       li.quantity between :q1 and :q2
group by c.type,
          i.type
;

Listagem 2. Consulta com dois operadores between.

 

...

Quer ler esse conteúdo completo? Tenha acesso completo