Performance e Tuning de SQL¶

Definição: Tuning de SQL

Tuning de SQL (ajuste ou afinação) é o conjunto de técnicas para fazer instruções SQL — SELECT, INSERT, UPDATE, DELETE — consumirem menos tempo e menos recursos (CPU, memória, disco). Ele complementa o ajuste do próprio banco, do hardware e da aplicação.

Esta página usa o Oracle como exemplo, porque o livro-base é sobre ele, mas as ideias (estatísticas, plano de execução, tipos de junção, índices) valem para PostgreSQL, SQL Server e MySQL, só mudando os nomes dos comandos. Os fundamentos de índice e EXPLAIN estão em SQL; as particularidades do dialeto em SQL no Oracle.

A cultura da performance¶

Prevenir é mais barato que remediar. Quanto mais tarde um problema de desempenho aparece no ciclo de vida (análise, projeto, desenvolvimento, produção), mais caro e limitado é o conserto. Decisões ruins de modelagem e de consulta no início custam meses depois.
A modelagem é a base: tabelas bem normalizadas e chaves e tipos corretos evitam consultas complicadas (Modelagem de dados); conhecer os recursos do banco evita reinventar em código o que o SQL já faz.
Desenvolvimento e DBA trabalham juntos: quem escreve a consulta conhece o negócio; quem administra conhece estatísticas, memória e estrutura física.
Meça custo x benefício: um relatório mensal de três horas pode não valer o esforço; um processo que roda 10 mil vezes por dia, sim. Considere frequência, volume e crescimento futuro (1.000 notas por dia hoje podem ser 50.000 em dois anos).
Monitoramento contínuo (acompanhar métricas sempre, achar o problema antes do usuário) e reativo (investigar uma reclamação). Tenha um plano com ferramentas e indicadores (Observabilidade).

Como conduzir um ajuste¶

Entenda a queixa: o que está lento, quando, para quem, com quais parâmetros. Reproduza.
Simule em ambiente semelhante à produção (volume e estatísticas parecidos): consulta rápida com 100 linhas pode ser lenta com 100 milhões.
Seja metódico: mude uma coisa por vez, meça antes e depois e registre. Se a solução tem várias melhorias, descubra qual delas resolve.
Valide em produção com cuidado: sempre sobra alguma diferença entre homologação e produção.
Não otimize além do necessário: pare quando atingir o objetivo.

Como o otimizador pensa¶

Definição: otimizador de consultas

Otimizador é o componente do banco que recebe uma instrução SQL (que diz o quê, não como) e decide como executá-la: ordem das tabelas, método de acesso a cada uma, método de junção. Ele gera vários planos de execução possíveis e escolhe o de menor custo estimado.

Etapas simplificadas: (1) avaliar e simplificar expressões e condições; (2) converter para uma forma canônica; (3) escolher a abordagem (modo, estatísticas); (4) gerar planos e escolher o mais barato.

Custo e estatísticas (CBO)¶

No modo baseado em custos (CBO, Cost-Based Optimizer), padrão nas versões atuais, o otimizador estima o custo de cada plano com base em estatísticas dos objetos e nos recursos usados (I/O de disco, CPU e memória). O modo baseado em regras (RBO) é o antigo, que escolhia o caminho por uma tabela fixa de pontuação, sem olhar os dados; está obsoleto e só existe por compatibilidade.

Conceito	O que é
Estatísticas de tabela	Número de linhas, número de blocos, tamanho médio da linha
Estatísticas de coluna	Valores distintos (`num_distinct`), nulos (`num_nulls`), distribuição (histogramas)
Estatísticas de índice	Níveis da árvore, blocos folha, chaves distintas
Seletividade	Fração (0,0 a 1,0) das linhas que um filtro retorna; baixa = filtra muito (bom para índice)
Cardinalidade	Quantidade de linhas: efetiva (selecionadas), de junção (geradas por uma junção) e de valores distintos
Custo	Estimativa dos recursos para executar o plano; menor custo ≠ garantia de menor tempo, mas é a bússola do otimizador

Modos de otimização: ALL_ROWS (menor tempo total, bom para relatórios e lotes), FIRST_ROWS_n (primeiras n linhas mais rápido, bom para telas paginadas) e o modo padrão CHOOSE (usa o CBO se há estatísticas). Controla-se por parâmetro do banco (OPTIMIZER_MODE), da sessão ou por hint em uma consulta.

Transformações que o otimizador faz sozinho¶

Reconhecer isso ajuda a não se preocupar com variações cosméticas:

IN (1,2,3) vira = 1 OR = 2 OR = 3; NOT e BETWEEN são reescritos.
Transitividade: se e.dept = d.dept e d.dept = 50, deduz e.dept = 50 e pode usar o índice de e.
Subexpressões comuns em condições OR podem ser fatoradas.
Fusão de views (view merging): a consulta da view e a instrução externa são juntadas quando possível.
Funções de agregação complexas ou certas subconsultas impedem transformações.

