Aula 23: Performance Tuning — Explain Plan, AWR e SQL Advisor

Hoje, sábado, 12 de setembro de 2026, damos um passo decisivo na consolidação do conhecimento em Oracle SQL. Esta é a Aula 23 de 24 do nosso curso Oracle SQL — Do Zero ao Avançado, e o foco central é Performance Tuning. Ao longo das aulas anteriores, você aprendeu a escrever consultas complexas, utilizar joins, subqueries, funções analíticas, PL/SQL e a manipular objetos de banco de dados. Agora chegou a hora de tratar um dos temas mais críticos e valorizados no mercado de TI: como fazer uma consulta lenta rodar em milissegundos, como diagnosticar gargalos de maneira científica e como usar as ferramentas nativas do Oracle Database para obter recomendações automatizadas de otimização.

Em ambientes corporativos, a lentidão de uma consulta SQL pode gerar impacto financeiro direto, indisponibilidade de sistemas e sobrecarga de servidores. Profissionais de infraestrutura, segurança da informação e administração de bancos de dados frequentemente precisam atuar em conjunto para identificar se o problema está em hardware, rede, storage, no próprio SQL ou na configuração da instância. A capacidade de gerar e interpretar um plano de execução, analisar relatórios AWR e utilizar o SQL Tuning Advisor separa um desenvolvedor mediano de um especialista em banco de dados Oracle. Na JRT Technology Solutions, nossos especialistas utilizam diariamente essas técnicas em projetos de migração, suporte a ambientes críticos e consultoria de performance, justamente porque elas entregam resultados objetivos e mensuráveis.

Nesta aula avançada, você vai aprender a diagnosticar problemas de desempenho com base em evidências, não em suposições. Vamos explorar três pilares fundamentais do Performance Tuning: o Explain Plan, que revela como o otimizador do Oracle executa uma instrução SQL; o AWR (Automatic Workload Repository), que armazena estatísticas históricas da carga de trabalho e permite identificar padrões de consumo de recursos; e o SQL Tuning Advisor, que analisa consultas problemáticas e sugere correções como novos índices, perfis SQL e ajustes de estatísticas. Cada ferramenta será demonstrada com comandos reais e saídas esperadas, para que você possa reproduzir os procedimentos em seu próprio ambiente.

Os pré-requisitos para acompanhar esta aula incluem acesso a uma instância Oracle Database 19c ou 21c com privilégios de SYSDBA ou, no mínimo, os papéis ADVISOR e SELECT ANY DICTIONARY para as tarefas administrativas. Você também precisará do SQL*Plus ou SQLcl e de um schema com objetos de teste. Ao final desta aula, você será capaz de coletar e interpretar planos de execução, gerar snapshots e relatórios AWR, executar o SQL Tuning Advisor e aplicar as recomendações sugeridas — tudo isso seguindo um passo a passo completo e verificável.

O que você vai aprender nesta aula

• Compreender o papel do otimizador baseado em custo (CBO) e a importância das estatísticas de objetos no Performance Tuning.
• Gerar e interpretar planos de execução com o comando EXPLAIN PLAN e a função DBMS_XPLAN.DISPLAY.
• Identificar operações custosas em um plano de execução, como TABLE ACCESS FULL, NESTED LOOPS e HASH JOIN.
• Configurar, consultar e gerenciar o Automatic Workload Repository (AWR), incluindo a criação manual de snapshots.
• Gerar relatórios AWR em formato HTML e interpretar seções essenciais como Top SQL, Wait Events e Load Profile.
• Utilizar o pacote DBMS_SQLTUNE para criar, executar e obter recomendações do SQL Tuning Advisor.
• Verificar a configuração de todas as ferramentas de Performance Tuning e solucionar os erros mais comuns em ambientes reais.
• Aplicar boas práticas de otimização e documentar resultados de forma profissional.

Pré-requisitos e Ambiente

