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.
Pingback: Oracle TKProf Usage | Oracle Database Internal Mechanism
Pingback: Understanding TKProf output | Oracle Database Internal Mechanism