O Otimizador

Para compreender planos de explicação, primeiro você precisa compreender como o otimizador funciona. O otimizador é uma função do banco de dados Oracle que escolhe o que imagina ser a forma mais eficiente de executar uma instrução SQL. Essa é uma etapa importante no processamento de qualquer instrução DML (Data Manipulation Language), como as seguintes: SELECT, INSERT, UPDATE, ou DELETE. Existem muitas formas de executar uma instrução SQL, por exemplo, variando a ordem em que as tabelas ou índices são acessados. O procedimento que o Oracle utiliza para executar uma instrução pode afetar muito a velocidade com que a instrução é executada. Os caminhos de acesso que o otimizador utiliza podem ser exibidos para gerar planos de explicação.

O otimizador tem quatro modos principais de operação: Regra, Custo Primeiro, Custo Total e Escolher. O modo selecionado orientará a estratégia do otimizador:

vtc00090000.gif Regra avalia possíveis caminhos de execução e os classifica com base em uma série de regras sintáticas.

vtc00090000.gif Custo Primeiro executa a instrução de uma forma que recupere a primeira linha de dados com a maior eficiência possível. Sua ênfase é a velocidade de recuperação.

vtc00090000.gif Custo Total executa a instrução de uma forma que cause o menor impacto no desempenho global. Sua ênfase é na velocidade de throughput total.

vtc00090000.gif Escolher chama o modo Custo Total, caso alguma tabela tenha sido analisada, e o modo Baseado em Regras, caso nenhuma tabela tenha sido analisada.

Você pode definir um objeto default para o otimizador especificando o parâmetro OPTIMIZER_MODE no arquivo init.ora do seu banco de dados ou pode alterar o otimizador para uma instrução SQL específica adicionando dicas a ela. As diferenças os métodos de regra e baseado no custo são listadas a seguir.

Método Baseado em Regras

Usando o método baseado em regras, o otimizador escolhe um plano de execução com base nos caminhos de acesso disponíveis e em suas classificações. Você pode usar a otimização baseada em regras para acessar dados relacionais e tipos de objeto. A classificação da Oracle para os caminhos de acesso é heurística. Se houver mais de uma forma de executar uma instrução SQL, o método baseado em regras utilizará a operação com a classificação mais baixa. Em geral, as operações de classificação mais baixa são executadas mais rapidamente do que as associadas a construções de classificação mais alta.

Método Baseado no Custo

Usando o método baseado no custo, o otimizador determina qual plano de execução é mais eficiente ao considerar os caminhos de acesso disponíveis e fatorá-los em informações com base no dicionário de dados do dicionário dos objetos de esquema (tabelas, clusters ou índices/partições) acessados pela instrução. O método baseado no custo também considera dicas, ou otimizações de sugestão, incluídas em um Comentário da instrução.

O método baseado no custo consiste nas seguintes etapas:

  1. O otimizador gera um conjunto de planos de execução potenciais para a instrução com base em seus caminhos de acesso e dicas disponíveis.

  2. O otimizador estima o custo de cada execução com base nas características de estatísticas de distribuição e armazenamento de dados para as tabelas, clusters e índices do dicionário de dados.

  3. O custo de um valor proporcional estimado em relação ao uso esperado para o recurso necessário à execução da instrução com o plano de execução. O otimizador calcula o custo com base nos recursos computacionais estimados, incluindo (mas não limitados a) E/S, tempo de CPU e memória, que são necessários para executar a instrução com o plano de execução.

Os planos de execução em série com custos mais altos levam mais tempo para serem executados do que aqueles com custos mais baixos. No entanto, durante a utilização de um plano de execução paralela, o uso do recurso não é diretamente relacionado ao tempo decorrido.

4. O otimizador compara o custos dos planos de execução e escolhe o de menor custo.

Observação: Você deve analisar o esquema do seu banco de dados com o comando ANALYZE para manter as estatísticas atualizadas. Se você não analisar o esquema de banco de dados regularmente, o otimizador utilizará estatísticas potencialmente inválidas e falharão ao escolher o método de acesso mais eficiente.

Objetivo do Método Baseado no Custo

Por default, o objetivo do método baseado no custo é o melhor throughput ou a utilização mínima de recursos para processar todas as linhas acessadas pela instrução.

O Oracle também otimiza uma instrução com o objeto de obter o melhor tempo de resposta possível ou uma mínima utilização de recursos necessários para processar a primeira linha acessada pela instrução SQL.

Estatísticas para o Método Baseado no Custo

O método baseado no custo utiliza estatísticas para estimar o custo de cada plano de execução. Essas estatísticas quantificam as características de distribuição e armazenamento de dados de tabelas, colunas, índices e partições. Você pode gerar essas estatísticas usando o comando ANALYZE. O otimizador utiliza essas estatísticas para estimar o volume de E/S, o tempo de CPU e a memória necessária para executar uma instrução SQL com determinado plano de execução.

Quando Usar o Método Baseado no Custo

O Oracle SQL Analyze permite que você teste tanto o método baseado em regras quanto o baseado no custo. No entanto, em geral, você deve usar o método baseado no custo para todas as novas aplicações; o método baseado em regras é fornecido para aplicações criadas antes de a otimização baseada no custo estar disponível. A otimização baseada no custo pode ser mais eficiente porque sua avaliação se baseia nas informações mais recentes sobre o desempenho do seu banco de dados. Em geral, ele escolhe um plano de execução que seja tão bom ou melhor que o plano escolhido pelo método baseado em regras, especialmente para consultas longas com várias junções ou vários índices.

No entanto, como o otimizador baseado no custo depende muito das estatísticas de objeto, é importante renovar periodicamente essas estatísticas usando o comando ANALYZE.

Os seguintes recursos só podem usar a otimização baseada no custo:

vtc00090000.gif tabelas particionadas

vtc00090000.gif views particionadas

vtc00090000.gif tabelas organizadas por índices

vtc00090000.gif índices de chave invertida

vtc00090000.gif índices de bitmap

vtc00090000.gif consulta paralela e DML paralelo

vtc00090000.gif transformação em estrela

vtc00090000.gif junção em estrela

A otimização baseada no custo deve ser usada para proporcionar um desempenho adequado a consultas em estrela. Da mesma forma, ela deve ser usada com histogramas e junções de hash.

A otimização baseada no custo é sempre usada com consultas paralelas e com tabelas particionadas.