O rastreamento de sessões podem ser feito de várias maneiras em um banco de dados Oracle.
Um dos métodos mais populares é através do parâmetro sql_trace a nível de sessão.
SINTAXE:
alter session set events ‘sql_trace level <trace_level>’
ONDE:
<trace_level> define o nível de rastreamento de uma instrução SQL que pode ser realizada;
Exemplo:
Todos os exemplos abaixo, foram efetuados em sessões conectadas a um banco de dados single-instance utilizando uma conexão dedicada.
1. Habilitando o rastreamento a nível de sessão
SQL> alter session set events ‘sql_trace level 4’;
Session altered.
2. Executar instrução sql
SQL> select 1 from dual;
1
———-
1
SQL>
3. Desligue o rastreamento em nível de sessão
SQL> alter session set events ‘sql_trace off’;
Session altered.
4. Rastreamentos gerados são armazenados no diretório
<diagnostic_dest>\rdbms\<database name>\<instance name>\trace
onde:
4.1 – <diagnostic_dest> é o parâmetro de inicialização DIAGNOSTIC_DEST do banco de dados aonde está definido o diretório de diagnóstico.
Para descobrir a localização de parâmetro DIAGNOSTIC_DEST podemos executar a query abaixo:
SQL> select name, value from v$parameter where name like ‘diagnostic_dest’;
NAME VALUE
————————- ———————————–
diagnostic_dest /u01/app/oracle
4.2 – <database_name> – nome do banco de dados
SQL> select name from v$database;
NAME
————————-
ORCL
Então,os arquivos traces serão gerados no diretório:
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
OBS: O valor em negrito acima, está relacionado com o servidor de banco de dados que estou usando para fazer os testes. Dependendo do local de instalação e do sistema operacional (windows, Linux, etc) do binário do Oracle esse valor será diferente do valor que esta em negrito acima.
Normalmente, com o banco de dados em atividade, são gerados vários arquivos traces nesse diretório e, para identificar o arquivo trace que geramos devemos descobrir o processo de servidor dedicado da sessão rastreada.
Lembre-se, a consulta deve ser feita na mesma sessão em que o trace esta sendo efetuado.
SQL> select spid from v$process
where addr in (select paddr from v$session
where sid=sys_context(‘USERENV’, ‘SID’));
SPID
————
5347
Nome do arquivo de rastreamento é gerado como:
<instance_name>_ora_<process_id>.trc
onde:
<instance_name> – nome de instância
<process_id> – id do processo de servidor dedicado para sessão
então o nome do arquivo é:
orcl_ora_5347.trc
Verificando localmente no servidor oracle (neste caso o servidor é o servidor aonde fiz os testes)
-rw-r—– 1 oracle oinstall 269077 Jun 22 06:13 alert_orcl.log
-rw-r—– 1 oracle oinstall 68 Jun 22 07:00 orcl_j000_6610.trm
-rw-r—– 1 oracle oinstall 968 Jun 22 07:00 orcl_j000_6610.trc
-rw-r—– 1 oracle oinstall 133 Jun 22 08:35 orcl_ora_5347.trm
-rw-r—– 1 oracle oinstall 4624 Jun 22 08:36 orcl_ora_5347.trc
Se executarmos novamente o rastreamento, é preciso lembrar que cada interação irá substituir o conteúdo do arquivo trace.
Para evitar isso, você pode usar o parâmetro tracefile_identifier.
Este parâmetro adicionará a variável do parâmetro tracefile_identifier ao nome do arquivo.
Outros parâmetros que podem ser usados durante o trace:
TIMED_STATISTICS – Especifica se as estatísticas relacionadas ao tempo são coletados ou não. Este parâmetro é verdadeiro quando o parâmetro de inicialização statistic_level está definido como TYPICAL ou ALL
STATISTICS_LEVEL – Especifica o nível de coleta de estatísticas do banco de dados e do sistema operacional. A visão V$STATISTICS_LEVEL pode mostrar o que esse parâmetro controla.
MAX_DUMP_FILE_SIZE – define o tamanho máximo do arquivo trace gerado.
exemplo:
SQL> alter session set tracefile_identifier=’teste_trace’;
Session altered.
SQL> alter session set timed_statistics = true;
Session altered.
SQL> alter session set statistics_level = all;
Session altered.
SQL> alter session set max_dump_file_size = unlimited;
Session altered.
SQL> alter session set events ‘sql_trace level 4’;
Session altered.
SQL> select count(*) from user_tables;
COUNT(*)
———-
987
SQL> select count(*) from dba_tables;
COUNT(*)
———-
2970
SQL> alter session set events ‘sql_trace off’;
Session altered.
5. Encontrar a instrução sql rastreada. Você pode usar a exibição V$SQL
SQL> select sql_text, sql_id from v$sql
where sql_text=’select count(*) from dba_tables’;
SQL_TEXT SQL_ID
———————————- ————-
select count(*) from dba_tables 56bs32ukywdsq
6. Executando mais alguns passos, as informações geradas no arquivo trace são armazenadas para o sql rastreado.
SQL> alter session set tracefile_identifier=’sql_trace_teste’;
Session altered.
SQL> alter session set events ‘sql_trace [sql:56bs32ukywdsq] level 4’;
Session altered.
SQL> select 1 from dual;
1
———-
1
SQL> select count(*) from user_tables;
COUNT(*)
———-
987
SQL> select count(*) from dba_tables;
COUNT(*)
———-
2970
SQL> alter session set events ‘sql_trace off’;
Session altered.
Verificando os arquivos gerados no servidor oracle:
-rw-r—– 1 oracle oinstall 125 Jun 22 10:49 orcl_ora_11951_teste_trace.trm
-rw-r—– 1 oracle oinstall 756 Jun 22 11:00 orcl_mmon_5213.trm
-rw-r—– 1 oracle oinstall 7992 Jun 22 11:00 orcl_mmon_5213.trc
-rw-r—– 1 oracle oinstall 9156 Jun 22 11:00 orcl_ora_11951_teste_trace.trc
-rw-r—– 1 oracle oinstall 83 Jun 22 11:00 orcl_ora_11951_sql_trace_teste.trm
-rw-r—– 1 oracle oinstall 4423 Jun 22 11:00 orcl_ora_11951_sql_trace_teste.trc
Nos próximos post, veremos como rastrear uma sessão usando dbms_trace e também o uso do TKPROF.
Um abraço
ORACLE BLOG 