Antes de iniciar os procedimentos práticos desta aula, é necessário ter uma instância Oracle Database em execução. Em nossos laboratórios na JRT Technology Solutions, padronizamos o uso do Oracle Database 19c Enterprise Edition em sistemas operacionais Linux (Oracle Linux 8 ou Ubuntu 22.04, dependendo do cliente), com o SQL*Plus devidamente configurado e as variáveis de ambiente ORACLE_HOME e ORACLE_SID corretamente definidas. Você pode utilizar qualquer edição do Oracle Database, desde que os pacotes DBMS_XPLAN, DBMS_WORKLOAD_REPOSITORY e DBMS_SQLTUNE estejam disponíveis — o que é padrão nas versões Enterprise, Standard e Personal.

Para executar os comandos administrativos deste tutorial, você precisará de um usuário com privilégios adequados. Recomendamos conectar-se como SYS com a cláusula AS SYSDBA para as tarefas de configuração e concessão de permissões. Para as demonstrações de Performance Tuning, criaremos um usuário chamado PERF_TUNING com uma tabela de exemplo chamada VENDAS. Esse usuário receberá os papéis CONNECT, RESOURCE e ADVISOR, além da permissão SELECT ANY DICTIONARY, necessária para consultar as visões dinâmicas do dicionário de dados usadas pelo AWR e pelo SQL Tuning Advisor.

O primeiro bloco de comandos abaixo realiza a criação do usuário, a conexão como esse usuário, a criação da tabela de exemplo, a inserção de um milhão de linhas de dados sintéticos e a coleta de estatísticas do otimizador. A inserção utiliza a técnica de CONNECT BY LEVEL para gerar registros em massa, o que é útil para simular uma carga de trabalho significativa. Se o seu ambiente não tiver espaço ou desempenho para um milhão de linhas, você pode reduzir o valor para 100.000 sem prejudicar o aprendizado dos conceitos de Performance Tuning.

-- Conexão como SYSDBA para tarefas administrativas
sqlplus / as sysdba

-- Criação do usuário de demonstração para Performance Tuning
CREATE USER perf_tuning IDENTIFIED BY "Senha123"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

-- Concessão de privilégios necessários
GRANT CONNECT, RESOURCE TO perf_tuning;
GRANT ADVISOR TO perf_tuning;
GRANT SELECT ANY DICTIONARY TO perf_tuning;

-- Conectar como o usuário de demonstração
CONNECT perf_tuning/"Senha123"

-- Criação da tabela de exemplo
CREATE TABLE vendas (
    venda_id    NUMBER PRIMARY KEY,
    cliente_id  NUMBER NOT NULL,
    produto_id  NUMBER NOT NULL,
    data_venda  DATE NOT NULL,
    valor       NUMBER(10,2) NOT NULL,
    regiao      VARCHAR2(50)
);

-- Inserção de 1 milhão de linhas de dados sintéticos
INSERT INTO vendas
SELECT level,
       MOD(level, 1000) + 1,
       MOD(level, 500) + 1,
       TRUNC(SYSDATE) - MOD(level, 365),
       ROUND(DBMS_RANDOM.VALUE(10, 5000), 2),
       CASE MOD(level, 4)
           WHEN 0 THEN 'Norte'
           WHEN 1 THEN 'Sul'
           WHEN 2 THEN 'Leste'
           ELSE 'Oeste'
       END
FROM dual
CONNECT BY level <= 1000000;

-- Confirmação da transação
COMMIT;

-- Coleta de estatísticas do otimizador para a tabela VENDAS
EXEC DBMS_STATS.GATHER_TABLE_STATS('PERF_TUNING', 'VENDAS');

SQL> CREATE USER perf_tuning IDENTIFIED BY "Senha123"
  2  DEFAULT TABLESPACE USERS
  3  TEMPORARY TABLESPACE TEMP
  4  QUOTA UNLIMITED ON USERS;

User created.

SQL> GRANT CONNECT, RESOURCE TO perf_tuning;

Grant succeeded.

SQL> GRANT ADVISOR TO perf_tuning;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO perf_tuning;

Grant succeeded.

