How to Enable SQL_TRACE for Another Session Using ORADEBUG


The ORADEBUG utility can enable/disable setting the SQL tracing for another user's session.  To enable tracing for another session, the Oracle process identifier (PID) or the Operating System processes identifier (SPID) must be identified from v$process.  This is an effective way of capturing a SQL trace from a process which is already running.  The output can be used to analyze SQL related performance issues. 

The ORADEBUG dump produces a trace file in the user_dump_dest that can be formatted with TKPROF.  The ORADEBUG is a utility which is available from Server Manager line mode (svrmgrl).  This utility is available in Oracle versions 7.3 and up. 

Do the following:  

1. Obtain the Oracle process identifier or the Operating System process 
   identifier (SPID) from v$process: 

SVRMGR> select pid, spid, username from v$process; 

               PID    SPID   USERNAME 
               ----   -----  -------- 
               8      25807  oracle 

2. Attach to the process using ORADEBUG. 

   Using the Oracle process identifier: 

       SVRMGR> oradebug setorapid 8 

       Unix process pid: 25807, image: oracleV804 

   - or -  

   Using the Operating System process identifier: 

       SVRMGR> oradebug setospid 25807 

       Oracle pid: 8, Unix process pid: 25807, image: oracleV804 

3. Turn on SQL Trace for the session. 

       SVRMGR> oradebug event 10046 trace name context forever, level 12 

       Statement processed. 

4. Turn off the SQL trace for the session.  

       SVRMGR> oradebug event 10046 trace name context off 

5. Format trace file using TKPROF. 

References:  
===========  

Note 41634.1   TKPROF and Problem Solving

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: