Não se trata de ler um plano de explicação.
Não se trata de adicionar dicas ou medir o desempenho de uma consulta específica.
Sempre me perguntei se é possível para um determinado plano de explicação para uma consulta (ou um autotrace da consulta) "perguntar" ao Otimizador por que ele escolheu esse plano em vez de outros planos disponíveis para ele.
- Existe uma maneira de pedir ao banco de dados para exibir uma comparação de diferentes planos que considerou para a mesma consulta?
- Existe uma maneira de visualizar quais cálculos foram incluídos no custo exibido para um plano? (Por exemplo, o que o Optimizer assumiu wrt. acesso ao disco vs. coisas na memória, etc.)
Justificativa da pergunta: Com o EXPLAIN PLAN como eu conheço, quando o banco de dados escolhe um determinado plano de execução, e este é insuficiente/muito lento/... no primeiro momento um DBA ou desenvolvedor ad hoc não sabe: Se existe havia alguma alternativa a este plano e, além disso, não sabe por que, se o desenvolvedor conhece alternativas (por exemplo, um índice não utilizado), as alternativas não foram usadas.
Sim você pode.
Isso é chamado de rastreamento do otimizador Oracle. Fazer isso cria um arquivo de rastreamento no qual o otimizador despeja todo o raciocínio feito ao compor um plano.
Um exemplo de geração de rastreamento para um SQL específico pode ser encontrado aqui como rastrear otimizador para todo o sistema SQL específico - 10053 evento de rastreamento
Você pode precisar de um pouco de tempo para lê-lo, dada a quantidade de dados encontrados no trace, é incrível que o Oracle ainda encontre tempo para fazer isso e retornar em um tempo decente.
O Optimizer da Oracle é uma ciência inteira em si. Além de @ik_zelf, você deve consultar o TKPROF, que ajuda a tornar os arquivos .trc legíveis por humanos. Confira aqui e aqui os livros de Cary Millsap (principalmente o Oracle trace). Confira o livro de Jonathan Lewis e seu blogpara saber muito sobre o otimizador Oracle. O livro de Lewis tem 536 páginas (só se chama Fundamentals!) e ele disse que iria escrever outro (ainda não saiu). Há também um conceito chamado "estabilidade do plano" (Google it), onde você pode corrigir o plano se estiver satisfeito com o desempenho e não quiser que o otimizador "adivinhe" você, às vezes produzindo planos abaixo do ideal. Essas ideias parecem estar na linha de "melhor o diabo que você conhece do que aquele que você não conhece" - ou seja, é melhor um plano abaixo do ideal que funcione razoavelmente bem do que um que _poderia_ funcionar brilhantemente, mas com o risco de atrapalhar o funciona se o otimizador der errado.