Event 10046 is used to gather extended SQL_TRACE information for Oracle sessions. This article describes how to enable event 10046 to get extended information to troubleshoot a performance issue.

To gather 10046 trace at the session level:

alter session set tracefile_identifier='10046'; 
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';

#Once the query is executed, execute below statement to turn off sql tracing

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

Above tracing works when you can manually execute the query in concern, but if you want to enable tracing on a session that is already started or executing a query, then follow below steps:-

Step 1: Identify the session (SPID) to be traced from SID, you can get SID by using the queries mentioned in the post Monitoring Oracle Sessions:

select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/ 

Step 2: Once the SPID is determined, use following commands to enable tracing:

#From SPID
connect / as sysdba
oradebug setospid <spid>;
oradebug unlimit;
oradebug event 10046 trace name context forever,level 12 ;

#From PID
connect / as sysdba
oradebug setorapid <pid>;
oradebug unlimit;
oradebug event 10046 trace name context forever,level 12; 

To disable tracing execute below command:-

oradebug event 10046 trace name context off;

Tracefile should be generate at diagnostic_dest with name as <instance><spid>_<stid>.trc

This was all about session level tracing, but if you want to enable Instance wide tracing, please execute below command:-

#Enable tracing
alter system set events '10046 trace name context forever,level 12';  
#Disable tracing
alter system set events '10046 trace name context off'; 

Trace can also be enabled using SQLT script and DBMS_MONITOR package, in subsequent posts we would explore these methods of enabling trace as well.