HOWTO (Oracle DB) - Oradebug to trace session

ORADEBUG Session Trace :- 

The following steps are required to trace a user session with oradebug:

Obtain the SPID from v$process

Step 1) 

Identify OS process ID based on database SID

col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;

Enter value for sid: 232
old   4: and a.sid='&sid'
new   4: and a.sid='232'

    SID    SERIAL# USERNAME             OSUSER          SPID
------- ---------- -------------------- --------------- ------------------------
    232      17695 SYSTEM               oracle          3822

Identify database SID based on OS Process ID





col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

SPID = OS ID in UNIX 


Step 2) Start the debug session with the SPID of the process that needs traced
SQL> oradebug setospid 3822 

SQL> oradebug unlimit




·         Select the appropriate trace level. There are four different options when specifying a tracing level.

·         Level 1 – provides “base set” tracing information. Bind variables are displayed as variables (:b1).

·         Level 4 – provides Level 1 data and the actual data values of bind variables.

·         Level 8 – provides Level 1 data and information on wait events when the elapsed time is greater than the CPU time.

·         Level 12 – combines levels 1, 4 and 8 tracing information. A Level 12 trace contains base set, bind variable values and wait events.




Step 3) The oradebug command below will enable the maximum tracing possible:




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




Step 4) Turn tracing off




SQL> oradebug event 10046 trace name context off




Obtain the trace file name. The oradebug facility provides an easy way to obtain the file name




SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/mq1dwhtmp/XXXXXX/trace/XXXXXXX_j000_3822.trc




Format the trace file with tkprof (as described in the earlier section on tkprof)

The result will be a trace file that contains more trace file information. Viewing wait events and bind variable values can be critical to diagnosing performance issues.