Definição
O que é um Plano de Execução?
O Plano de Execução é o roteiro que o Oracle cria para executar uma query. Mostra: ordem de acesso às tabelas, tipo de acesso (full scan vs index), método de JOIN (nested loops, hash, merge), operações de sort/aggregate e custo estimado de cada etapa.
Componentes do Plano:
- Operation: Tipo de operação (TABLE ACCESS, INDEX SCAN, HASH JOIN)
- Object: Tabela ou índice sendo acessado
- Cost: Estimativa de recursos (I/O, CPU) necessários
- Cardinality: Número estimado de linhas processadas
- Bytes: Volume de dados movimentados
Tipos de Operações Comuns
TABLE ACCESS FULL
Lê toda a tabela sequencialmente. Rápido para tabelas pequenas ou quando precisa de muitos registros. Problemático em tabelas grandes quando busca poucos registros.
INDEX RANGE SCAN
Usa índice para localizar registros rapidamente. Eficiente para queries seletivas (WHERE coluna = valor específico). Melhor que full scan para poucos registros.
NESTED LOOPS
JOIN onde para cada linha da tabela externa, busca correspondentes na interna. Bom quando tabela externa retorna poucas linhas e há índice na interna.
HASH JOIN
Constrói hash table de uma tabela na memória e faz lookup da outra. Eficiente para JOINs de tabelas grandes, requer memória adequada (PGA).
Aplicação Prática
Gerando Planos de Execução
-- Método 1: EXPLAIN PLAN (não executa a query)
EXPLAIN PLAN FOR
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;
-- Ver o plano gerado
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-- Método 2: AUTOTRACE (executa e mostra estatísticas)
SET AUTOTRACE ON EXPLAIN STATISTICS
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;
SET AUTOTRACE OFF
-- Método 3: DBMS_XPLAN com SQL_ID (plano real executado)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('g2xb5k8f3zp9n'));
-- Ver estatísticas reais de execução (actual rows vs estimate)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => 'g2xb5k8f3zp9n',
format => 'ALLSTATS LAST'
));
-- Ver plano de SQL no AWR (histórico)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('g2xb5k8f3zp9n'));Interpretando um Plano
-- Exemplo de plano problemático
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost | Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 5432 | |
| 1 | NESTED LOOPS | | 10000 | 5432 | 00:01:05 |
|* 2 | TABLE ACCESS FULL | ORDERS | 10000 | 234 | 00:00:05 |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 5198 | 00:01:00 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORDERS"."ORDER_DATE">SYSDATE-30)
3 - filter("ORDERS"."CUSTOMER_ID"="CUSTOMERS"."CUSTOMER_ID")
-- Problemas identificados:
-- 1. TABLE ACCESS FULL em ORDERS (pode ser OK se precisa de muitos registros)
-- 2. NESTED LOOPS com 10000 iterações
-- 3. TABLE ACCESS FULL em CUSTOMERS dentro do loop (10000x full scan!)
-- 4. Falta índice em CUSTOMERS.CUSTOMER_ID
-- Solução: criar índice
CREATE INDEX idx_customers_id ON customers(customer_id);
-- Após criar o índice, novo plano:
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost | Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 256 | |
| 1 | NESTED LOOPS | | 10000 | 256 | 00:00:03|
|* 2 | TABLE ACCESS FULL | ORDERS | 10000 | 234 | 00:00:02|
|* 3 | INDEX UNIQUE SCAN | IDX_CUSTOMERS_ID | 1 | 1 | 00:00:01|
| 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 2 | 00:00:01|
----------------------------------------------------------------------------------
-- Custo reduziu de 5432 para 256 (21x mais rápido!)Usando Hints para Controlar Planos
-- Forçar uso de índice específico
SELECT /*+ INDEX(e emp_salary_idx) */ *
FROM employees e
WHERE salary > 5000;
-- Forçar FULL TABLE SCAN (quando é realmente melhor)
SELECT /*+ FULL(e) */ *
FROM employees e
WHERE department_id = 10;
-- Forçar método de JOIN
SELECT /*+ USE_HASH(e d) */ e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
-- Forçar ordem de JOIN
SELECT /*+ LEADING(d e) */ e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
-- Hint para paralelizar query (DW/batch)
SELECT /*+ PARALLEL(e, 4) */ COUNT(*)
FROM employees e;
-- Ver se hint foi usado
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));Resultado Esperado
Competências a Dominar
Gerar planos
Usar EXPLAIN PLAN, AUTOTRACE e DBMS_XPLAN
Ler planos hierarquicamente
Entender indentação e ordem de operações
Identificar operações custosas
Full scans desnecessários, sorts, nested loops ruins
Comparar estimado vs real
Identificar estatísticas desatualizadas
Usar hints adequadamente
Forçar planos quando necessário
Propor melhorias
Sugerir índices, reescrita SQL, ajustes
Sinais de Alerta em Planos
Exercícios Práticos
Exercício 1: Análise de Plano
Analise este plano e identifique o problema principal e a solução.
Ver Solução
Problema: NESTED LOOPS fazendo full scan na tabela interna a cada iteração. Com 5000 linhas na externa, são 5000 full scans!
Solução: Criar índice na coluna de JOIN da tabela interna ou usar hint USE_HASH para forçar hash join se ambas tabelas são grandes.