Hungry DBA .com for DBAs who like food

Capturing wait events when tracing a user session 

A common method of tracing user sessions is to use the command: 

BEGIN

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);”;

END;

However this does not include wait events.

To include wait stats you have to set an event as follows (this will start tracing for the relevant session immediately; thus no other command is necessary):

BEGIN

DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,8,'');

END;

This is basically setting event 10046 to level 8.

To turn off tracing you can set it to level 0 as follows:

BEGIN

DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,'');

END;

You can also set the event in your current session using the command:

alter session set events '10046 trace name context forever, level 8';

To turn tracing off again for your current session run:

alter session set events '10046 trace name context off';

Other levels are as follows:


Level 0 = No statistics generated
Level 1 = standard trace output including parsing, executes and fetches plus more.
Level 2 = Same as level 1.
Level 4 = Same as level 1 but includes bind information
Level 8 = Same as level 1 but includes wait's information
Level 12 = Same as level 1 but includes binds and waits

When using tkprof you can get a summary of the waits stats by including the option “waits=yes”; e.g.

tkprof trace_name output_file_name explain=username/password sys=yes waits=yes

This SQL is useful for finding out the name of the trace file for a particular sid:

select c.value || '/' || lower(d.value) || '_ora_' ||
to_char(a.spid, 'fm00000') ||'.trc' "TRACE FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and b.sid = &SID
and c.name = 'user_dump_dest'
and d.name = 'db_name';

Personally I format the trace file 3 times with tkprof to sort them by execute elapsed time; parse elapsed time and fetch elapsed time.  I also make sure the resulting output files have an extension of .wri so when opened up in windows they are associated with wordpad, which makes them easier to read.  For example:

tkprof TRACEFILE OUTFILE_exeela.wri explain=USERNAME/PASSWORD sys=yes waits=yes sort=exeela
tkprof TRACEFILE OUTFILE_prsela.wri explain=USERNAME/PASSWORD sys=yes waits=yes sort=prsela
tkprof TRACEFILE OUTFILE_fchela.wri explain=USERNAME/PASSWORD sys=yes waits=yes sort=fchela