How to trace session – event sql_trace

In case you want to collect trace statistics you can do it in many ways in Oracle. One of the most popular method is by setting parameter event with value sql_trace on session level.

alter session set events 'sql_trace level <trace_level>'

<trace_level> defines level of SQL tracing that can be performed to retrieve different types/amounts of data:

  • Regular (Level 1 – standard/default level)
  • Level 4 (standard + binds)  – A Level 4 trace provides contains details of the actual contents of all the ‘bind variables’ passed to each SQL statement.
  • Level 8 (standard + waits) – A Level 8 trace provides the default or regular trace information as well as displays a list of all database wait events. (Database wait  events list the reasons if the Elapsed time is greater than the CPU time in the tkprof report. The trace output is used to identify what SQL statement(s) is/are non-performant.
  • Level 12 (standard + binds and waits) – A Level 12 trace provides both binds and waits and are also typically requested for performance issues.

Example

All examples were started in sessions connected to NO-RAC database using dedicated server.

1. Turning on tracing on session level

alter session set events 'sql_trace level 4';

2. Executing traced code

select 1 from dual;

3. Turn off  tracing on session level

alter session set events 'sql_trace off';

Generated traces are stored in directory

<diagnostic_dest>\rdbms\<database name>\<instance name>\trace

where

  • <diagnostic_dest> is parameter DIAGNOSTIC_DEST on database level which defines diagnostic directory.
select name, value from v$parameter
where name like 'diagnostic_dest';

NAME              VALUE             
----------------- --------------------------------
diagnostic_dest   D:\APP\ORACLE
  • <database name> – database name
select name from v$database;

NAME    
---------
ORA11G
  • <instance_name> – instance name where you trace your session
select name, value from v$parameter
where name like 'instance_name';

NAME                VALUE       
------------------- ------------
instance_name       ora11g

so in my case traces are generated to directory

d:\app\oracle\diag\rdbms\ora11g\ora11g\trace\

Usually there are a lot of traces generated in the directory so to identify your trace you need to find out dedicated server process of your traced session. Remember the query must be started in the same session where you were running your tracing.

select spid from v$process 
where addr in (select paddr 
                 from v$session 
                where sid=sys_context('USERENV', 'SID'));

SPID                   
------------------------
5732

Trace name is generated as

<instance_name>_ora_<process_id>.trc

  • <instance_name> – instance name for your session
  • <process_id> – process id of your dedicated server

so in my case trace name is

ora11g_ora_5732.trc

If you rerun tracing you need to remember that each iteration will override content of trace file.

To avoid it you can use parameter TRACEFILE_IDENTIFIER. This parameter add extra postfix to trace file name.

alter session set tracefile_identifier='test1'; 

alter session set events 'sql_trace level 12';

select 1 from dual;

alter session set events 'sql_trace off';

Now trace file name is

ora11g_ora_5732_test1.trc

Another parameters that can be used during tracing

  • timed_statistics – specifies whether or not statistics related to time are collected. This parameter is true when statistic_level is set to TYPICAL or ALL
  • statistics_level – specifies the level of collection for database and operating system statistics. View V$STATISTICS_LEVEL can show you what the parameter controls.
  • max_dump_file_size – defines maximum size of generated trace
alter session set tracefile_identifier='test2'; 

alter session set timed_statistics = true;
alter session set statistics_level = all;
alter session set max_dump_file_size = unlimited;

alter session set events 'sql_trace level 4';

select count(*) from user_tables;

select count(*) from dba_tables;

alter session set events 'sql_trace off';

Another interesting option is to trace dedicated sql using option [sql:sql_id, sql_id …]. It’s fantastic option to make trace really small and to focus only on really important sql statements.

alter session set 
events 'sql_trace [sql:g3yc1js3g2689|7ujay4u33g337] level 4';

1. Find sql to trace. You can use view V$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

2. Run many steps but store in trace only informations dedicated to traced sql

alter session set tracefile_identifier='test3'; 

alter session set events 'sql_trace [sql:56bs32ukywdsq] level 4';

select 1 from dual;

select count(*) from user_tables;

select count(*) from dba_tables;

alter session set events 'sql_trace off';

Have a fun 🙂
Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.