How to enable 10046 Trace (SQL_TRACE) to troubleshoot performance issues

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.

Advertisements

2 thoughts on “How to enable 10046 Trace (SQL_TRACE) to troubleshoot performance issues

  1. Pingback: Oracle TKProf Usage | Oracle Database Internal Mechanism

  2. Pingback: Understanding TKProf output | Oracle Database Internal Mechanism

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.