How to Perform a Trace with Oradebug on Another Session : A Back-To-Basics Guide

14 Apr How to Perform a Trace with Oradebug on Another Session : A Back-To-Basics Guide

A very common task is investigating other sessions, since the ones causing problems in our database are often not our own, but application sessions. One very effective tool to determine what they are doing is to trace them. Let’s say we identify a session performing a lot of I/O.

DBATEST>SELECT s.sid, s.serial#, s.username, s.program, s.client_info, i.block_changes
         FROM v$session s, v$sess_io i
         WHERE s.sid = i.sid AND username NOT IN ('SYS','SYSTEM')
         ORDER BY 5 DESC, 1, 2, 3, 4;

Example Output:

SID   SERIAL#  USERNAME  PROGRAM                  CLIENT_INFO          BLOCK_CHANGES
----- -------- --------- ------------------------ -------------------- --------------
114   2166     HR        DBATESTplus@localhost    APP DE PRUEBA        1112706
                        (TNS V1-V3)

Step 1: Identify the Session

First, in SQL*Plus, gather information about the session you want to trace:

DBATEST> SELECT '''' || s.sid || ',' || s.serial# || '''' "SID,Serial",
                p.pid "Oracle Process Id (PID)",
                p.spid "OS Oracle Process Id (SPID)",
                s.osuser "OS Client User",
                s.process "OS Client Process Id (Unix PID)",
                s.client_info "Client Info",
                command
         FROM V$SESSION S, V$PROCESS P
         WHERE s.paddr = p.addr(+)
           AND s.USERNAME IS NOT NULL
           AND NVL(s.osuser, 'x') <> 'SYSTEM'
           AND s.type <> 'BACKGROUND'
           AND s.sid IN ('&SID')
         ORDER BY s.process;
Enter value for sid: 1114

Example Output:

SID,Serial    Oracle PID  OS SPID  OS Client User  OS Client PID  Client Info       COMMAND
------------  ----------  -------  --------------- -------------- ------------------ -------
'114,2166'    81          25963    oracle          25961          APP DE PRUEBA      2

Step 2: Start the Trace with Oradebug

Once you’ve located the PID of the session, use oradebug setorapid to begin tracing:

DBATEST> oradebug setorapid 81
Oracle pid: 81, Unix process pid: 25963, image: oracle@localhost (TNS V1-V3)

Step 3: Enable 10046 Trace Level 12

This level captures wait events and bind variables:

DBATEST> oradebug event 10046 trace name context forever, level 12
Statement processed.

Step 4: Locate the Trace File

Use the following to identify the trace file location:

DBATEST> oradebug tracefile_name
/mount/dump01/oracle/DBATEST/diag/rdbms/dbatest/DBATEST/trace/DBATEST_ora_25963.trc

Step 5: Stop the Trace

When you’re done, stop the trace with:

DBATEST> oradebug event 10046 trace name context off
Statement processed.
DBATEST> exit

Step 6: Format the Trace with tkprof


Finally, use tkprof to convert the raw trace file into a readable format and analyze what the session was doing. In this case, the session was waiting on redo log writes.

oracle@localhost [DBATEST] /mount/oracle
oracle $ tkprof /mount/dump01/oracle/DBATEST/diag/rdbms/dbatest/DBATEST/trace/DBATEST_ora_25963.trc prueba.txt sys=no

oracle@localhost [DBATEST] /mount/oracle
oracle $ more prueba.txt

Example Output Snippet from TKPROF:

TKPROF: Release 19.25.0.0.0 - Development on Thu Mar 14 01:23:50 2025

Copyright (c) 1982, 2025, Oracle and/or its affiliates. All rights reserved.

Trace file: /mount/dump01/oracle/DBATEST/diag/rdbms/dbatest/DBATEST/trace/DBATEST_ora_25963.trc
.
.
.
Event waited on                          Times Waited  Max. Wait  Total Waited
---------------------------------------- ------------- ---------- -------------
log file switch completion               10            0.10        1.00
log file switch (checkpoint incomplete)  20            3.06        13.13
db file scattered read                   2             0.01        0.01
reliable message                         28            0.00        0.00
rdbms ipc reply                          28            0.00        0.00

Conclusion

I hope this method for tracing other sessions helps you pinpoint those problematic sessions that sometimes give us headaches.