How to use trace event 10046

ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = 'normal_run_with_trace_10046';
ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET events '10046 trace name context forever, level 12';

Your SQL statement(s) here

ALTER SYSTEM SET EVENTS '10046 trace name context off';
EXIT

Alternatively, use the oradebug utility:

select s.sid,s.serial#,p.spid
from v$session s, v$process p 
where sid = 29
and s.paddr = p.addr

Output:

SID	SERIAL#	SPID
29	9	24510662

Check that the process is indeed there:

prodserver1>ps -ef | grep 24510662
  ora11g 24510662        1   0 17:24:42      -  0:00 oraclemagr (LOCAL=NO)
prodserver1>proctree 24510662
  24510662    oraclemagr (LOCAL=NO)

Enable tracing on the operating system process, inside sqlplus:

SQL> oradebug setospid 24051998
Oracle pid: 420, Unix process pid: 24051998, image: oracle@ystu032ma
SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever, level 12

When done, disable tracing:

SYS@magr SQL> oradebug setospid 24051998
Oracle pid: 420, Unix process pid: 24051998, image: oracle@ystu032ma

SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever, level 12

You can now parse the trace file produced through tkprof:

tkprof mytracefile.trc mytracefile.out sys=yes waits=yes sort=exemis

For an extensive list of commands to be used with tkprof, simply type tkprof at the prompt.

An excellent note on 10046 trace event is called "How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)".

Another great note on tracing in general is "Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above) (Doc ID 293661.1)"

- both available on Oracle Supports site.