Collecting Oracle Extended Trace (10046 event)

By Alexander Bubernak

The most importing thing in collecting trace data is
collecting properly scoped data. You should know the application and user
interaction. Collecting too little data can point you in the wrong direction.
Collecting data in the middle of a performance problem or long wait will give
you a partial trace file missing some important information. The best time to
collect is to turn tracing on, have a user start their process and turn tracing
off after the process has completed.

Collection extended SQL trace

10046 Trace levels

0 – no statistics

1 – basic statistics CURSOR, PARSE, EXEC, FETCH ERROR, SORT
UMAP, ERROR, UMAP, STATS and XCTEND. This is the same as setting sql_trace=true.

2 – same as level 1, do not know why they have this one.

4 – same as level 1 except adds BIND section

8 – same as level 1 except with wait events.

12 – combines all levels 4 and 8.

For timed wait event collecting you must have the parameter timed_statistics=true.
You can do this at the system level or session level. You should also set max_dump_file_size=unlimited
or you may receive the following in the trace file and make it invalid:

WAIT #7: nam=’db file sequential read’ ela= 88 p1=6 p2=37500 p3=1
WAIT #7: nam=’db fil

Tracing a session is very easy if you know what session to

Tracing your own session is simple.

SQL> alter session set timed_statistics=true — turn timing on.
SQL> alter session set max_dump_file_size=unlimited
– set to unlimited otherwise you may get an incomplete
trace file when dump file size limit is reached.
SQL> alter session set tracefile_identifier=’MYSESSION’ – optional to help identify trace files.
SQL> alter session set events ‘10046 trace name context forever, level 8’
– sets extended SQL trace to level 8, see previous section for all trace levels.
******* run all of your processing here *******
SQL> alter session set events ‘10046 trace name context off’ – this will disable tracing.

Tracing another session

This is easy if you know the sid and serial# of the session
you want to trace. This can be more difficult if MTS is used or commonly an
application uses connection pooling where one connection can service many

Find the session:

set linesize 150
column Name format a14
column SID format 9999
column PID format 99999
column TERM format a15
column OSUSER format a15
column Program format a15
column Stats format a10
column Logon_time format a20
select a.username Name,
a.sid SID,
a.serial#, b.spid PID,
substr(a.program,1,10) Program,
a.status Status,
to_char(a.logon_time,’MM/DD/YYYY hh24:mi’) Logon_time
from v$session a,
v$process b
where a.paddr = b.addr
and a.serial# <> ‘1’
and a.status = ‘ACTIVE’
and a.username like upper(‘%&user%’) — if you want to filter by username
order by a.logon_time

Once you have the SID and SERIAL# of the session you can use
some Oracle supplied packages.


SQL> execute sys.dbms_system.set_boo_param_in_session(&&SID, &&SERIAL,’timed_statistics’,true);
— set timed statistics at user session level if not set at system level.
SQL> execute sys.dbms_system.set_in_param_in_session
(&&SID, &&SERIAL, ‘max_dump_file_size’,10000000);
set max dump file size if not set at system level.
SQL> execute sys.dbms_system.set_ev(&&SID, &&SERIAL, 10046, 8, ‘ ‘);
— activate level 8 extended SQL tracing.
******* run all of your processing here *******
SQL> execute sys.dbms_system.set_ev(&&SID, &&SERIAL, 10046, 0, ‘ ‘);
— disables extended SQL tracing.


This package is preferred and fully supported by Oracle but
the above DBMS_SYSTEM works just fine. You will have to install this package
and it is not available on all platforms. To install run the dbmssupp.sql script
as sysdba located in $ORACLE_HOME/rdbms/admin directory.

SQL> execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>false);
— equivalent to level 8 tracing, bind=>true would be equivalent to level 12 tracing.
******* run all of your processing here *******
SQL> execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL); — end tracing.

Logon trigger:

In some applications it may be difficult to start tracing by
changing code or finding a session because of connection pooling. Therefore, a
logon trigger may help. Just enable it as long as needed for a short amount of
time, as this could cause a performance problem in heavily used systems. Note:
The user that creates the trigger must be granted alter session explicitly,
even if the user is system.

Create or replace the trigger set_tracing after logon on to
the database.

if user like ‘USERNAME%’ then
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set events ”10046 trace name context forever, level 8”’;
end if;

Finding your trace files

All user session 10046 tracing will be located in user_dump_dest
or background_dump_dest for background traces. Oracles naming convention is
platform dependant, but should have the spid in it returned from the session
SQL above. An example on Solaris would be {ORACLE_SID}_ora_{spid}.trc
(orcl_ora_1389.trc). You can also use the TRACEFILE_IDENTIFIER before you start
the tracing, to help identify your trace files.

Future articles will include tracing parallel execution.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles