TKPROF – Utilizar ou não? E porque?

TKPROF – Utilizar ou não? E porque?

Olá pessoal, hoje resolvi falar sobre um assunto que desde que ingressei na carreira de DBA sempre foi tido como uma pratica bastante massante e porque não dizer chata, estou me referindo ao TKPROF.

Neste post vou tentar abordar conceitos e práticas de uso, bem como onde e quando devem ser empregados e qual a melhor maneira de gera-los e analisa-los.

Espero que apreciem.

TKPROF

Conceito: é uma das inumeras ferramentas que a Oracle dispõe para auxiliar no trabalho do DBA no seu dia – a – dia, muito usada para diagnosticar possiveis problemas com performancem seja de querys em particular ou até mesmo de processos fechados ( packages e procedures). Através desta ferramenta o DBA pode inclusive encontrar problemas causados por instruções SQL mau parametrizadas ou mau construidas e também analisar problemas de Wait Events.

TKPROF , dificil ou apenas mau interpretado?

Notavelmente , muitos de nós DBA’s, já devemos ter esbarrado com alguém que ache essa ferramenta muito complicada, não só pelo fato de sua saida em forma de arquivo texto , conter inumeras informações que para muitos é de nenhuma relevancia, mais que para alguns DBA’s são de suma importância. Eu mesmo no inicio de minha carreira achei que esse tal de TKPROF era uma verdadeira enrrolação, um programa que gerava inumeras linhas das quais eu não fazia idéia do que se passava.
O que ocorre na verdade é que muitos não analisam com cautela suas linhas de arquivo de saida, deixando muitas vezes passar detalhes preciosos despercebidos, como por exemplo um valor da coluna de PARSES muito alto, um resultado de FETCH muito maior que o de EXECUTE, e por ai vai. São detalhes que podem até não responder aos nossos questionamentos, mais que com certeza nos traram “clarevidência” com relação a que atitude tomar, por onde começar a olhar em nossos ambientes.

Quando devemos usa-lo? (TKPROF)

Bom, podemos avaliar que, o uso desta ferramenta deve ser feito sempre que houverem problemas relacionados a performance de processos em particular, processos esses muitas vezes apontados por usuários finais, ou até mesmo developers experientes. Na maioria das vezes o que chama atenção e nos leva a querer usar essa ferramenta, é quando visivelmente , seja pelo feeling, pela experiencia ou até mesmo por puro chute, não conseguimos identificar onde esta a ponta o iceberg. Iceberg esse que vem na forma de POOR PERFORMANCE, onde um processo em determinada semana rodava em instantes e agora passou a ser em horas, sem que tenha mudado nada em termos de Ambiente ( infra ), e a única maneira de tirarmos a “prova dos 9”, é de fato usando algo que nos dê subsidios para discutirmos seja com o Adm. de Redes, de S.O ou até mesmo o próprio reclamante que em muitos casos é o desenvolvedor do processo.

Analisando o Resultado do TKPROF.

Bem, o que deveriamos analisar primeiro em um arquivo de saido do processo de geração de arquivos de TRACE? ( os arquivos de traces são em geral armazenados nos diretórios UDUMP, BDUMP e CDUMP), vou mostrar uma pequena lista do que merece mais atenção nesse sentido :

1.) Comparar o numero de PARSES com o numero de EXECUTIONS
Neste caso um bom tunning tera um Parse apenas para “n” execuções, o que evitará que seja feito vários re-parsing, o que poderia com certeza degradar a performance.

2.) Procurar por Instruções SQL que não usam variaveis BIND (:Variavel)
É sabido hoje em dia, que quanto mais BIND variables suas instruções SQL conseguirem usar, maiores as chances de haver reaproveitamentos de querys evitando assim todo o processo de parse, execute e fetch, com isso ganhase performance e melhores resultados. Portanto uma vez detectado instruções que não usem variaveis bind , devemos trabalhar no sentido de muda-las.