Como a instrução é processada e reaproveitada¶

Além do Parse / Execute / Fetch (SQL no Oracle), há o passo de Bind (ligar valores às variáveis). O Oracle guarda planos na shared pool (library cache): se o texto da instrução é idêntico (mesmos espaços, maiúsculas/minúsculas e objetos de mesmo schema), reaproveita o cursor (soft parse), economizando CPU e memória; senão faz hard parse.

Escreva SQL com variáveis bind (WHERE id = :id), não concatenando valores literais (WHERE id = 10, WHERE id = 11...), que gera milhares de cursores diferentes. Também é defesa contra SQL injection.
Padronize o estilo do texto das consultas (um mesmo método, package ou função compartilhada).
Para inspecionar: views V$SQL, V$SQLAREA, V$LIBRARYCACHE.
Reduza idas e vindas entre aplicação e banco: dezenas de SQLs soltas custam mais que um bloco PL/SQL ou uma operação em lote (PL/SQL: bulk collect).

Índices e desempenho¶

Índices aceleram SELECT, UPDATE e DELETE (que localizam linhas), mas atrasam INSERT e atualizações (cada alteração mantém o índice) e ocupam espaço. Crie com critério.
Seletividade manda: o índice é bom quando o filtro retorna poucas linhas. Quando o filtro devolve uma fatia grande da tabela (regra prática de algo entre 20% e 30% das linhas), ler a tabela inteira (full scan) pode ser mais barato: ler blocos em sequência, de uma vez, supera milhares de acessos aleatórios via índice.
B-tree (padrão), composto (várias colunas; a ordem importa), bitmap (baixa cardinalidade, tabelas grandes de consulta, ruim para escrita concorrente porque o bloqueio é por bloco) e por função (CREATE INDEX ... (UPPER(nome))). Dados reais para decidir: num_distinct e num_nulls de USER_TAB_COL_STATISTICS.
O que desativa o índice: aplicar função na coluna indexada (WHERE TRUNC(data) = ..., WHERE UPPER(nome) = ...), conversões implícitas de tipo, LIKE '%texto' (curinga no início) e comparar com <> costumam ignorar o índice. Reescreva o filtro para deixar a coluna "limpa" (por exemplo, intervalo data >= :ini AND data < :fim + 1).
Índices e estatísticas desatualizados levam o otimizador a erros: mantenha-os atualizados.

Métodos de acesso aos dados¶

Método	Como funciona	Quando aparece
Full table scan	Lê todos os blocos da tabela (em lotes multiblocos)	Tabela pequena, filtro pouco seletivo, sem índice útil
Rowid scan (`TABLE ACCESS BY INDEX ROWID`)	Vai direto à linha pelo `ROWID`, geralmente obtido de um índice	Depois de uma varredura de índice
Index unique scan	Busca um único valor (chave primária ou único)	`WHERE id = :id`
Index range scan	Percorre uma faixa de entradas do índice	`>`, `<`, `BETWEEN`, `LIKE 'abc%'`
Index full / fast full scan	Lê o índice todo (sem tocar na tabela se todas as colunas estão nele)	Consultas "cobertas" pelo índice
Index skip scan	Usa um índice composto mesmo sem filtrar a 1ª coluna (quando ela tem poucos valores)	Coluna inicial com baixa cardinalidade
Bitmap scans (`BITMAP MERGE`, `AND`)	Combina mapas de bits de vários índices	Filtros em várias colunas de baixa cardinalidade
Cluster / hash scan	Acesso a tabelas armazenadas em clusters	Casos especializados
Sample scan	Lê amostra aleatória (`SAMPLE`)	Estatística e análise exploratória

Observe que o FULL TABLE SCAN não é um "erro": é o método certo para lotes grandes. Preocupe-se quando ele aparece numa consulta que deveria devolver poucas linhas de uma tabela grande.

Métodos de junção¶

Quando duas tabelas se relacionam, o otimizador escolhe como combiná-las:

Método	Como funciona	Bom quando
Nested loops (laços aninhados)	Para cada linha da tabela externa (guia), procura as correspondentes na interna, idealmente por índice	Poucas linhas na externa e índice na interna; respostas rápidas (`FIRST_ROWS`); consultas transacionais
Hash join	Monta uma tabela hash em memória com a menor tabela e percorre a maior consultando-a	Grandes volumes, sem índice útil; só serve a junções por igualdade
Sort merge	Ordena as duas fontes e as intercala	Junções por desigualdade (`<`, `>=`) ou fontes já ordenadas; quando falta índice e o volume é grande
Cartesian	Combina toda linha com toda linha (produto cartesiano)	Quase sempre é erro (esqueceu a condição de junção); aceitável com uma tabela de 1 linha