SQL> CONNECT perf_tuning/"Senha123"
Connected.
SQL> CREATE TABLE vendas (
  2      venda_id    NUMBER PRIMARY KEY,
  3      cliente_id  NUMBER NOT NULL,
  4      produto_id  NUMBER NOT NULL,
  5      data_venda  DATE NOT NULL,
  6      valor       NUMBER(10,2) NOT NULL,
  7      regiao      VARCHAR2(50)
  8  );

Table created.

SQL> INSERT INTO vendas
  2  SELECT level,
  3         MOD(level, 1000) + 1,
  4         MOD(level, 500) + 1,
  5         TRUNC(SYSDATE) - MOD(level, 365),
  6         ROUND(DBMS_RANDOM.VALUE(10, 5000), 2),
  7         CASE MOD(level, 4)
  8             WHEN 0 THEN 'Norte'
  9             WHEN 1 THEN 'Sul'
 10             WHEN 2 THEN 'Leste'
 11             ELSE 'Oeste'
 12         END
 13  FROM dual
 14  CONNECT BY level <= 1000000;

1000000 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('PERF_TUNING', 'VENDAS');

PL/SQL procedure successfully completed.

O bloco acima é a base para todos os exercícios de Performance Tuning desta aula. Com a tabela VENDAS populada e as estatísticas coletadas, o otimizador tem informações suficientes para estimar cardinalidades e custos. Observe que a coleta de estatísticas é um passo essencial; sem ela, o Oracle pode escolher planos de execução ineficientes, comprometendo a análise de performance. A partir deste ponto, qualquer consulta que envolva a tabela VENDAS será avaliada pelo otimizador com base nos metadados estatísticos armazenados no dicionário de dados.

Fundamentos de Performance Tuning no Oracle Database

O Performance Tuning no Oracle Database não é uma atividade isolada, mas sim um processo contínuo e sistemático de diagnóstico, correção e monitoramento. O coração desse processo é o otimizador baseado em custo (Cost-Based Optimizer, CBO), que avalia diferentes caminhos de acesso aos dados — como índices, full table scans, hash joins e nested loops — e escolhe aquele com menor custo estimado. O custo é calculado a partir das estatísticas dos objetos, dos parâmetros de inicialização e das características do hardware, como velocidade de CPU e throughput de I/O. Entender essa lógica é fundamental para interpretar qualquer ferramenta de Performance Tuning.

Quando uma consulta SQL é submetida ao banco de dados, ela passa por várias etapas: parsing, otimização, geração do plano de execução e, finalmente, execução. O plano de execução é o mapa detalhado de como os dados serão acessados, filtrados, ordenados e agregados. O comando EXPLAIN PLAN expõe esse mapa sem executar a instrução, permitindo uma análise prévia e segura. Já o AWR registra informações sobre a execução real de todas as consultas, incluindo tempo de CPU, esperas de I/O, leituras lógicas e físicas, e as armazena em snapshots periódicos. Por fim, o SQL Tuning Advisor cruza essas informações com o histórico de carga e propõe melhorias específicas para consultas com alto consumo de recursos.

Um dos maiores erros em Performance Tuning é focar apenas em sintaxe SQL, ignorando o impacto do design físico do banco de dados. A ausência de índices adequados, estatísticas desatualizadas, falta de particionamento, uso excessivo de hints e até mesmo parâmetros de memória mal dimensionados podem degradar drasticamente o desempenho. Por isso, as ferramentas nativas do Oracle devem ser utilizadas em conjunto: o Explain Plan mostra o que o otimizador planejou; o AWR mostra o que realmente aconteceu durante um período de carga; e o SQL Tuning Advisor executa uma análise aprofundada e automatizada, recomendando a correção mais adequada.

Em nossos projetos na JRT Technology Solutions, adotamos uma metodologia de três etapas para qualquer demanda de otimização: 1) coleta de evidências com AWR e planos de execução; 2) análise detalhada com o SQL Tuning Advisor e, quando necessário, análise manual; 3) implementação controlada das mudanças em um ambiente de homologação, seguida de validação com testes de carga. Essa abordagem evita os riscos de alterar índices ou parâmetros diretamente em produção sem antes comprovar os ganhos esperados.

