Lima Dba: TKPRO - SQL TRACE 1

DBMS_MONITOR – SQL TRACE
Fontes :
http://felipe.fortaltec.com.br/blog/?p=28
https://sites.google.com/site/telodba/blog/tkprof

O Pacote DBMS_MONITOR nos auxilia através do SQL Trace gerando dados e estatísticas de uma determinada sessão. Estas informações são geradas para cada instrução DML executada dentro da sessão auditada.
Dentre as informações geradas, podemos citar:

* CPU: Tempo de CPU e Average;
* Tempo Decorrido: Tempo gasto com a execução da instrução SQL;
* I/O: Número de leituras físicas de blocos no disco;
* Consultas: Número de buffers obtidos na leitura;
* Processamento: Quantidade de linhas processadas em cada chamada;
* Contadores PFE: Parse, Fetch, Execute;

Todas estas informações ficam armazenadas em um diretório (USER_DUMP_DEST) através de um arquivo de trace (TRC).

- TKPROF

Como dito anteriormente, todos os arquivos de trace são armazenados em uma pasta do sistema operacional (USER_DUMP_DEST) em um arquivo de extensão trc. Este arquivo por si só não possui as informações que queremos (instruções executadas, tempo decorrido, quantidade de execuções, etc…). Para transformarmos este arquivo trc em um arquivo de texto legível, utilizamos o TKPROF. Em termos gerais, este utilitário quem vem dentro da pasta de binários do Oracle é o responsável por transformar o arquivo de trace em um arquivo de texto compreensível.

Exemplo de utilização:
$> TKPROF /pasta_user_dump_dest/arquivo.trc /pasta_destino/arquivo.txt

OBS: checar se há traces habilitados na view DBA_ENABLED_TRACES que é baseada na tabela underlying data dictionary SYS.WRI$_TRACING_ENABLED

select * from sys.wri$_tracing_enabled;

ou SELECT *FROM DBA_ENABLED_TRACES;

- HABILITANDO O TRACE NA SESSÃO

Normalmente, os arquivos de trace são gerados em diretórios específicos do oracle. Neste caso, o trace sempre ficará armazenado em um diretório chamado USER_DUMP_DEST.
Para verificar o destino deste diretório basta executar no SQL o seguinte comando:

SHOW PARAMETER USER_DUMP_DEST

* Caso precise alterar o destino deste diretório, execute o seguinte comando:
ALTER SYSTEM SET USER_DUMP_DEST = 'caminho/da/nova/pasta';

ou alterar somente o destino da sessão:

(WINDOWS)

alter session set user_dump_dest="c:\nome_do_diretorio";

alter session set 
tracefile_identifier ="nome_do_trace";

* Quando alterar a pasta se certifique que o usuário oracle tenha permissão de leitura/escrita nela.

Bem, inicialmente você precisará saber qual sessão auditar. Neste caso, iremos realizar uma consulta na visão do dicionário de dados chamada V$SESSION (Nesta visão, podemos ter um apanhado geral sobre todas as sessões que estão abertas no banco).
Filtrar pelo usuário do banco (Username), Usuário do S.O. (OSUSER), Máquina (MACHINE) ou Programa (PROGRAM):

alter session set nls_date_format='dd/mm/yyyy hh24:mi';
set linesize 2000
set pagesize 1000
SELECT * from v$session where program <> 'plsqldev.exe'
ou para identificar, usar a V$SESSION

select sid, serial#, username 
from v$session;

   SID     SERIAL#  USERNAME
     ------    -------  ------------
       133       4152  SYS
       136       7854  SCOTT
       139         53  KIMBERLY
       140        561  DBSNMP
       141          4  DBSNMP

Agora de posse do SID (Session ID) e o serial# da sessão, habilitar o trace:

exec DBMS_MONITOR.session_trace_enable (session_id => 136,serial_num => 7854); -- 136 é o SID e 7854 é o SERIAL# da sessão auditada

Habilitando o trace pelo cliente:

SQL> exec dbms_monitor.client_id_trace_enable
(client_id=>'nome_cliente');

Trace se encontra habilitado e tudo que for feito nesta sessão será escrito no arquivo trc.
Aguardar o usuário fazer todos os SELECTS, UPDATES, DELETES…

- DESABILITANDO O TRACE E GERANDO ARQUIVO DE RESULTADOS

Após realizar todas as operações de DML, executar o seguinte script para desativar o trace:

exec DBMS_MONITOR.session_trace_disable (session_id => 136,serial_num => 7854); -- 136 é o SID e 7854 é o SERIAL# da sessão auditada

Após executar o comando acima, o arquivo TRC será gerado na pasta USER_DUMP_DEST.
A única coisa que você deve fazer agora é utilizar o TKPROF para gerar o arquivo de resultados (txt). 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 /app/oracle/admin/udump/msid_146632.trc /home/oracle/saida_trace.txt

Pronto! Basta agora obter o arquivo gerado (txt) e analisar os resultados!

Segue abaixo uma visão geral de tudo que devemos fazer (passo a passo):
PgSQL
-- Ver sessões abertas (Filtrar de acordo com necessidade)
select * from v$session where program <> 'plsqldev.exe' and username = 'GESTOR_MASTER'

-- Para ativar o Trace (Especificar SID e SERIAL#)
exec DBMS_MONITOR.session_trace_enable (session_id => 136,serial_num => 7854);

-- Após operações de DML, será necessário desativar o trace. --
Para desativar o Trace (Especificar SID e SERIAL#)
exec DBMS_MONITOR.session_trace_disable (session_id => 136,serial_num => 7854);

-- Para ver onde o arquivo de trace (TRC) será armazenado:
SHOW PARAMETER USER_DUMP_DEST; --

Para gerar o arquivo de texto contendo as operações auditadas -- Executar no SHELL: --
tkprof /caminho/arquivo.trc /caminho/arquivo_destino.txt

TKPROF

posted Oct 27, 2010, 9:09 AM by Telo Vieira [ updated Oct 29, 2010, 4:44 AM ]

É uma ferramenta que converte os arquivos de trace do Oracle num formato de leitura mais compreensível.
O parâmetro TIMED_STATISTICS deve ser configurado para TRUE.
Não vou entrar em detalhes sobre como gerar o TKPROF, mas sim, como interpretar seu resultado. Mesmo assim uma pequena parte dele, pois o assunto é extenso.
O resultado do TKPROF contém algumas informações de performance consolidadas para todos os SQLs analisados.
Vou destacar as descritas abaixo:
********************************************************************************
count    = número de vezes que o SQL foi executado
cpu      = tempo de CPU utilizado em segundos
elapsed = tempo decorrido (elapsed time) em segundos. Inclui CPU Time + Wait Time
disk     = número de data blocks(1) físicos lidos do disco (datafiles)
query    = número de buffers(2) (blocks) CR recuperados (Consistent Read). Usualmente são recuperados em queries
current = número de buffers (blocks) CURRENT recuperados (Current Mode). Geralmente para updates,inserts e deletes
rows     = número de linhas (rows) processadas pelo fetch ou execute call
********************************************************************************
Veja um exemplo:
select a.owner,b.object_name,b.status from dba_tables a, dba_objects b
where a.owner='SYSTEM' and
a.table_name = b.object_name and a.owner = b.owner

call	count	cpu	elapsed	disk	query	current	rows
Parse	1	1.45	1.68	0	6	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	2	0.22	0.26	1	1868	0	191
total	4	1.67	1.95	1	1874	0	191

Aproveito para explicar o que é PARSE, EXECUTE e FETCH, visto que essa pergunta já me foi feita algumas vezes.
PARSE : Verifica a sintaxe do SQL, segurança/existência dos objetos envolvidos e o traduz para um plano de execução;
EXECUTE : A real execução do SQL pelo Oracle. Modifica dos dados para INSERT, DELETE E UPDATE. Identifica as linhas (rows) selecionadas pelo SELECT;
FETCH : Recupera as linhas (rows) retornadas pela query. É utilizado somente para SELECTS; Observamos, no exemplo acima, que para um determinado SQL:
a) Feito o PARSE 1 vez com tempo de duração de 1.68 segundos (CPU time + 0.23 segundos);
b) Foi executado 1 vez com tempo de CPU de 0,00 segundos e elapsed time de 0.00 segundos;
c) As 191 linhas foram recuperadas em 0.26 segundos (CPU time + 0.04 segundos);
d) Foram lidos 1 buffer físico do disco, 1868 CR e 0 CURRENTs
e) #blocks in the table: #blocks = query mode / # of times the query was executed
f) Cada chamada do fetch recuperou aproximadamente 95 linhas (rows/count);
g) Um fetch no database realiza no mínimo um LIO (Logical I/O) para fazer o PIN do Oracle block no database buffer cache;

(1) data block é a menor unidade de dados usada pelo oracle database (DB_BLOCK_SIZE)
(2) buffer é onde os blocos são colocados após a leitura deles no disco.

Prática:

Parte 1