Uma das ferramentas mais importantes para realizarmos uma análise do desempenho de um banco de dados é o Trace das instruções SQL que estão sendo executadas por um aplicativo.

Neste artigo veremos as instruções básicas para gerarmos este trace no Oracle.

SQL Trace

O SQL Trace gera informações para cada instrução SQL executada por uma determinada instância do Oracle ou gerada por algum usuário específico.

Dentre as informações geradas, podemos destacar:

  • Contadores de parse, fetch e execute.
  • Tempos de CPU e o tempo gasto com a instrução.
  • Leituras físicas e lógicas.
  • Número de linhas processadas.

As informações são armazenadas nos arquivos de trace do Oracle.

TKPROF

Este utilitário formata o resultado gerado nos arquivos de trace. São relacionadas todas as instruções que foram executadas, quais recursos foram empregados, o número vezes que foram executadas e a quantidade de linhas processadas.

De posse destas informações é possível verificar as instruções tem um impacto maior no desempenho.

Geralmente o problema de desempenho está localizado naquelas instruções mais elaboradas que utilizam recursos pesados para o banco de dados como diversos joins, unions, etc. Porém algumas vezes melhorando o desempenho de uma instrução mais simples, entretanto que executa muitas vezes em um determinado procedimento, poderia ser mais significativa na performance do aplicativo.

Gerando o Trace

Os arquivos normalmente são gerados em pastas padrões do Oracle, caso seja necessário gerar estes arquivos em outra pasta utilize o comando ALTER SYSTEM SET USER_DUMP_DEST=novapasta.

Verifique se você possui permissão de acesso para a pasta onde estas informações serão geradas, caso contrário será necessário a um administrador copiar os arquivos antes de executar o comando TKPROF.

Neste artigo comentarei somente a opção de iniciar o Trace a partir da própria sessão com o banco de dados. Para isto utilize a instrução ALTER SESSION SET SQL_TRACE = TRUE.

A geração de informações para o Trace aumenta o consumo de recursos para o sistema, portanto ative no seu aplicativo somente no momento em que for começar a análise dos procedimentos e encerre logo tenha terminado o processo. Para encerrar o Trace execute: ALTER SESSION SET SQL_TRACE = FALSE.

O comando TKPROF possui diversos parâmetros para formatar a saída do Trace, mas simplesmente informando o arquivo de trace e um arquivo de saída já são suficientes para vermos os recursos disponibilizados pelo comando:TKPROF ArquivoEntrada ArquivoSaida

Abaixo um exemplo de uma saída deste utilitário:

SELECT * FROM emp, dept 
WHERE emp.deptno = dept.deptno;

call count cpuelapsed disk query current rows
---- ------- ------- --------- -------- -------- ------- ------
Parse 1 0.16 0.29 3 13 0 0
Execute 10.00 0.00 0 0 0 0
Fetch 1 0.03 0.26 22 4 14 

Misses in library cache during parse: 1 
Parsing user id: (8) SCOTT 

Rows Execution Plan
------- --------------------------------------------------- 

14 MERGE JOIN
 4 SORT JOIN
 4 TABLE ACCESS (FULL) OF 'DEPT'
14 SORT JOIN
14 TABLE ACCESS (FULL) OF 'EMP'

Existem outras formas para gerarmos Trace no Oracle que pretendo abordar em outros artigos, está é uma das mais simples, mas que já nos permite fazer uma análise de dos recursos que estão sendo usados pelas instruções SQL, além de nos mostrar o plano de consulta utilizado.