Nesta aula, você seguirá exatamente esse fluxo de trabalho, mas em um ambiente controlado. Você começará gerando planos de execução para uma consulta de agregação sobre a tabela VENDAS, depois coletará snapshots AWR e gerará relatórios de carga, e por fim usará o SQL Tuning Advisor para obter recomendações automáticas. Cada etapa será detalhada com comandos, saídas e explicações linha a linha, de modo que você possa aplicar o mesmo raciocínio em qualquer cenário real de Performance Tuning.

Entendendo o Explain Plan no Performance Tuning

O Explain Plan é a ferramenta mais básica e essencial para iniciar qualquer análise de Performance Tuning baseada em SQL. Ele mostra, de forma hierárquica, as operações que o otimizador escolheu para executar uma instrução, como acessos a tabelas, uso de índices, joins, agregações e ordenações. O comando que utilizamos é EXPLAIN PLAN FOR seguido da instrução SQL que desejamos analisar. O resultado não executa a consulta; ele apenas gera o plano no objeto PLAN_TABLE ou em uma tabela temporária indicada pelo parâmetro INTO.

A exibição do plano é feita por meio da função DBMS_XPLAN.DISPLAY, que formata a saída de maneira legível, incluindo colunas como Id, Operation, Name, Rows, Bytes, Cost (%CPU) e Time. A coluna Operation descreve o tipo de operação (por exemplo, TABLE ACCESS FULL, INDEX RANGE SCAN, HASH JOIN). A coluna Rows indica a estimativa de linhas retornadas por aquela operação, enquanto Bytes estima o volume de dados trafegado. O Cost é uma unidade relativa calculada pelo otimizador e serve para comparar planos alternativos, não representando diretamente tempo de execução.

Além da interpretação visual, é importante entender o conceito de predicado de acesso e predicado de filtro. O predicado de acesso aparece quando o Oracle utiliza um índice para localizar diretamente as linhas desejadas, como em INDEX RANGE SCAN ou INDEX UNIQUE SCAN. O predicado de filtro aparece quando as linhas são lidas e depois filtradas, o que geralmente indica que o índice não está sendo totalmente aproveitado ou que a tabela está sendo varrida integralmente. A análise minuciosa desses predicados é uma das habilidades mais valorizadas em Performance Tuning.

Na prática, o Explain Plan deve ser gerado para as consultas mais críticas do sistema, especialmente aquelas que aparecem no topo dos relatórios AWR ou que apresentam lentidão percebida pelos usuários. Em conjunto com o comando DBMS_XPLAN.DISPLAY_AWR, que recupera planos históricos de execuções passadas, o DBA pode comparar o plano planejado com o plano realmente executado, identificando mudanças de comportamento do otimizador causadas por alterações de estatísticas ou parâmetros.

A tabela abaixo resume as colunas mais importantes da saída do EXPLAIN PLAN e o significado de cada uma no contexto de Performance Tuning:

Com essa base teórica, você está pronto para gerar e interpretar planos de execução reais. No próximo tópico, montaremos um passo a passo completo, desde a consulta problemática até a leitura detalhada do plano.

Passo a Passo — Gerando e Interpretando o Explain Plan

Vamos agora colocar em prática a geração do Explain Plan para uma consulta típica de relatório gerencial sobre a tabela VENDAS. A consulta que utilizaremos retorna o total de vendas por produto nos últimos 30 dias, ordenado do maior para o menor. Essa consulta envolve filtro por data, agregação GROUP BY e ordenação ORDER BY, sendo um excelente exemplo para demonstrar operações de acesso e processamento.

O procedimento é composto por duas etapas principais: primeiro, executar EXPLAIN PLAN FOR com a instrução SQL; segundo, consultar a função DBMS_XPLAN.DISPLAY para visualizar o plano formatado. Antes de executar, certifique-se de estar conectado como o usuário PERF_TUNING, como demonstrado nos pré-requisitos. Caso contrário, a consulta retornará o erro ORA-00942 ou similar, dependendo dos privilégios.

