Activate extended SQL trace for a given session

I was consulted for issue with a session which appeared not to be doing anything. I advised them to enable extended SQL trace since they had already found the session id. Here are the steps for activating extended SQL trace as given in the book, Optimizing Oracle Performance by Cary Milsap:

There are several ways to activate extended SQL tracing for a given session. Two such ways are shown in Example 6-5 and Example 6-6. Oracle encourages you to use the DBMS_SUPPORT package instead of DBMS_SYSTEM if you have a choice (Oracle MetaLink note 62294.1). However, Oracle does not ship dbmssupp.sql and prvtsupp.plb with some software distributions. If you cannot find DBMS_SUPPORT on your system, don't despair. My colleagues and I have used DBMS_SYSTEM.SET_EV in hundreds of performance improvement projects without negative incident. Friends in Oracle Support have informed me that the DBMS_SUPPORT.START_TRACE_IN_SESSION procedure is implemented as a call to SET_EV anyway.

The safety of using START_TRACE_IN_SESSION is that you're not susceptible to typographical errors in specifying event 10046. Accidentally typing the wrong event number could obviously lead to catastrophe.

Example 6-5. Activating extended SQL trace at level 8 with START_TRACE_IN_SESSION for a session identified by :sid and :serial
sys.dbms_support.start_trace_in_session(:sid, :serial,waits=>true, binds=>false)

/* code to be traced executes during this time window */
sys.dbms_support.stop_trace_in_session(:sid, :serial)

Do not use DBMS_SYSTEM.START_SQL_TRACE_IN_SESSION to activate extended SQL trace, because this procedure can activate SQL tracing only at level 1. You cannot activate extended SQL tracing with START_SQL_TRACE_IN_SESSION.

Example 6-6. Activating extended SQL trace at level 8 with SET_EV for a session identified by :sid and :serial
sys.dbms_system.set_ev(:sid, :serial, 10046, 8, '')
/* code to be traced executes during this time window */
sys.dbms_system.set_ev(:sid, :serial, 10046, 0, '')

You may also refer to Metalink Note: 376422.1 which describes recommended methods for getting extended trace:

Subject:	Recommended Method for Obtaining 10046 trace for Tuning
	Doc ID:	Note:376442.1	Type:	FAQ
	Last Revision Date:	19-FEB-2008	Status:	PUBLISHED

PURPOSE

Outline how to effectively gather 10046 trace for use with tuning issues.

SCOPE & APPLICATION

DBAs, Developers and Support personnel

Gathering 10046 trace

Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.
For details of the event see:

Note 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"

For Tuning issues the typical need is to record wait and bind variable infrormation which is achieved using level 12.
The following examples outline how to set the event in various scenarios:

• Session Tracing
• Tracing a process after it has started
• Instance wide tracing
• Initialisation parameter setting
• Tracing sessions via a logon trigger

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

show parameter user_dump_dest
 

Note that 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.
  
  
• 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.
  
  
  1. 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)
     
     
  2. 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
   

  Alternatively you can use the DBMS_SUPPORT package to trace sessions.
  To install the DBMS_SUPPORT package see: Note 377204.1 How to Install the DBMS_SUPPORT Package
  
  To set session tracing using the DBMS_SUPPORT package:

  
  exec DBMS_SUPPORT.START_TRACE_IN_SESSION( &SID, &Serial, waits=>true, binds=>true )
   

  SID is the Oracle Session ID for the session that is to be traced.
  To disable the tracing use:

  
  exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null )
   

• Instance wide tracing
  
  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';
   

• Via a Logon Trigger
  
  There may be some situations where it is necessary to trace the activity of a specific user. In this case a logon trigger could be used.
  An example is provided below:

  
  CREATE OR REPLACE TRIGGER SYS.set_trace
  AFTER LOGON ON DATABASE
  WHEN (USER like  '&USERNAME')
  DECLARE
      lcommand varchar(200);
  BEGIN
      EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
      EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
      EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END set_trace;
  /
   

RELATED DOCUMENTS

Note 41634.1 TKPROF and Problem Solving
Note 214106.1 Using TKProf to compare actual and predicted row counts
Note 32951.1 Tkprof Interpretation
Note 75713.1 Important Customer information about using Numeric Events
Note 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note 258418.1 Getting 10046 Trace for Export and Import:
Note 242374.1 Tracing PX session with a 10046 event or sql_trace
Note 171647.1 Tracing Oracle Applications using Event 10046
Note 1058210.6 How to Enable SQL_TRACE for Another Session Using Oradebug
Note 224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046
Note 377204.1 How to Install the DBMS_SUPPORT Package
Note 62294.1 The DBMS_SUPPORT Package.
Note 62160.1 Tracing Sessions in Oracle