3.) Identificar os processos que contém altos indices de FULLTABLE SCANS, consumo de CPU excessivo e multiplas leituras á DISCO.
Não se pode levar essas recomendações a ferro e fogo, devemos antes analisar o tipo de Base de Dados que temos, e de acordo com o tipo tolerar algumas caracteristicas e traços encontrados unica e exclusivamente por essas bases, como por exemplo as diferenciações entre um banco OLTP e um DATA WAREHOUSE.

Agora vamos entender algumas particularidades que devem ser preenchidas antes mesmo de gerarmos o nosso tão sonhado TRACE, para futura analise.

a) Verificar o Ambiente

• Devemos habilitar o parametro TIMED_STATISTICS, com isso poderemos ter informações preciosas de consumo de CPU, WAITs e tempos decorridos das execuções.
• A instrução apra colocar este paramtro em funcionamento é:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

b) Verificar o diretório destino dos arquivos de trace ( rastreamento), representados pelo parametro USER_DUMP_DEST encontrado no arquivo de inicialização (init.ora)

• Estes arquivos podem ser gerados indiscriminadamente, ou até que se esgote o espaço em disco disponvel para este diretório, pois constantemente o oracle gera pequenos arquivos de trace relatando algum problema em sua Base de dados, seja de pequena ou de grande magnitude.
• Vejamos como podemos identificar um exemplo do caminho onde são gerados os trace’s:

select value
from v$parameter
where name = 'user_dump_dest';

VALUE
----------------------------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\\UDUMP

 1 linha selecionada.

 Testedb.System>

O Nosso próximo passo é saber como “ligar”, ou dar o start no processo de geração dos arquivos Traces.
Os tarces podem ser definidos até a nivel de sessão.

ALTER SESSION SET SQL_TRACE = TRUE;
DBMS_SESSION.SET_SQL_TRACE(TRUE);

