Collecting Oracle Extended Trace (10046 event)

February 15, 2005

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

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
*** DUMP FILE SIZE IS LIMITED TO 10000000 BYTES ***

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

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 users.

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.TERMINAL,1,9) TERM,
        SUBSTR(A.OSUSER,1,9) OSUSER,
	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.

DBMS_SYSTEM:

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.

DBMS_SUPPORT:

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.

begin
 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;
end;

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.