Módulo 4: Performance e Tuning

Introdução

Um banco de dados que funciona é bom, mas um banco de dados rápido é essencial. Otimização de desempenho, ou tuning, é uma das disciplinas mais desafiadoras e valorizadas de um DBA. Vamos aprender a diagnosticar gargalos, otimizar SQL e ajustar componentes internos.

🔍

4.1 Diagnóstico de Performance

Onde começar quando "o sistema está lento"

AWR

Automatic Workload Repository. Coleta estatísticas de desempenho continuamente. Snapshots de hora em hora.

Relatório AWR

Documento HTML detalhado: queries mais "caras", eventos de espera, etc. Habilidade fundamental!

ASH

Active Session History. Amostra atividade a cada segundo. Diagnóstico granular de problemas transitórios.

ADDM

Automatic Database Diagnostic Monitor. Analisa AWR e gera recomendações automáticas.

📊

4.2 Tuning de SQL: Plano de Execução

A "receita" do otimizador

O Plano de Execução é a "receita" que o Otimizador Oracle cria para buscar dados. Descreve os passos: qual tabela primeiro, se usa índice, como faz o join, etc.

Full Table Scan

Oracle lê tabela inteira para encontrar poucas linhas. Sinal de índice faltando ou não usado.

Tipo de Join Inadequado

Oracle pode escolher Nested Loops, Hash Join ou Sort Merge que não é ideal para o volume de dados.

Gerando o Plano de Execução

-- Gerar plano de execução
EXPLAIN PLAN FOR
SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME = 'Smith';

-- Visualizar o plano
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

📚

4.3 Índices: Acelere suas Consultas

O "índice do livro" para seus dados

Um índice é uma estrutura de dados separada que "aponta" para as linhas de uma tabela. Permite encontrar dados rapidamente sem ler a tabela inteira.

✓ Quando Criar Índice

• Colunas frequentemente usadas no WHERE
• Colunas usadas em JOINs
• Colunas com alta seletividade

✗ Quando NÃO Criar

• Tabelas pequenas
• Colunas com muitos valores duplicados
• Tabelas com muito INSERT/UPDATE/DELETE

-- Criar um índice na coluna DEPT_ID
CREATE INDEX idx_emp_dept_id ON EMPREGADOS(DEPT_ID);

🧠

4.4 Gestão de Memória e Tuning Interno

SGA, PGA e AMM

Tuning da SGA

Aumentar Buffer Cache reduz I/O. Aumentar Shared Pool acelera parsing.

Tuning da PGA

Melhora sorts e hash joins, evitando uso de disco (tablespace temporário).

Gestão Automática de Memória (AMM)

A partir do Oracle 11g, defina apenas MEMORY_TARGET e o Oracle gerencia dinamicamente SGA/PGA.

🛠️

4.5 Ferramentas de Diagnóstico

SQL Tuning Advisor e SQL Trace

SQL Tuning Advisor

Analisa query de alta carga e fornece recomendações: criar índice, coletar estatísticas, reescrever query.

SQL Trace e TKPROF

Habilita trace de sessão. TKPROF formata em relatório legível com plano de execução, tempos de CPU, I/O.

📝

Exercícios Práticos

Performance e Tuning

1. Identificando Query Lenta

Gere o plano de execução para: SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME = 'Smith';

2. Criando um Índice

Crie índice para acelerar a query anterior. Gere o plano novamente e compare.

3. Coletando Estatísticas

Execute DBMS_STATS.GATHER_TABLE_STATS para HR.EMPLOYEES.

4. Analisando um Join

Gere plano para query com JOIN entre EMPLOYEES e DEPARTMENTS. Identifique método de join.

5. Relatório AWR (Opcional)

Se tiver Diagnostic Pack, gere um relatório AWR e identifique "SQL ordered by Elapsed Time".

📋 Ver Gabarito dos Exercícios

1. Plano de Execução

EXPLAIN PLAN FOR
SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME = 'Smith';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Esperado: TABLE ACCESS FULL (sem índice)

2. Criando Índice

CREATE INDEX idx_emp_last_name ON HR.EMPLOYEES(LAST_NAME);

-- Gerar plano novamente
-- Esperado: INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID