Como Usar o TKPROF para Otimizar a Execução de Relatórios no Oracle

TKPROF

Olá caros amigos.

Neste Post vamos por em prática o TKPROF, vocês o conhecem?

Há alguns dias eu estava com um problema muito sério, o problema era um relatório, este relatório demorava cerca de 30 minutos para executar, a solução era descobrir o que estava demorando tanto para realizar os ajustes necessários.

A rotina que gerava o relatório era uma rotina que chamava cerca de umas 30 funções diferentes. E agora, como descobrir qual era a função que estava deixando o relatório lento?

Simplesmente eu não sabia, precisei pesquisar para descobrir como fazer um “Trace” daquela rotina. Eis que pesquisando encontrei a solução.

A solução foi encontrada em um blog aqui do GPO, o blog do David.

• https://www.profissionaloracle.com.br/blogs/drbs/

Gostaria de agradecer imensamente a toda atenção que o David deu ao meu problema, ele me ajudou com vários e vários emails e a idéia deste post surgiu para contar como eu consegui realizar o TKPROF.

Neste post vamos por a mão na massa, acredito que vocês vão precisar ler conceitos sobre o TKPROF, e para isso indico os links que eu utilizei:

• https://www.profissionaloracle.com.br/blogs/drbs/2009/06/30/tkprof-utilizar-ou-nao-e-porque/
• https://www.profissionaloracle.com.br/blogs/drbs/2009/07/02/tkprof-utilizar-ou-nao-e-porque-parte-ii/
• http://databaseguard.blogspot.com/2009/07/tkprof-utilizar-ou-nao-e-porque-parte.html

Breve Descrição:O TKPROF é apenas um utilitário que converte as informações de um TRACE para “texto”.

O que são traces ? Trace é um log de tudo que acontece no banco. Desde o usuário que o executou, até estatísticas avançadas, este arquivo normalmente fica no servidor.

Agora que sabemos o que é o TKPROF, temos referências sobre o conteúdo, podemos continuar.

Para atingirmos nosso objetivo serão necessários quatro passos:
1º – Habilitar duas seções do Oracle, uma para realizar os testes e outra logada como administrador.
2º – Habilitar o Trace para a seção em questão, auditar o select desejado e desabilitar o trace da seção.
3º – Recuperar o arquivo “.trc” do servidor
4º – Analisar o arquivo “.trc”.

1º Passo: Abrindo seção de teste e administrador.

Primeiro devemos abrir uma seção no nosso banco de teste (imagem acima) e executar a rotina a ser analisada. Nota-se na imagem acima o nome da seção “Banco_de_Teste” e que ela está executando.

Após rodarmos nosso “select alvo” devemos verificar qual o seu “SID” e “Serial” para isso devemos utilizar o select da imagem acima.

select sid,serial#,username,osuser,machine,terminal,status from v$session
where username =;

• Note que você deve pegar o SID e SERIAL da seção que está ativa!

Pronto, a partir de agora sabemos qual é a seção que o seu select está utilizando e podemos auditá-la.

2º Passo: Habilitar trace para a seção em questão.

Para auditar a seção utilize o “Command” do PL/SQL Developer, ou faça via SQL*plus.

Utilize os seguintes comandos:

set verify off
execute sys.dbms_system.set_sql_trace_in_session(“SID”,”SERIAL”,true);

Utilize o “SID” e “SERIAL” da sua seção de teste, passando True(Para habilitar o trace da seção) ou False(Para desabilitar o trace da seção).

Feito isso ele ira começar a auditar sua sessão de testes, e tudo que você executar naquela sessão enquanto estiver com o trace ligado a ela, será impresso em um arquivo TRACE, que depois teremos que coletar do servidor do Banco de dados , no diretório UDUMP (User Dump Dest), e analisar via TKPROF.

3º Passo: Recuperando o arquivo .trc do servidor.

Descobrindo o local de armazenamento do trace.
Utilize o SQL*PLUS e conecte-se com um usuário com permissões de SYS ou SYSTEM, ou ainda com outro usuário com permissões suficientes para poder realizar tarefas de cunho administrativo em seu banco, feito isso prossiga com o seguinte comando:

show parameter user_dump_dest
NAME TYPE VALUE
———————————— ———– ——————————
user_dump_dest string E:\oracle\admin\psal\udump
>

É importante que você saiba a hora que começou a auditar a sua seção, pois no diretório acima haverão vários arquivos traces e você só saberá qual é o seu a partir do horário em que começou a auditoria.
Agora, sugiro que vá ao seu servidor, copie o trace para sua máquina para realizar a análise necessária.

*Para ver a como é uma arquivo trace, acessem o blog do David, pois ele colocou um exemplo muito bacana.
https://www.profissionaloracle.com.br/blogs/drbs/2009/07/02/tkprof-utilizar-ou-nao-e-porque-parte-ii/

Se vocês acessaram o link acima, perceberam que o arquivo trace é um monte de informação, um monte de linhas incompreensíveis e aparentemente sem nexo nenhum. Para obtermos informações úteis do arquivo trace, é necessário que analisemos essas informações utilizando o TKPROF.

4º Passo – Analisando o arquivo trace.

Para utilizar o TKPROF é necessário utilizar o seguinte comando no prompt:

C:\>tkprof [Caminho_Trace] [Arquivotxt] [SQL] [SQL]

Exemplo de como analisar o seu arquivo trace:

C:\>tkprof e:\tkprof\oracle.trc output.txt insert=tkprof.sql Record=allsql.sql

Após executar a análise do seu arquivo trace foram gerados três arquivos

output.txt: Trace analisado propriamente dito, através desse arquivo você conseguirá identificar os problemas da sua rotina.

SQLs: Serão criados 2 arquivos SQL, um script para a criação de uma tabela no seu banco de dados com todos os dados do trace, através dessa tabela ficará mais fácil você obter informações do seu trace.

Agora você já poderá fazer a análise da sua rotina e descobrir o local exato para realizar um pequeno tunning, que na melhor das hipóteses será apenas criar um índice na tabela!

*É recomendável desligar o trace da seção que será auditada após o uso.

É isso aí pessoal, espero ter ajudado, e quando precisarem analisar uma rotina que utiliza “N” selects já sabem o que fazer!

Não deixem de apreciar o blog do nosso amigo David!

Abraços e até a próxima!