Como Otimizar Queries Pesadas no Oracle com Cardinalidade Alta: Guia Completo de EXPLAIN PLAN e Melhores Práticas

 

Quando trabalhamos com milhões de registros no Oracle, é comum enfrentar queries com alta cardinalidade, resultando em lentidão, consumo excessivo de CPU/memória e até ORA-01555: snapshot too old em joins ou subqueries.

O segredo está em analisar corretamente o EXPLAIN PLAN e aplicar técnicas de otimização que reduzam o custo e melhorem o desempenho.

Neste guia, vou mostrar como avaliar o plano de execução no Oracle e quais estratégias aplicar para otimizar queries pesadas.

1. O que é Cardinalidade no Oracle?

Cardinalidade indica a estimativa do número de linhas retornadas por uma operação no plano de execução.

• Alta cardinalidade: muitos registros retornados → mais custo de processamento.

• Baixa cardinalidade: poucos registros retornados → processamento rápido.

🔎 Problema: quando o otimizador estima errado a cardinalidade, ele pode escolher um JOIN ineficiente (ex:), gerando lentidão.

  NESTED LOOP em vez de HASH JOIN

---

2. Avaliando Queries Pesadas com EXPLAIN PLAN

Para analisar:

EXPLAIN PLAN FOR

SELECT o.order_id, c.customer_name, p.product_name

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

JOIN products p ON o.product_id = p.product_id

WHERE o.order_date BETWEEN DATE '2023-01-01' AND DATE '2023-12-31';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 Saída típica:

--------------------------------------------------------------------------------

| Id  | Operation            | Name      | Rows   | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |           |  10M   |   ... | 500000 (20)| 00:02:30 |

|   1 |  HASH JOIN           |           |  10M   |   ... | 400000 (15)| 00:02:10 |

|   2 |   TABLE ACCESS FULL  | ORDERS    |  10M   |   ... | 200000 (10)| 00:01:00 |

|   3 |   TABLE ACCESS FULL  | CUSTOMERS |  1M    |   ... |  50000 (5) | 00:00:30 |

|   4 |   TABLE ACCESS FULL  | PRODUCTS  |  5M    |   ... | 150000 (8) | 00:00:40 |

--------------------------------------------------------------------------------

Aqui vemos:

• 10 milhões de linhas retornadas.

• O otimizador escolheu HASH JOIN (adequado para grandes volumes).

• Mas o TABLE ACCESS FULL em ORDERS CUSTOMERS  PRODUCTS mostra que faltam índices.

3. Estratégias de Otimização

a) Criar Índices Apropriados

Se o filtro principal é o.order_date, crie um índice:

 

 

 

 

CREATE INDEX idx_orders_date ON orders(order_date);

 

Se usamos JOIN frequentes, considere índices compostos:

CREATE INDEX idx_orders_cust_prod ON orders(customer_id, product_id);

b) Estatísticas Atualizadas

 Se o Oracle tem estatísticas desatualizadas, a cardinalidade estimada será incorreta.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'ORDERS');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'CUSTOMERS');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'PRODUCTS');

c) Reescrita de Query

Muitas vezes, subqueries ou views complexas aumentam o custo.
✅ Prefira CTEs materializadas (no Oracle 12c+):

WITH orders_filtered AS (

   SELECT /*+ MATERIALIZE */ *

   FROM orders

   WHERE order_date BETWEEN DATE '2023-01-01' AND DATE '2023-12-31'

)

SELECT o.order_id, c.customer_name, p.product_name

FROM orders_filtered o

JOIN customers c ON o.customer_id = c.customer_id

JOIN products p ON o.product_id = p.product_id;

d) Hints para Forçar Melhor Plano

Se mesmo após índices o otimizador insistir em usar o plano errado:

SELECT /*+ USE_HASH(o c p) PARALLEL(o 4) */ ...

•  USE_HASH força Hash Join.
• PARALLEL paraleliza a leitura da tabela.

4. Checklist para Queries Pesadas no Oracle

• Use EXPLAIN PLAN e DBMS_XPLAN.DISPLAY_CURSOR para checar custos reais.
•  Crie índices nos campos de filtro e joins.
•  Mantenha estatísticas sempre atualizadas.
•  Reescreva queries complexas em CTEs menores.
•  Use hints apenas quando necessário.
•  Teste em ambientes com milhões de registros antes de produção.

O EXPLAIN PLAN no Oracle é a principal ferramenta para entender gargalos em queries com alta cardinalidade.
Seguindo boas práticas como criação de índices, atualização de estatísticas e reescrita de queries, você pode reduzir o custo de execução em até 80% e evitar sobrecarga no banco.

Lembre-se: otimizar queries é medir, ajustar e medir novamente.