Tipos de junção (independem do método): equijoin (=), non-equijoin (<, BETWEEN), outer join (LEFT, RIGHT, FULL), semijoin (resultado de um EXISTS/IN, sem duplicar linhas) e antijoin (NOT EXISTS/NOT IN). Prefira a sintaxe ANSI (LEFT JOIN ... ON) ao operador antigo (+).

Tabela de condução (driving table) e a ordem das tabelas¶

O Oracle processa uma tabela por vez: a primeira (tabela de condução ou driving table) define quantas linhas alimentam o restante da junção. Os filtros do WHERE são aplicados antes da junção, então a melhor tabela de condução costuma ser a que, depois de filtrada, tem o menor número de linhas.

No CBO a ordem escrita no FROM é irrelevante: o otimizador decide (você pode forçar com hints).
No antigo RBO a ordem importava (tabela mais restritiva por último no FROM).
Ver o plano mudar ao criar ou remover um índice é a melhor forma de entender o efeito da tabela de condução.

Hints: sugestões ao otimizador¶

Definição: hint

Hint é um comentário especial (/*+ ... */) colocado logo depois do SELECT/UPDATE/DELETE que sugere ao otimizador um comportamento para aquela instrução. É uma sugestão, não uma ordem: se estiver mal escrito, o Oracle o ignora sem avisar.

SELECT /*+ INDEX(e emp_name_ix) */ e.last_name FROM employees e WHERE e.last_name LIKE 'A%';
SELECT /*+ LEADING(d e) USE_NL(e) */ e.last_name, d.department_name
  FROM departments d JOIN employees e ON e.department_id = d.department_id;
SELECT /*+ FIRST_ROWS(10) */ * FROM pedidos ORDER BY data DESC;

Hint	Efeito
`ALL_ROWS` / `FIRST_ROWS(n)`	Otimiza para tempo total / para as primeiras n linhas
`INDEX(tab idx)` / `NO_INDEX`	Força / proíbe o uso de índices
`FULL(tab)`	Força full table scan
`ORDERED` / `LEADING(t1 t2)`	Fixa a ordem das tabelas / só a primeira (tabela de condução)
`USE_NL`, `USE_HASH`, `USE_MERGE`	Força o método de junção
`PARALLEL(tab, n)`	Executa em paralelo com grau n (bom em full scan grande; custo de recursos)
`CACHE`	Mantém blocos lidos no cache
`RULE`, `CHOOSE`	Modos antigos (RBO / escolha automática); evite

Considerações: use hints com parcimônia, só depois de checar estatísticas e índices. Um hint "congela" a decisão: quando os dados mudam, o plano forçado pode virar o pior. Documente o motivo no código e revise. Hints de nível de consulta prevalecem sobre o parâmetro da sessão.

Planos de execução: como ler¶

Um plano de execução é a sequência de passos que o otimizador escolheu: ordem das tabelas, método de acesso a cada uma e método de junção. Fica em formato de árvore.

| Id | Operation                       | Name         | Rows | Cost |
|  0 | SELECT STATEMENT                |              |   5  |   4  |
|  1 |  NESTED LOOPS                   |              |   5  |   4  |
|  2 |   TABLE ACCESS FULL             | DEPARTMENTS  |   1  |   2  |
|  3 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEES    |   5  |   2  |
|* 4 |    INDEX RANGE SCAN             | EMP_DEPT_IX  |   5  |   1  |

Regra de leitura: execute primeiro a operação mais indentada (mais interna); entre irmãs no mesmo nível, a de cima vem antes; o resultado sobe para o "pai". Aqui a ordem é 2 → 4 → 3 → 1 → 0: lê DEPARTMENTS por completo e, para cada departamento, usa o índice para achar os empregados.

Como ler o resultado:

Rows (cardinalidade) e Cost são estimativas do otimizador; compare com os números reais.
O asterisco * indica predicados (access = usados para localizar; filter = aplicados depois de ler).
Sinais de alerta: full scan em tabela grande com filtro seletivo; CARTESIAN; estimativa muito diferente da realidade (estatísticas ruins); ordenações grandes (SORT); muitos acessos por ROWID (talvez compense um full scan).
Isole trechos pequenos do plano; não tente decifrar tudo de uma vez.

Como obter o plano¶

Ferramenta	O que mostra
`EXPLAIN PLAN FOR ...` + `DBMS_XPLAN.DISPLAY`	Plano teórico (previsto, sem executar), gravado na `PLAN_TABLE`
`SET AUTOTRACE ON` (SQL*Plus)	Executa e mostra plano + estatísticas (leituras lógicas, físicas, ordenações, idas ao servidor)
`V$SQL_PLAN` + `DBMS_XPLAN.DISPLAY_CURSOR`	Plano real do cursor que já executou (com `GATHER_PLAN_STATISTICS`, mostra linhas reais x estimadas)
SQL Trace + TKPROF	Relatório de tempo por etapa (parse/execute/fetch) de uma sessão
AWR / ASH (licenciados)	Histórico do banco, sessões ativas, SQLs mais caros

