O otimizador do Oracle para desenvolvedores - Parte II - Otimizador Baseado em Custos

Segundo artigo de uma série que apresenta aos desenvolvedores de sistemas de aplicações alguns aspectos relevantes sobre o otimizador do Oracle. Este artigo detalha o otimizador baseado em custos (CBO), exibindo seus principais componentes e a interação existente entre eles.

por Vinícius Ronconi

O artigo anterior apresentou ao leitor os principais componentes utilizados pelo servidor de banco de dados Oracle para resolver as consultas que lhe são enviadas. Os componentes foram explicados, com ênfase sobre o otimizador, que é o componente mais importante do processo.

Este artigo visa mostrar ao leitor o otimizador baseado em custo de forma mais aprofundada do que o artigo anterior, mostrando os principais componentes do CBO e sua forma de trabalho.

O objetivo do CBO é encontrar o melhor plano para que um determinado comando seja executado. Para isto, ele primeiro gera diferentes maneiras de resolver o comando e em seguida estima o custo para resolver cada uma das opções geradas. Esta estimativa baseia-se nas estatísticas contidas no dicionário de dados e os recursos existentes na máquina. Aquele plano que apresentar a menor estimativa de esforço é o escolhido para a execução do comando.

Figura 1 – Fluxo dentro do otimizador

O otimizador baseado em custo é composto por três componentes: transformador de consultas (query transformer), gerador de estimativas (estimator) e gerador de planos (plan generator). A Figura 1 ilustra a interação existente entre estes componentes.

O transformador de consultas busca uma nova maneira de escrever a consulta e a encaminha para o gerador de estimativas. Este tenta utilizar estatísticas existentes, ou valores padrões do otimizador, para estimar o esforço para resolver a consulta. Em seguida o gerador de planos analisa as opções apresentadas e escolhe aquela que oferece o melhor desempenho.

Transformador de consultas

Este componente recebe um comando analisado pelo analisador gramatical e o divide em diferentes blocos relacionados entre si. O objetivo do transformador de consultas é verificar se existe alguma forma mais otimizada para se obter o mesmo resultado, gerando assim um plano melhor. Para isto, são utilizadas três técnicas, que podem ser utilizadas em conjunto: agrupamento das visões (view merging), eliminar sub-consultas e reescrever a consulta utilizando visões materializadas.

Agrupamento das visões

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 esta visão com a criação de alguns “sub-planos”.

Após ter otimizado o bloco contendo a visão, o transformador de consultas tenta agrupá-lo em outros blocos do comando, o que é possível na maioria dos casos, diminuindo assim a quantidade de blocos e minimizando a existência de blocos não otimizados.

Eliminar sub-consultas

Assim como as visões, as sub-consultas são colocadas em blocos separados pelo analisador gramatical. O transformador de consultas 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 aninhada em uma junção da consulta principal.

Aquelas sub-consultas não transformadas recebem um sub-plano de execução buscando a melhor forma de resolver a consulta.

Reescrever a consulta utilizando visões materializadas

Uma visão materializada é similar a uma consulta armazenada em uma tabela. Quando o transformador de consultas encontra uma consulta compatível com uma visão materializada, ela é 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.

A consulta somente será reescrita para utilizar a visão materializada caso exista algum ganho de desempenho. Caso contrário, a consulta atual será mantida intacta.

Gerador de estimativas

Este é o principal componente do otimizador, uma vez que é o responsável por indicar o esforço estimado para cada plano apresentado pelo transformador de consultas. Para gerar a estimativa, o gerador de estimativas baseia-se em três métricas: seletividade, cardinalidade e custo. Cada métrica é relacionada e derivada das demais. Caso o banco de dados possua estatísticas sobre os objetos, esta informação será utilizada para melhorar a precisão da estimativa.

Gerador de planos

O objetivo do gerador de planos é testar os diferentes planos para uma consulta e escolher aquele que apresenta o menor custo. Estes planos são gerados com variações das formas de acesso, formas de junção e ordens de junção.

Figura 2 – Exemplo da ordem de junção

A ordem de junção (join order) é a seqüência em que os diferentes itens da junção são acessados e ordenados. Por exemplo: caso a ordem de junção seja “alunos”, “turmas” e “disciplinas”, então a tabela “alunos” é acessada em primeiro lugar. Em seguida é feito o acesso à tabela de “turmas”, e uma junção com os dados de “alunos” será realizada. Finalmente, a tabela “disciplinas” é acessada, e a junção é feita com o resultado da junção de “alunos” x “turmas”. Este comportamento é ilustrado pela Figura 2.

Para a geração do plano 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, ou seja, a consulta completa, é a última a ser otimizada.

O número de planos possíveis é proporcional à quantidade de junções na cláusula FROM. À medida que novos itens são acrescentados nesta cláusula, a quantidade de planos aumenta exponencialmente. Por esta razão, o gerador de planos utiliza um mecanismo interno para reduzir a quantidade de opções. Este mecanismo baseia-se no custo do melhor plano encontrado até o momento. Caso o plano ainda possua um alto custo, então o gerador de planos permite que mais opções sejam tentadas até que se encontre um plano com baixo custo. Neste momento o gerador de planos deixa a busca por planos melhores, já que o benefício alcançado seria pequeno.

Para iniciar a pesquisa pelo melhor plano, o gerador de planos organiza os itens da junção de acordo com sua cardinalidade. O item com a menor cardinalidade efetiva é colocado em primeiro lugar, seguindo assim sucessivamente, até chegar ao final com o item de maior cardinalidade efetiva.

Conclusão

Os componentes do otimizador baseado em custo foram apresentados neste artigo, buscando dar aos desenvolvedores os conceitos importantes para seu dia-a-dia, sem entrar em detalhes sobre a administração do banco de dados.

O próximo artigo detalhará as métricas do gerador de estimativas. Este componente dará subsídios ao otimizador para realizar a escolha do plano de execução. Caso um servidor de banco de dados possua um gerador de estimativas ineficiente, seu desempenho, consequentemente, deixará a desejar.

Referências:

• http://download-est.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm