O Poder do SQL e o Desafio da Performance

Uma das grandes vantagens do SQL é sua natureza declarativa. Isso significa que você não precisa dizer ao banco de dados como obter os dados; basta especificar o que deseja. O otimizador do Oracle (CBO) cuida de encontrar a melhor forma de executar sua consulta.

No entanto, nem sempre o caminho escolhido pelo banco de dados é o mais eficiente. Em consultas complexas ou com grandes volumes de dados, a performance pode ser impactada. Mas como identificar e corrigir esses problemas?

A resposta está no plano de execução.

O Que é um Plano de Execução?

O Execution Plan (Plano de Execução) é um roteiro detalhado que descreve o caminho que o Oracle seguirá para recuperar os dados solicitados em uma consulta SQL. Esse plano é gerado durante a fase de análise (parser) da instrução SQL.
Para construir esse caminho de execução, o Oracle considera diversas informações, entre elas:

• Estatísticas do banco de dados: Incluem system statistics (estatísticas do sistema) e data statistics (estatísticas dos dados), que ajudam a prever a melhor forma de acessar as tabelas e índices.

• Parâmetros da instância do banco de dados: Configurações específicas da instância que influenciam as decisões do otimizador.

Esses fatores fornecem insumos para o CBO (Cost-Based Optimizer), o otimizador do Oracle, que calcula a estratégia mais eficiente para executar a consulta SQL. Dessa forma, o plano de execução representa a melhor estimativa do banco de dados sobre como recuperar as informações da maneira mais rápida e eficiente possível.

Diferença entre Execution Plan e Explain Plan

EXPLAIN PLAN (Planos de Explicação) e EXECUTION PLAN (Plano de execução): Qual a Diferença?

Ao trabalhar com consultas SQL no Oracle, é essencial entender a diferença entre dois tipos de planos: EXPLAIN PLAN e EXECUTION PLAN.

Explain Plan (Planos de Explicação)

O EXPLAIN PLAN é uma previsão do que o Oracle planeja fazer para executar a consulta. Quando você roda um EXPLAIN PLAN, o banco de dados gera um plano estimado de execução, com base nas estatísticas atuais das tabelas e nos algoritmos do otimizador. Isso pode ser útil para entender a estratégia escolhida pelo otimizador antes de realmente executar a consulta.

No entanto, o EXPLAIN PLAN pode estar errado ou ser diferente do plano real de execução, porque:

Ele não executa a consulta de fato, apenas faz uma previsão.

Se as estatísticas do banco de dados estiverem desatualizadas, a previsão pode ser imprecisa.

EXPLAIN PLAN FOR 
SELECT * FROM TABELA_XPTO WHERE coluna = 'valor';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Execution Plan (Plano de execução)

O EXECUTION PLAN, por outro lado, mostra o que realmente aconteceu quando a consulta foi executada. Ele exibe:

A sequência exata de operações realizadas pelo banco de dados.
O tempo gasto em cada operação.
O número real de linhas processadas.
O impacto da E/S (leitura e escrita em disco ou memória).
Se a consulta utilizou índices, joins, ordenações ou full table scans.

SELECT * FROM TABLE(dbms_xplan.display_cursor('<SQL_ID>','<CHILD_NUMBER>','ALLSTATS LAST  +PEEKED_BINDS +PREDICATE +COST +BYTES ADVANCED'));

Ao analisar a rota seguida pelo banco de dados, você pode identificar se ele escolheu o caminho mais eficiente ou se há uma alternativa ainda mais rápida, como a criação de um atalho.

Abaixo está o plano de execução para a junção de duas tabelas:

--------------------------------------
| Id  | Operation          | Name    |
--------------------------------------
|   0 | SELECT STATEMENT   |         |
|   1 |  HASH JOIN         |         |
|   2 |   TABLE ACCESS FULL| TAB_A   |
|   3 |   TABLE ACCESS FULL| TAB_B   |
--------------------------------------


Cada linha no Execution Plan (Plano de Execução) representa uma operação distinta. Essas operações estão organizadas em uma estrutura hierárquica, formando um relacionamento de pai/filho.

O plano pode ser visualizado como uma árvore. A instrução SELECT, localizada no topo, é a raiz; as tabelas, situadas na parte inferior, são as folhas; e, entre elas, há uma série de operações intermediárias.

Essas operações se dividem em três categorias principais:

- Single-child operations (Operações de filho único)
- Multichild operations (Operações de múltiplos filhos)
- Joins (Junções)

Single-child operations (Operações de filho único)

Como o nome sugere, esse tipo de operação sempre possui exatamente uma operação abaixo dela na árvore do plano de execução. Exemplos comuns incluem:

• Grouping (Agrupamento)
• Sorting (Ordenação)

Multichild operations (Operações de múltiplos filhos)

Essas operações podem ter uma ou mais operações abaixo delas e são mais raras. A mais comum que pode aparecer no plano de execução é a operação UNION (ALL), que combina os resultados de múltiplas consultas.

Joins (Junções)

As junções sempre possuem exatamente dois filhos. Esses filhos podem ser:

• Outras junções
• Tabelas
• Qualquer outra operação do plano de execução

Cada um desses componentes desempenha um papel essencial na forma como o banco de dados processa a consulta, influenciando diretamente a eficiência e o desempenho da execução.

Como Ler ou Interpretar um Execution Plan (Plano de Execução)

