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:
Regra avalia possíveis caminhos de execução e os classifica com base em uma série de regras sintáticas.
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.
Custo Total executa a instrução de uma forma que cause o menor impacto no desempenho global. Sua ênfase é na velocidade de throughput total.
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:
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:
tabelas particionadas
views particionadas
tabelas organizadas por índices
índices de chave invertida
índices de bitmap
consulta paralela e DML paralelo
transformação em estrela
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.