EXPLAIN PLAN SET STATEMENT_ID = 'q1' FOR
  SELECT e.last_name, d.department_name
    FROM employees e JOIN departments d ON d.department_id = e.department_id
   WHERE d.location_id = 1700;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'q1', 'TYPICAL'));

-- plano real, com linhas estimadas x reais
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) FROM employees WHERE department_id = 50;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

O EXPLAIN PLAN é ótimo para uma verificação rápida (usa o índice? há full scan?); para diagnosticar um problema real, prefira o plano real e as estatísticas de execução. Em outros bancos: EXPLAIN / EXPLAIN ANALYZE (PostgreSQL, MySQL), plano estimado/real no SQL Server.

Estatísticas e histogramas¶

Definição: estatísticas do otimizador

São dados sobre tabelas, colunas e índices (número de linhas, valores distintos, distribuição) que o otimizador usa para estimar custos. Estatísticas desatualizadas levam a planos ruins, mesmo com consulta e índices corretos.

Use o pacote DBMS_STATS; o antigo ANALYZE não é mais recomendado.
O Oracle tem um job automático de coleta em janela de manutenção. Colete manualmente após cargas grandes ou mudanças de volume.
Cuidado ao regerar: novas estatísticas podem mudar planos de instruções existentes (para melhor ou pior). Teste antes em ambiente semelhante, ou trave estatísticas estáveis em tabelas críticas.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'HR',
    tabname    => 'EMPLOYEES',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',  -- decide onde criar histogramas
    cascade    => TRUE);                        -- inclui os índices
END;
/

Definição: histograma

Histograma é uma estatística que descreve como os valores de uma coluna se distribuem (em faixas/"buckets"). Sem ele, o otimizador assume distribuição uniforme; com dados desbalanceados (por exemplo, 95% dos pedidos com status FINALIZADO), isso leva a escolhas erradas.

Quando criar: colunas muito usadas em filtros e junções e com distribuição muito desigual. Com SIZE AUTO, o Oracle decide com base no uso das colunas registrado. Cuidados: histogramas aumentam o custo de análise do otimizador, especialmente com variáveis bind (o plano pode depender do valor da primeira execução — bind peeking). DBMS_STATS.REPORT_COL_USAGE mostra quais colunas são usadas em filtros.

Roteiro rápido de diagnóstico¶

flowchart TD
    A[Consulta lenta] --> B[Reproduzir com dados parecidos com produção]
    B --> C[Obter plano real e estatísticas de execução]
    C --> D{Estimativa x real<br/>muito diferentes?}
    D -- sim --> E[Atualizar estatísticas / histogramas]
    D -- não --> F{Acesso ou junção<br/>inadequados?}
    F -- full scan em filtro seletivo --> G[Criar ou ajustar índice, remover função da coluna]
    F -- método de junção ruim --> H[Reescrever a consulta, rever índices, hint como último recurso]
    F -- muitas idas ao banco --> I[Operar em lote, reduzir round-trips, bind]
    E --> J[Medir de novo]
    G --> J
    H --> J
    I --> J

Meça (plano real, tempo, leituras). 2. Cheque estatísticas. 3. Cheque índices e filtros sem função na coluna. 4. Reescreva a consulta (menos dados cedo: filtre antes, selecione só as colunas necessárias, evite SELECT *, troque subconsultas correlacionadas por joins ou EXISTS quando fizer sentido — JOIN ou subquery?). 5. Hint só como último recurso.
Valide em ambiente semelhante e monitore depois da mudança.

Para responder em entrevista¶

Pergunta	Ideias para a resposta
"Uma consulta ficou lenta; por onde você começa?"	Reproduzir, olhar o plano real, comparar estimativa x real, checar estatísticas, índices e filtros; mudar uma coisa por vez e medir
"Quando um índice não ajuda?"	Filtro pouco seletivo (retorna muita linha), função na coluna, tabela pequena, estatísticas ruins; índice custa em escrita
"Diferença entre nested loops, hash e sort merge?"	Laço com índice para poucas linhas; hash em memória para volumes grandes por igualdade; sort merge para desigualdade ou fontes ordenadas
"O que são estatísticas e histogramas?"	Dados para o otimizador estimar custo; histograma trata distribuição desigual de valores
"O que é um hint e quando usar?"	Sugestão ao otimizador por instrução; último recurso, documentado e revisado
"O que são bind variables?"	Parâmetros que permitem reaproveitar planos (menos hard parse) e evitam SQL injection