Otimizador, Planos e Bind Variables

Conceito

O Oracle compartilha planos de execução entre sessões, na library cache (2. Instance, SGA e Processos). Essa única decisão gera a maior parte do que é específico do tuning Oracle — inclusive o conselho mais repetido do ecossistema: use bind variables.

Hard parse vs soft parse

SQL chega
  ↓
existe na library cache com o MESMO TEXTO (hash)?
  ├─ sim → SOFT PARSE: reusa o plano. Barato.
  └─ não → HARD PARSE: parse, semântica, otimização, geração do plano.
           Caro em CPU + exige latch na shared pool (serializa!)

O custo do hard parse não é só CPU: ele exige latches na library cache. Sob concorrência, isso serializa sessões que nem deveriam se tocar — o sintoma é latch: shared pool ou cursor: pin S wait on X dominando as esperas.

-- ❌ SQL literal: um texto diferente por valor → um hard parse por chamada
SELECT * FROM pedidos WHERE cliente_id = 4711;
SELECT * FROM pedidos WHERE cliente_id = 4712;    -- outro plano na cache!

-- ✅ bind variable: um texto, um plano, reusado
SELECT * FROM pedidos WHERE cliente_id = :id;

Em Go, isso é automático se você usar placeholders (:1, ?) em vez de concatenar — o mesmo hábito que evita SQL injection. Ver 5. Go em Produção.

Há um paliativo, para quando o SQL vem de uma aplicação de terceiros que você não pode alterar:

ALTER SYSTEM SET CURSOR_SHARING = FORCE;  -- o Oracle substitui literais por binds

Funciona, e é um remendo: perde-se precisão de estatística e podem surgir planos piores.

O outro lado: bind peeking e planos instáveis

Se um plano é compartilhado, qual valor foi usado para escolhê-lo? O Oracle "espia" o valor do primeiro bind (bind peeking) e otimiza para ele:

SELECT * FROM pedidos WHERE status = :st;
-- primeira execução com :st = 'cancelado'  (0,1% das linhas) → INDEX RANGE SCAN
-- execuções seguintes com :st = 'concluido' (98% das linhas) → usa o MESMO plano
--   → index scan em 98% da tabela = desastre

Esse é o problema clássico de dados enviesados. As respostas do Oracle:

O CBO e as estatísticas

O otimizador é por custo desde o 10g (o baseado em regras morreu). Custo vem de estatísticas:

-- coleta moderna: deixe o Oracle decidir o tamanho da amostra e os histogramas
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'LOJA',
    tabname          => 'PEDIDOS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,          -- inclui os índices
    degree           => 4
  );
END;
/

Existe um job automático (auto optimizer stats collection) na janela de manutenção. O problema clássico: uma carga em massa insere 50 milhões de linhas de manhã, o job só roda de madrugada, e o dia inteiro o otimizador acha que a tabela tem 1.000 linhas. Depois de carga grande, colete estatística explicitamente.

Ler um plano

-- o plano REAL de uma execução que já aconteceu (não a estimativa)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

| Id | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time  | Buffers |
|  0 | SELECT STATEMENT             |             |      1 |        |     20 |00:00:00.01|      24 |
|  1 |  SORT ORDER BY               |             |      1 |     18 |     20 |00:00:00.01|      24 |
|  2 |   TABLE ACCESS BY INDEX ROWID| PEDIDOS     |      1 |     18 |     20 |00:00:00.01|      24 |
|* 3 |    INDEX RANGE SCAN          | IDX_CLIENTE |      1 |     18 |     20 |00:00:00.01|       4 |

O que olhar:

• E-Rows vs A-Rows (estimado vs real). Divergência grande = estatística ruim. É sempre o primeiro item.
• Starts — quantas vezes a operação rodou. Starts=5000 num acesso interno é 7. Problema N+1 dentro do plano.
• Buffers — blocos lidos; a métrica real de trabalho.
• A-Time por operação — onde o tempo foi.

AWR e ASH: o histórico que outros bancos não têm

-- quem dominou o tempo do banco na última hora (ASH)
SELECT sql_id, event, COUNT(*) AS amostras,
       ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) AS pct
  FROM v$active_session_history
 WHERE sample_time > SYSTIMESTAMP - INTERVAL '60' MINUTE
 GROUP BY sql_id, event ORDER BY amostras DESC FETCH FIRST 15 ROWS ONLY;

