Query Tuning: Ferramentas e Práticas para Analisar Planos de Execução no Oracle

Introdução

O post que irei fazer esta semana é sobre algo que é uma busca constante por nós desenvolvedores Oracle e em específico os atuantes com banco de dados e PL/SQL, que é construção de soluções que atendam as demandas dos clientes ou empresa ao ponto que consiga entregar tal solução com uma alta perfomance num mundo com uma quantidade cada dia maior de dados sendo armazenados no banco de dados.

Diante disso, entender como otimizar queries e utilizar recursos para isso tem se tornado um diferencial cada dia maior para todos os desenvolvedores. E para isso o banco de dados Oracle fornece algumas ferramentas como EXPLAIN PLAN e AUTOTRACE.

Query Tuning

Query tuning (ou otimização de consultas) é o processo de ajustar consultas SQL para que sejam executadas de forma mais eficiente, consumindo menos recursos do banco de dados. Entre os inúmeros benefícios os principais incluem:

• Redução de tempo de execução
• Menor consumo de CPU e I/O
• Evita sobrecarga no banco de dados
• Melhoria da escalabilidade do sistema

Explain Plan

O EXPLAIN PLAN mostra como o otimizador do Oracle planeja executar uma consulta. Ele ajuda a identificar operações custosas, como full table scans e nested loops desnecessários.

Antes de usar o EXPLAIN PLAN, é necessário criar uma tabela para armazenar os resultados:

CREATE TABLE plan_table AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Assim é possível gerar o plano de execução para uma consulta específica:

EXPLAIN PLAN FOR 
SELECT first_name, last_name FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

O resultado exibe várias colunas, incluindo:

• Operation: Tipo de operação (Full Table Scan, Index Scan, etc.).
• Cost: Custo relativo da operação.
• Cardinality: Número estimado de linhas retornadas.
• Bytes: Tamanho dos dados processados.

Se um Full Table Scan estiver ocorrendo quando um índice poderia ser usado, pode ser necessário criar um índice ou reescrever a consulta.

Uma outra forma de acessar o EXPLAIN PLAN é no SQL Developer (F6 ) onde é possível obter todo o plano de execução de uma determinada query:

Autotrace

O AUTOTRACE fornece estatísticas sobre a execução real da consulta, além do plano de execução.

Para ativar o AUTOTRACE fazemos o seguinte:

SET AUTOTRACE ON; 

SELECT first_name, last_name FROM employees WHERE department_id = 10;l

Isso gera um relatório com:

1. Plano de execução da consulta.
2. Estatísticas de desempenho, como número de blocos lidos e escrituras.

Para exibir apenas estatísticas:

SET AUTOTRACE TRACEONLY STATISTICS;

Dicas para otimizar consultas

Além de usar EXPLAIN PLAN e AUTOTRACE, algumas práticas ajudam a melhorar o desempenho:

Use índices corretamente

• Evite full table scans desnecessários criando índices para colunas frequentemente filtradas.
• Prefira índices compostos quando múltiplas colunas são usadas em filtros.

Evite funções nas cláusulas WHERE

Esta consulta não usará um índice:

SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

Melhor solução:

SELECT * FROM employees WHERE last_name = 'Smith';

Prefira EXISTS ao invés de IN

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 100);

Melhor solução:

SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE location_id = 100 AND employees.department_id = departments.department_id);

Conclusão

O Query Tuning é essencial para garantir que aplicações funcionem com alto desempenho e sem sobrecarregar o banco de dados. Ferramentas como EXPLAIN PLAN e AUTOTRACE ajudam a entender como as consultas estão sendo processadas e onde podem ser otimizadas. Aplicando boas práticas, como uso eficiente de índices e reescrita de consultas, é possível melhorar significativamente a performance do banco de dados Oracle.

Referências

• Analisando Desempenho de Consultas Utilizando Oracle SQL Developer
• Query Tuning: Ferramentas e Práticas para Analisar Planos de Execução no Oracle