Execute os seguintes comandos na ordem indicada:

1. Abra o SQL*Plus ou SQLcl e conecte-se como usuário PERF_TUNING.
2. Verifique se a tabela VENDAS existe e se as estatísticas foram coletadas recentemente.
3. Execute o comando EXPLAIN PLAN FOR com a consulta alvo.
4. Consulte o plano com SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);.
5. Analise a saída, prestando atenção às operações de acesso, cardinalidades estimadas e custos.
6. Repita o processo com variações da consulta (por exemplo, adicionando um filtro por região) para comparar os planos.

-- Conectar como usuário de demonstração
sqlplus perf_tuning/"Senha123"

-- Gerar o plano de execução para a consulta de vendas dos últimos 30 dias
EXPLAIN PLAN FOR
SELECT v.produto_id,
       SUM(v.valor) AS total_vendas
FROM vendas v
WHERE v.data_venda >= TRUNC(SYSDATE) - 30
GROUP BY v.produto_id
ORDER BY total_vendas DESC;

-- Exibir o plano de execução formatado
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 4123456789

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   492 | 13776 |   182   (4)| 00:00:01 |
|   1 |  SORT GROUP BY     |        |   492 | 13776 |   182   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| VENDAS | 82192 |  2248K|   180   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("V"."DATA_VENDA">=TRUNC(SYSDATE)-30)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

A interpretação deste plano é direta: a consulta realizou um TABLE ACCESS FULL na tabela VENDAS, varrendo todas as linhas para aplicar o filtro de data. Em um ambiente com milhões de registros, essa operação é extremamente custosa. A coluna Rows indica que o otimizador estimou que 82.192 linhas atendem ao filtro data_venda, com um custo de 180. Como não há índice na coluna data_venda, o Oracle não teve alternativa senão percorrer toda a tabela. Essa é uma informação valiosa para o Performance Tuning, pois sugere a criação de um índice sobre a coluna usada no filtro.

Se criarmos um índice na coluna data_venda, por exemplo, o otimizador provavelmente escolherá um INDEX RANGE SCAN, reduzindo drasticamente o número de blocos lidos e o custo total. O procedimento para criar o índice e gerar novamente o plano de execução é o seguinte:

-- Criação de índice na coluna data_venda para otimizar o filtro por período
CREATE INDEX idx_vendas_data ON vendas(data_venda);

-- Coleta de estatísticas após a criação do índice
EXEC DBMS_STATS.GATHER_TABLE_STATS('PERF_TUNING', 'VENDAS');

-- Gerar novamente o plano de execução
EXPLAIN PLAN FOR
SELECT v.produto_id,
       SUM(v.valor) AS total_vendas
FROM vendas v
WHERE v.data_venda >= TRUNC(SYSDATE) - 30
GROUP BY v.produto_id
ORDER BY total_vendas DESC;

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

Plan hash value: 987654321

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |   492 | 13776 |    39   (5)| 00:00:01 |
|   1 |  SORT GROUP BY               |                  |   492 | 13776 |    39   (5)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| VENDAS           | 82192 |  2248K|    38   (3)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_VENDAS_DATA  | 82192 |       |    15   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("V"."DATA_VENDA">=TRUNC(SYSDATE)-30)

Observe como o plano mudou: a operação TABLE ACCESS FULL foi substituída por um INDEX RANGE SCAN sobre o índice IDX_VENDAS_DATA, seguido de um TABLE ACCESS BY INDEX ROWID para buscar as demais colunas. O custo total caiu de 182 para 39, uma melhoria significativa. Este exemplo ilustra o poder do Explain Plan no Performance Tuning: com poucos comandos, conseguimos visualizar o impacto de um objeto de banco de dados no desempenho de uma consulta.

Performance Tuning com AWR — Automatic Workload Repository

O Automatic Workload Repository