Em um Execution Plan (Plano de Execução) baseado em texto, a estrutura hierárquica das operações é representada pelo nível de recuo (indentação). Esse recuo indica o relacionamento pai/filho entre as operações.

• Operação pai: É sempre a primeira linha acima de uma operação, com um recuo menor.
• Operações filhas: São as linhas logo abaixo da operação pai, recuadas mais para a direita. Elas pertencem à operação pai até que outra linha com o mesmo nível de recuo apareça.
  

Essa estrutura ajuda a visualizar como as operações se relacionam e a entender a ordem em que o banco de dados executa cada etapa da consulta.

Para ilustrar a estrutura hierárquica de um Execution Plan (Plano de Execução) no Oracle, vamos analisar um exemplo prático. Suponha que temos duas tabelas: clientes e pedidos. Desejamos selecionar todos os clientes e seus respectivos pedidos. A consulta SQL seria:

SELECT c.nome, p.numero_pedido
FROM clientes c
JOIN pedidos p ON c.cliente_id = p.cliente_id;

Ao gerar o plano de execução para essa consulta, obteríamos uma saída semelhante a:

--------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |   100 |  5200 |    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |         |   100 |  5200 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | CLIENTES|   100 |  2600 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | PEDIDOS |   200 |  5200 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Vamos interpretar esse plano de execução, destacando as relações pai/filho:

Operação Raiz (Pai Principal):

Id 0: SELECT STATEMENT é a operação raiz. Todas as outras operações são suas filhas diretas ou indiretas.

Operação Filha da Raiz:

Id 1: HASH JOIN é filha direta do SELECT STATEMENT. Esta operação combina as tabelas CLIENTES e PEDIDOS usando um algoritmo de junção hash.
Operações Filhas do HASH JOIN:

Id 2: TABLE ACCESS FULL na tabela CLIENTES. Esta operação lê toda a tabela CLIENTES e fornece os dados para o HASH JOIN.

Id 3: TABLE ACCESS FULL na tabela PEDIDOS. Similarmente, lê toda a tabela PEDIDOS e passa os dados para o HASH JOIN.

Observações Importantes:
Recuo (Indentação): No plano de execução baseado em texto, o nível de recuo indica a hierarquia das operações. Operações com maior recuo são filhas da operação imediatamente acima com menor recuo.

Relação Pai/Filho:

SELECT STATEMENT é o pai do HASH JOIN.
HASH JOIN é pai das operações TABLE ACCESS FULL das tabelas CLIENTES e PEDIDOS.

Entender essa estrutura hierárquica é crucial para analisar como o Oracle executa a consulta e identificar possíveis otimizações, como a criação de índices ou a reescrita da consulta para melhorar o desempenho.

Passo a Passo do Execution Plan

1 – Primeira operação (Seta Vermelha) – Busca inicial das tabelas COLOURS e TOYS

• A execução começa buscando os dados da tabela COLOURS (TABLE ACCESS FULL, linha 4).
• Em seguida, os dados da tabela TOYS são buscados (TABLE ACCESS FULL, linha 5).
• Esses dados são passados para o primeiro HASH JOIN (linha 3), que combina os resultados dessas tabelas.
  

2 – Segunda operação (Seta Roxa) – Adicionando a tabela PENS

• Após a junção das tabelas COLOURS e TOYS, o banco acessa a tabela PENS (TABLE ACCESS FULL, linha 6).
• O resultado do primeiro HASH JOIN é combinado com os dados da tabela PENS através de outro HASH JOIN (linha 2).
• Neste ponto, temos um novo dataset contendo os dados já filtrados e processados.
  

3 – Terceira operação (Seta Laranja) – Busca na tabela BRICKS

• O próximo passo é buscar os dados da tabela BRICKS (TABLE ACCESS FULL, linha 7).
• O resultado da junção anterior (dataset da etapa 2) é agora unido à tabela BRICKS por meio de um HASH JOIN (linha 1).
  

4 – Última operação (Seta Verde) – Retorno do resultado

• O HASH JOIN final combina todos os resultados e retorna os dados ao cliente através do SELECT STATEMENT (linha 0).
• Como não há mais filhos na estrutura, essa etapa finaliza a execução.
  
  

Resumo da Ordem de Execução

A ordem correta de execução pode ser descrita assim:

1. TABLE ACCESS FULL COLOURS (linha 4) → primeiro conjunto de dados.
2. TABLE ACCESS FULL TOYS (linha 5) → segundo conjunto de dados.
3. HASH JOIN (linha 3) → combinação de COLOURS e TOYS.
4. TABLE ACCESS FULL PENS (linha 6) → terceiro conjunto de dados.
5. HASH JOIN (linha 2) → combinação do resultado do primeiro HASH JOIN com PENS.
6. TABLE ACCESS FULL BRICKS (linha 7) → quarto conjunto de dados.
7. HASH JOIN (linha 1) → combinação do resultado da segunda junção com BRICKS.
8. SELECT STATEMENT (linha 0) → retorna o resultado final ao cliente.

Compreender a estrutura e a ordem de execução de um plano de execução é essencial para diagnosticar e otimizar o desempenho de consultas no Oracle Database. Ao analisar detalhadamente cada operação e suas interações, é possível identificar gargalos e aplicar melhorias específicas, resultando em consultas mais eficientes e sistemas mais responsivos.

Referencias :
https://blogs.oracle.com/connect/post/how-to-read-an-execution-plan