Uma instrução SQL pode ser executada de muitas maneiras diferentes, como varreduras completas de tabela, varreduras de índice, loops aninhados e junções de ‘hash’. O otimizador de consulta determina a maneira mais eficiente de executar uma instrução SQL depois de considerar muitos fatores relacionados aos objetos referenciados e as condições especificadas na consulta. Essa determinação é uma etapa importante no processamento de qualquer instrução SQL e pode afetar muito o tempo de execução.

Operações do otimizador:

1. Avaliação de expressões e condições.
   O otimizador primeiro avalia as expressões e condições que contêm constantes da maneira mais completa possível.

2. Transformação de declaração.
   Para instruções complexas envolvendo, por exemplo, subconsultas ou visualizações correlacionadas, o otimizador pode transformar a instrução original em uma instrução de junção equivalente.

3. Escolha de metas do otimizador.
   O otimizador determina o objetivo da otimização.

4. Escolha de caminhos de acesso.
   Para cada tabela acessada pela instrução, o otimizador escolhe um ou mais dos caminhos de acesso disponíveis para obter os dados da tabela.

5. Escolha de pedidos de junção.
   Para uma instrução de junção que une mais de duas tabelas, o otimizador escolhe qual par de tabelas é unido primeiro e, em seguida, qual tabela é unida ao resultado e assim por diante.

O objetivo número um do otimizador, é sempre melhorar o tempo de resposta.

O otimizador do SGBD vai apresentar o plano para a execução da atividade (SQL) que ele recebeu, cabe-nos saber interpretar se aquilo que construímos tem uma boa proposta de solução por parte do SGBD, ou seja, está custoso? pode ser melhorado? ou, isso é crítico?
O Oracle como vários outros SGBDs, oferece uma poderosa ferramenta para avaliar e explicar como faria se a tarefa (SQL) fosse submetida para execução, uma previsão, pois de fato o custo real pode variar devido a fatores, como: recurso, concorrência, etc.

Visão geral do PLANO DE EXPLICAÇÃO (EXPLAIN PLAN):
É possível examinar o plano de execução escolhido pelo otimizador para uma instrução SQL usando a ferramenta EXPLAIN PLAN. Quando a instrução é emitida, o otimizador escolhe um plano de execução e, em seguida, insere dados que descrevem o plano em uma tabela temporária do banco de dados. Basta emitir a instrução EXPLAIN PLAN e, em seguida, consultar a tabela de saída:

EXPLAIN PLAN FOR
SQLStatement;

Para ver o resultado da saída da tabela temporária, PLAN_TABLE:

SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY ());
ou
SELECT * FROM TABLE(DBMS_XPLAN.display);

Alguns Exemplos:

Exemplo 1. Busca simples com filtro.

Descrevendo as colunas do EXPLAIN PLAN.

ID - Identificação da operação.
OPERATION - Descrição da operação do otimizador.
NAME - Mostra o nome do objeto da operação do otimizador.
ROWS - Mostra o número de linhas estimado pelo otimizador.
BYTES - Mostra o número de bytes estimado pelo otimizador.
COST - Mostra o custo estimado pelo otimizador.
TIME - Mostra o tempo estimado pelo otimizador.

Neste exemplo é apresentada uma busca simples, havendo somente uma tabela envolvida, com um filtro para coluna BE_MARCA.

Exemplo 2. Busca com ‘join’ e filtro

Mais informações do resultado do EXPLAIN PLAN.

PREDICATE INFORMATION:
Este espaço apresenta informações valiosas de como o otimizador está
executando cada operação. Pelo número do 'id' se identifica de qual linha da
operação se trata.
O mais comum é haver dois tipos de informação:
ACCESS – significa que vai utilizar algum índice para selecionar os registros
da operação.
FILTER – significa que selecionou mais registros do que o necessário e,
precisa excluir linhas de acordo com o critério informado na clausula
'where'.

Neste outro exemplo, a busca contém um 'Join' das duas tabelas com as colunas de marca, e propõe ainda o filtro para os bens da marca 'CAVALETTI'. Pode perceber que o otimizador não faz uso de nenhum índice, a leitura é sequencial e em todos as linhas dos objetos.

Criando um índice na coluna marca da TB_BEM.

CREATE INDEX SGCMANUT_AP.IXD_MARCA_BEM
ON SGCMANUT_AP.TB_BEM (BE_MARCA ASC);

Exemplo 3. Tratando da mesma consulta do exemplo 2, executada após criação do índice.

Podemos perceber o otimizador fazendo uso do novo índice (IXD_MARCA_BEM) e já apresentando um custo menor, se comparado ao exemplo 2, que executa a mesma consulta.

Criando um índice na coluna marca da TB_MARCA.

CREATE INDEX SGCMANUT_AP.IXD_MARCA
ON SGCMANUT_AP.TB_MARCA (MARCA ASC);

Exemplo 4. Usando a mesma consulta anterior, agora com índice nas duas tabelas.

O otimizador apresenta a previsão de uso dos dois índices, e percebe-se que o custo fica ainda menor.

Exemplo 5.Consulta com agregação e ordenação.

Neste exemplo, com relação aos custos não há alteração significativa, pois a quantidade de dados nas tabelas envolvidas são poucos, mas pode-se perceber o otimizador fazendo referência ao 'sort by' e 'order by' no resultado.

Concluindo.

O Oracle possui uma gama de recurso relacionados ao EXPLAIN PLAN, a documentação é extensa, para aprofundar no assunto pode iniciar pelo material citado na referência.
Esta introdução tem o objetivo de abrir o tema junto aos interessados. Com um pouco de experiencia e fazendo exercícios com a ferramenta é possível extrair bons resultados, pois a resposta do otimizador é bastante intuitiva para quem possui uma vivência com instruções SQL.

Recomendo que leia o artigo do amigo Eduardo, pois são tratados vários aspectos de grande importância para este tema, que não caberia redundar neste espaço.
link: http://tech.azi.com.br/otimizando-consultas-no-sql-server/
Neste material do Eduardo é tratado sobre o MS SqlServer, e se quiser ver o assunto no PostgreSQL, o link é: http://tech.azi.com.br/planoconsultapostgresql/

Referências:
https://docs.oracle.com/cd/B10501_01/server.920/a96533/ex_plan.htm
https://docs.oracle.com/database/121/ARPLS/d_xplan.htm#ARPLS70125
https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231