Trade-offs

Compartilhar plano economiza CPU e amarra sessões. É a razão de o Oracle aguentar milhares de execuções por segundo do mesmo SQL com custo de parse quase zero — e a razão de um plano ruim se espalhar instantaneamente por toda a aplicação.

SPM dá estabilidade e engessa. Congelar planos elimina a surpresa de "ficou lento sem deploy" e impede o otimizador de aproveitar um índice novo até você aprovar o plano. Em sistema crítico e estável, é o certo; em sistema em evolução, é fricção.

Hints resolvem hoje e cobram amanhã. /*+ INDEX(p idx_cliente) */ corrige um plano agora e continua forçando aquela escolha depois que o dado, o índice e a versão mudaram. Prefira corrigir a causa (estatística, histograma, índice) e reserve hint para emergência — com data de validade anotada.

O trade-off central: o Oracle tem o arsenal de tuning mais completo do mercado, e quase todo problema tem várias soluções possíveis. Isso premia quem sabe diagnosticar (E-Rows vs A-Rows, classe de espera) e pune quem sai aplicando hint — porque no Oracle sempre existe um jeito de forçar o resultado errado com elegância.

Exemplo prático

Encontrar e corrigir SQL sem bind variables

-- SQL "gêmeos": mesmo formato, textos diferentes → literais
SELECT force_matching_signature, COUNT(*) AS variantes,
       SUM(executions) AS execucoes, ROUND(SUM(cpu_time)/1e6) AS cpu_s,
       MIN(sql_text) AS exemplo
  FROM v$sql
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(*) > 20
 ORDER BY variantes DESC FETCH FIRST 10 ROWS ONLY;

Centenas de variantes com a mesma assinatura = a aplicação está concatenando literais. A correção é no código; CURSOR_SHARING = FORCE é a ponte enquanto isso não acontece.

Descobrir por que um SQL ficou lento

-- 1) o SQL tem mais de um plano no cache? (bind peeking / mudança de plano)
SELECT sql_id, plan_hash_value, executions,
       ROUND(elapsed_time/GREATEST(executions,1)/1000, 1) AS ms_por_exec,
       ROUND(buffer_gets/GREATEST(executions,1))          AS blocos_por_exec
  FROM v$sql WHERE sql_id = '&sql_id';

-- 2) qual plano estava em uso quando estava rápido? (histórico AWR)
SELECT snap_id, plan_hash_value, executions_delta,
       ROUND(elapsed_time_delta/GREATEST(executions_delta,1)/1000,1) AS ms
  FROM dba_hist_sqlstat WHERE sql_id = '&sql_id' ORDER BY snap_id DESC;

-- 3) congelar o plano bom, se ele existir
DECLARE n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap => 4100, end_snap => 4120,
                                    basic_filter => q'[sql_id = 'abc123xyz']');
END;
/

Como isso falha em produção

Aprofundamento aplicado

O mesmo SQL pode representar volumes muito diferentes

Um filtro por cliente pode retornar 3 linhas para um cliente e milhões para outro. Reutilizar SQL com binds reduz trabalho de parse, mas o plano ainda precisa lidar com distribuições desiguais. Analise os valores representativos e os cursores envolvidos, sem assumir que todo valor deve usar o mesmo acesso.

Um plano apenas estimado descreve uma previsão. Para diagnosticar uma execução real, procure estatísticas do cursor efetivamente usado, quando coletadas e disponíveis: linhas estimadas versus obtidas, repetições, leituras e memória ajudam a localizar onde a previsão divergiu.

Exercício: execute uma consulta de estudo para dois valores com cardinalidades muito diferentes. Resultado esperado: explique por que uma busca por índice pode favorecer o primeiro e leitura ampla favorecer o segundo; registre o plano de cada execução.

Hints podem estabilizar um experimento, mas não substituem entender a causa. Um hint que melhora o caso pequeno pode prejudicar o grande e envelhecer quando os dados mudam.

Referência: guia oficial de SQL tuning.

Relacionado

• 2. Instance, SGA e Processos
• 3. Redo, Undo e Consistência de Leitura
• 6. PL-SQL na Prática
• 5. Índices e o Planejador
• 4. Índices e o Otimizador
• SQL Tuning
• 7. Diagnóstico e Tuning

---

Parte de MOC Oracle · SQL Tuning Guide (docs)