How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

Gathering 10046 trace

The 10046 trace is the equivalent of setting SQL_TRACE=TRUE. The advantage of using the event is that additional details may be written to the trace file depending on the level specified with the event. This event is often requested to be set by Oracle Support when gathering information for specific SQL issues and particular when investigating and/or diagnosing performance issues. Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.

Trace Location

11g R1 and above:

With the introduction of the new diagnosability infrastructure introduced in Oracle Database 11g Release 1, traces and core files are placed in a location controlled by the DIAGNOSTIC_DEST initialization parameter.
To show the location of the DIAGNOSTIC_DEST, the following command can be used:
SQL> show parameter diagnostic_dest

Pre 11g R1:

Event 10046 tracing will produce a trace file in the <Parameter user_dump_dest> for user processes and <Parameter background_dump_dest> for background processes.
To show the location of the user_dump_dest, the following command can be used:

SQL> show parameter user_dump_dest

Note: Some examples include setting a 'tracefile_identifier' to assist with finding the resultant trace output.

Session Tracing

This tracing can be used where the session is accessible to the user prior to the start of the statement(s) to be traced.
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';

-- Execute the queries or operations to be traced here --

select * from dual;
exit;

If the session is not exited then the trace can be disabled using:

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

Note that if the session is not closed cleanly and tracing is disabled, then important trace information may be missing from the trace file.

Note: "statistics_level=all" is set here so as to gather some level of statistics in cases where the parameter has been changed from the default and recommended level of "TYPICAL" (to BASIC). In order to diagnose performance issues, some level of statistics is required. A value of "ALL" may not be absolutely necessary but has been chosen over "TYPICAL" so as to have the most comprehensive information for diagnosis.

Tracing a process after it has started

If trace from an existing session is required then oradebug can be used to attach to the session and initiate 10046 tracing.
The first step is to identify the session to be traced by some means:

For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/

SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)

If you do not know the Session ID then you can use a select similar to the following to help you identify the target session:

column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
  s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';

Document For multi threaded processes in 12c, use newly added column stid from v$process to find specific thread, as Oracle combines many processes into a single ospid.  To find the specific thread, use following syntax:

oradebug setospid <spid> <stid>

Once the OS process id for the process has been determined then the trace can be initialised as follows:

Lets assume that the process to be traced has an os pid of 9834.
Login to SQL*Plus as a dba and execute the following:
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
Remember to replace the example '9834' value with the actual os pid.

Note that it is also possible to attach to a session via oradebug using the 'setorapid'.

In this case the PID (Oracle Process identifier ) would be used (rather than the 'SPID') and the oradebug text would change to:
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
Remember to replace the example '9834' value with the actual ora pid.

To disable oradebug tracing once tracing is finished:
oradebug event 10046 trace name context off
Document For multi threaded processes in 12c, use newly added column stid from v$process to find specific thread, as Oracle
combines many processes into a single ospid.  To find the specific thread, use following sytax:
oradebug setospid <spid> <stid>oradebug unlimit
The tracefile name will be something like <instance><spid>_<stid>.trc.

Instance wide tracing
Note: Please be cautious when setting system wide, as this will impact performance due to every session being traced.

This setting will trace every session that is created after the parameter is set. Existing sessions will not be traced.

Setting system-wide 10046 tracing can be useful for scenarios where a problem session is known to occur but cannot be identified in advance.
In this situation, tracing can be enabled for a short period of time, the problem can then be reproduced and tracing disabled and the resultant traces searched for evidence of the problem.

System-wide tracing can be enabled as follows:
alter system set events '10046 trace name context forever,level 12';

The setting can be disabled in all sessions by using the following command:
alter system set events '10046 trace name context off';

Initialisation parameter setting

This setting will trace every session in the instance when it is restarted.
event="10046 trace name context forever,level 12"

The setting can be disabled by removing the parameter and restarting the instance or by using an alter system command as follows:
alter system set events '10046 trace name context off'