Atenção: esse artigo tem uma palestra complementar. Clique e assista!

O otimizador é o programa que realiza a transformação de comandos SQL em planos de execução. A partir do Oracle na versão 7, introduziu-se o Cost Based Optimizer (CBO) que, como o nome sugere, baseia-se no custo das operações. A partir do Oracle na versão 10g, esse otimizador se tornou default, sendo que seu antecessor, baseado em regras é bem mais simples de ser compreendido já que funciona a partir de 15 regras pré-definidas não levando em consideração a distribuição dos dados (ele é influenciado pela sintaxe do comando e ainda existe apenas por questões de compatibilidade). De acordo com [1], [2] e [3] (ver Referências Bibliográficas), o CBO gera suas estimativas para acesso aos dados baseado nas métricas de custo, cardinalidade e seletividade, sendo influenciado por parâmetros, statistics e hints (diretivas de compilação). Daí a importância de entendermos como as estatísticas funcionam e auxiliam o CBO na geração do plano de execução.

Antes de entrarmos no tópico de estatísticas vamos entender um pouco mais sobre o otimizador e como são gerados os planos de execução. O CBO é dividido em 3 componentes principais, conforme mostrado na Figura 1.

Figura 1. Componentes do CBO

O Query Transformer recebe um comando proveniente do analisador gramatical e o divide em diferentes blocos relacionados entre si. Seu objetivo é verificar se existe alguma forma mais otimizada para o comando em que se obtenha o mesmo resultado, gerando assim o melhor plano de execução. Para isto ele utiliza técnicas como:

• view merging: quando o analisador gramatical se depara com uma visão em um comando, ele a coloca em um bloco separado. O otimizador verifica qual é a melhor maneira de resolver essa visão com a criação de “sub-planos”. Após ter otimizado o bloco contendo a visão, o query transformer tenta agrupá-lo a outros blocos da consulta;

• subquery unnesting: assim como as visões, as sub-consultas são colocadas em blocos separados pelo analisador gramatical. O query transformer busca a melhor forma de resolver este comando, encontrando a maneira mais otimizada de resolver a sub-consulta e agrupando em um bloco da consulta principal, transformando esta consulta em uma junção da consulta principal;

• query rewrite with materialized views: uma visão materializada é similar a uma consulta armazenada em uma tabela. Quando o query transformer encontra uma consulta compatível com uma materialized view, a consulta é reescrita para utilizar a estrutura. Isto traz ganhos de desempenho, uma vez que parte do resultado já está previamente pronto, dispensando a necessidade de executá-lo novamente;

• predicate pushing: agrega um predicado que fica fora da visão para dentro da visão se isso melhorar na geração dos planos de execução.

O Estimator é responsável por indicar o esforço estimado para cada plano apresentado pelo QueryTransformer. Para gerar o custo, o estimator baseia-se em duas métricas: seletividade e cardinalidade, que serão explicados no decorrer deste artigo.

O Plan Generator testa diferentes planos de execução para uma consulta e escolhe aquele que apresenta o menor custo. Estes planos são gerados com variações de formas de acesso, formas de junção e ordens de junção. Para gerar o plano de execução de uma consulta, são gerados os sub-planos para cada bloco de visões não agrupadas e sub-consultas. Estes blocos são otimizados separadamente de baixo para cima, ou seja, a sub-consulta mais interna é otimizada em primeiro lugar, e um sub-plano é gerado para ela. Já a consulta mais externa é a última a ser otimizada. O número de planos possíveis é proporcional à quantidade de junções na clausula FROM. Para iniciar uma pesquisa pelo melhor plano, o gerador de planos organiza os itens da junção de acordo com sua cardinalidade, o item de menor cardinalidade é colocado em primeiro lugar e assim sucessivamente até o item de maior cardinalidade.

Agora vamos definir rapidamente os conceitos que influenciam o comportamento do otimizador:

• Custo: número gerado pelo otimizador para determinar o melhor método de acesso (estimativa);

• Seletividade: porcentagem de linhas retornadas conforme a aplicação de uma condição (filtro). A fórmula utilizada para cálculo depende da distribuição dos dados e da quantidade de buckets. Para as situações que analisaremos neste artigo utilizaremos as três Equações 1, 2 e 3.

Equação 1. Seletividade para valores não-populares