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.
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
- <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
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> – instance name for your session
- <process_id> – process id of your dedicated server
so in my case trace name is
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
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 🙂