Ainda contamos com a possibilidade d ehabilitar umtrace em uma determinada sessão do Banco de Dados, afim de monitorar um processo de maneira mais eficaz, veja :

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( sid,serial#,TRUE);

Obs.:Acima o comando deve ser passado informando o SID e o SERIAL# da sessão a ser analisada.

Ao longo dos anos e dos tempos, e de acordo com as diversas releases que o Oracle já teve e tem, as nomenclaturas dos arquivos de trace foram variando, como pode ser visto abaixo :

Version :7.3.4
Naming Convention: snnn_pid.trc
Example:s000_4714.trc

Version :8.0.5
Naming Convention:ora_pid_trc
Example:ora_2345.trc

Version :8.1.7
Naming Convention: ora_pid_instance.trc
Example:ora_1373_dbtest.trc

Version :9.x
Naming Convention:instance_ora_pid.trc
Example:dbtest_ora_12345.trc

Com essas informações o DBA pode montar uma query que retornara o nome especifico do arquivo de traces gerado para uma determinada sessão ou para todas. Veja como ficaria :

column username format a10
column trace_file format a70

select b.username,c.value||'\' ||lower(d.value)||'_ora_'||
to_char(a.spid,'fm00000')||'.trc' "Trace_file"
from v$process a,v$session b, v$parameter c,v$parameter d
where a.addr = b.paddr
and    c.name='user_dump_dest'
and    d.name='db_name'
and   b.username is not null;

Saida:

USERNAME                       Trace_file

DISTCUR                        e:\oracle\admin\cdpe\udump\cdpe_ora_02760.trc
DISTARA                        e:\oracle\admin\cdpe\udump\cdpe_ora_01484.trc
DISTSJR                        e:\oracle\admin\cdpe\udump\cdpe_ora_02476.trc
SYSTEM                         e:\oracle\admin\cdpe\udump\cdpe_ora_03888.trc
DIST                           e:\oracle\admin\cdpe\udump\cdpe_ora_02136.trc
DIST                           e:\oracle\admin\cdpe\udump\cdpe_ora_03752.trc
DISTBEL                        e:\oracle\admin\cdpe\udump\cdpe_ora_03280.trc
DISTCUI                        e:\oracle\admin\cdpe\udump\cdpe_ora_02356.trc
DISTPA                         e:\oracle\admin\cdpe\udump\cdpe_ora_03624.trc
DIST                           e:\oracle\admin\cdpe\udump\cdpe_ora_03556.trc
DISTCUI                        e:\oracle\admin\cdpe\udump\cdpe_ora_03508.trc

Como desligar a opção de TRACE.

Já mostramos como utlizar, os pontos importantes e também algumas manhas, agora é hora de sabermos como fazer para desabilitar caso não precisemos mais usar essa feature, ai vai os passos :

ALTER SESSION SET SQL_TRACE = FALSE;
DBMS_SESSION.SET_SQL_TRACE(FALSE);

Caso você queira desabilitar um trace ligado especificamente para uma sessão em particular, execute o seguinte comando :

dbms_system.set_sql_trace_in_session(sid,serial#,false);

Então, como já passamos por diversas caracteristicas do TKPROF e a geração de arquivos Traces, porque não tentarmos automitizar este processo? Uma vez que depedendo de seu ambiente podemos ter várias e várias necessidades de gerações de arquivos de trace, então criaremos uma procedure para que nos auxilie nesse trabalho diário :

Criando Procedure de Geração de Trace

create or replace procedure start_trace
(v_sid    in number,
v_serial# in number,
seconds   in number)

 is
v_user           varchar2(32);
stop_trace_cmd   varchar2(200);
duration         number;
v_spid           number;
dump_dest        varchar2(200);
db_name          varchar2(32);
v_version        varchar2(32);
v_compatible     varchar2(32);
file_name        varchar2(32);
no_session_found exception;

begin
begin
select a.username,
b.spid into v_user,v_spid
from v$session a,
v$process b
where a.sid = v_sid
and   a.serial# = v_serial#
and   a.paddr = b.addr;
exception
when NO_DATA_FOUND then
raise no_session_foubd;

end;

dbms_system.set_sql_trace_in_session(v_sid,v_serial#,true);
dbms_output.put_line('Tracing Started fo User:'||v_user);
dbms_output.put_line('Tracing Start Time:' ||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss'));

if seconds is null then
duration := 60;
else
duration := seconds;
end if;

dbms_lock.sleep(duration);

dbms_system.set_sql_trace_in_session(v_sid,v_serial#,false);
dbms_output.put_line('Tracing Stop Time:' ||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss'));

select value into dump_dest
from v$parameter
where name='user_dump_dest';

select value into db_name
from v$parameter
where name='db_name';

dbms_utility.db_version(v_version,v_compatible);

if substr(v_version,1,1)='9' then
file_name :=db_name ||'_ora_'||v_spid||'.trc';
elsif substr(v_version,1,3)='8.1' then
file_name :='ora_' ||v_spid||'_'||db_name||'.trc';
elsif substr(v_version,1,3)='8.0' then
file_name :='ora_'||v_spid||'.trc';
end if;

dbms_output.put_line('Trace Directory:' ||dump_dest);
dbms_output.put_line('Trace Filename:' ||file_name);

exception
when no_session_found then
dbms_output.put_line('No session found for sid and serial# specified');

 end strat_trace;

Esta procedure mostrará como saida o nome do usuário que deu o start na geração de Trace, o horario de inicio e fim da coleta e geração do trace o diretório onde foi gerado o trace file e também o nome do trace file.

Bem por enquanto é isso, ainda mostrarei como Analisar este arquivo gerado,o significado das colunas existentes no arquivo de Trace File e as opções de comandos do TKPROF.

Até logo, e espero que gostem deste Post.

Abraços á todos.