O otimizador do Oracle para desenvolvedores - Parte III - Gerador de Estimativas

Último artigo de uma série que apresenta aos desenvolvedores de sistemas de informação os aspectos importantes sobre o otimizador CBO do Oracle. Este artigo explica especificamente o gerador de estimativas, uma vez que é o componente mais importante do otimizador, detalhando os critérios utilizados para gerar as estimativas de custo dos comandos.

por Vinícius Ronconi

O artigo anterior mostrou os componentes do otimizador baseado em custos, explicando sobre o transformador de consultas, o gerador de estimativas e o gerador de planos. O fluxo da consulta por estes componentes foi apresentado, tornando possível ao leitor o entendimento sobre os passos executados pelo otimizador para encontrar qual é a melhor maneira de resolver uma consulta submetida ao servidor.

Verificou-se que o principal componente neste processo é o gerador de estimativas, uma vez que ele é o responsável por prever os recursos necessários para que o resultado seja apresentado ao usuário. Neste artigo, este componente será detalhado, exibindo as métricas utilizadas para calcular o esforço na resolução de uma consulta.

Para gerar a estimativa, o gerador de estimativas baseia-se em três métricas: seletividade (selectivity), cardinalidade (cardinality) e custo (cost). 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.

Seletividade

Representa uma fração da fonte de dados. Uma fonte de dados pode ser uma tabela, uma visão, o resultado de uma junção ou mesmo um operador GROUP BY. A seletividade é aplicada a uma condição da consulta (ex: alunos.nome = ‘Vinícius’), ou a um conjunto de condições (ex: alunos.nome = ‘Vinícius’ AND alunos.sobrenome = ‘Ronconi’). Esta métrica indica quantos registros serão filtrados pela condição, variando entre 0.0 e 1.0. O valor 0.0 indica que nenhum registro será selecionado pela condição, enquanto o valor 1.0 indica que todos os registros serão selecionados.

Caso não existam estatísticas para indicar a seletividade, o gerador de estimativas utiliza um valor padrão para esta métrica. Existem diferentes valores utilizados de acordo com o tipo de condição. Uma condição de igualdade (ex: alunos.nome = ‘Vinícius’) possui um valor padrão menor do que uma condição de faixa (ex: alunos.nome> ‘Vinícius’). Isto acontece porque se espera que um operador de igualdade selecione menos registros do que o operador de faixa.

Se as estatísticas estiverem disponíveis, elas são utilizadas para determinar a seletividade da condição. Por exemplo, o valor de seletividade da condição alunos.nome = ‘Vinícius’ será inversamente proporcional à quantidade de valores diferenciados para o campo alunos.nome. Desta maneira, quanto maior a diversidade de valores para o campo, menor será a quantidade de registros retornados pela condição.

Outro recurso existente para verificar a seletividade de uma condição é o histograma. Ele verifica a distribuição dos diferentes valores da coluna, garantindo assim uma estimativa ainda mais precisa do que as estatísticas. Sempre que o histograma estiver presente, o otimizador irá utilizá-lo para gerar as estimativas, portanto sua utilização é recomendada especialmente em colunas muito utilizadas para filtrar os registros e que possuam muitos valores diferenciados.

Cardinalidade

Representa o número de registros em uma fonte de dados. Uma fonte de dados pode ser uma tabela, uma visão, o resultado de uma junção ou mesmo um operador GROUP BY. A cardinalidade básica é a quantidade de registros numa fonte de dados. Este valor pode ser obtido através da análise dos objetos. Caso não existam estatísticas, então o Gerador de Estimativas utiliza a quantidade de extensões (extents) ocupadas pelo objeto para encontrar o valor da cardinalidade básica. Os tipos de cardinalidade existentes são descritos de forma resumida na Tabela 1.

Tipo	Descrição
Efetiva	Representa a quantidade registros realmente selecionados.
Junção	Representa a quantidade de registros gerados a partir da junção entre duas fontes de dados.
Distinção	Representa a quantidade de valores diferenciados.
Agrupamento	Representa a quantidade de registros após aplicar o GROUP BY.

Tabela 1 - Tipos de Cardinalidade

Tipo Descrição

A cardinalidade efetiva (effective cardinality) é a quantidade de registros que realmente serão selecionados da fonte de dados. Este valor depende das condições definidas na consulta, já que as condições filtrarão os valores que devem ser retornados. A cardinalidade efetiva é calculada pelo produto da cardinalidade básica e a seletividade das condições informadas na consulta. Se não houver nenhuma condição na consulta, então a cardinalidade efetiva é igual à cardinalidade básica.

A cardinalidade de junção (join cardinality) é o número de linhas produzido quando é feita a junção de duas fontes de dados. Uma junção é o produto cartesiano de duas fontes de dados, aplicando as condições para efetuar a junção entre as fontes. Desta forma, a cardinalidade de junção é o produto da cardinalidade das duas fontes de dados multiplicado pela seletividade das condições de junção.

A cardinalidade de distinção (distinct cardinality) é o número de valores diferenciados em uma coluna da fonte de dados. Por exemplo, se a tabela “alunos” possui 100 registros, mas apenas 20 valores diferentes para o campo “nome”, então a cardinalidade de distinção desta tabela é 20.

A cardinalidade de agrupamento (group cardinality) é a quantidade de registros existentes em uma fonte de dados após se aplicar o operador GROUP BY. Este valor depende da cardinalidade de distinção de cada coluna informada no operador GROUP BY. A cardinalidade de agrupamento é um valor entre a maior cardinalidade de distinção entre os campos do agrupamento e o menor valor entre o produto da cardinalidade de distinção dos campos e a quantidade de registros na fonte de dados.

Por exemplo, se uma consulta na tabela de alunos, que possui 100 registros, é agrupada pelos campos “nome” e “sobrenome”, e a cardinalidade de distinção destes campos é, respectivamente, 30 e 60, então a cardinalidade de agrupamento varia entre MAX (30, 60) e MIN (30*60, 100). Ou seja, a cardinalidade de agrupamento, neste caso, varia entre 60 e 100.

Custo

O custo é uma estimativa da quantidade de recursos que o servidor consumirá para resolver a operação, como a quantidade de acessos a disco, o consumo de CPU ou rede. Uma operação pode ser o acesso seqüencial a uma tabela, um acesso à tabela utilizando um índice, a junção de duas tabelas ou ainda a ordenação de um conjunto de registros.

A forma de acesso (Access Path) representa a quantidade de recursos necessária para acessar os dados numa tabela. Durante as formas de acesso “table scan” ou “full index scan” vários blocos da unidade de armazenamento são lidos numa única operação. Nestes casos, o custo de acesso depende da quantidade de blocos lidos e da capacidade de leitura de vários blocos numa única operação.

O custo para operações que utilizam a forma de acesso “index scan” depende da quantidade de níveis existentes na árvore binária, da quantidade de blocos na extremidade que precisam ser lidos e da quantidade de registros que serão buscados utilizando ROWID dos campos do índice.

O custo de junção (Join Cost) representa a quantidade de acessos individuais das fontes de dados da junção. Em uma junção, uma fonte de dados é chamada de interna e a outra de externa.

Nas junções aninhadas, para cada registro da fonte externa, a fonte interna é acessada para localizar todos os registros que satisfaçam a junção. Logo, em uma junção aninhada, a fonte de dados interna é acessada de acordo com a quantidade de registros da fonte externa. O custo da junção então pode ser expresso da seguinte maneira: custo de acesso da fonte externa + (custo de acesso da fonte interna * cardinalidade da fonte externa).

No “sort merge join”, as duas fontes de dados são ordenadas pelas chaves da junção, caso elas já não estejam na ordem da chave. O custo do “sort merge join” é custo de acesso da fonte externa + custo de acesso da fonte interna + custo de ordenação (se utilizado).

Na junção hash (hash join), a fonte de dados interna é colocada em uma tabela hash, utilizando a chave da junção. Cada registro da fonte de dados externa também é colocada na tabela hash, gerando os registros que atendem à junção. Caso o número de registros da fonte de dados interna seja muito grande, são criadas partições, contendo apenas parte dos registros. Cada registro da fonte de dados da fonte externa será colocado na tabela hash a fim de verificar os registros que atendem as condições de junção. Este procedimento é realizado para cada registro da fonte de dados externa. O custo do “hash join” pode ser obtido pela expressão (custo de acesso da fonte externa * número de partições) + custo de acesso da fonte interna.

Conclusão

Esta série de artigos apresentou ao leitor os otimizadores do Oracle, dando maior ênfase ao CBO, uma vez que se trata do único otimizador ainda em desenvolvimento. Seus principais componentes foram detalhados, permitindo que os desenvolvedores compreendam os critérios utilizados pelo Oracle para estimar os custos de resolver um comando.

Referências

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