SQL Tracing Another Session with DBMS_MONITOR

Whtat is SQL Tracing?

SQL Tracing records runtime statistics for SQL and PL/SQL statements, including execution plans, wait events, CPU time, elapsed time, and I/O activity. The output helps DBAs and developers identify performance bottlenecks.

There are various ways to enable SQL tracing either for your own session or for another session or for the entire database. You must be very cautious when enabling for the entire database and must do so only for temporary time window.

In this article, we will see a demo for tracing another session with Session ID using DBMS_MONITOR package.

I have logged in to the SYS user and the HR user in two separate session. 

Below is the HR session detail:

SYS@orcl SQL >SELECT sid, serial#, username, logon_time, status FROM gv$session WHERE username='HR';

       SID    SERIAL# USERNAME     LOGON_TIM STATUS
---------- ---------- ------------ --------- --------
       101      25002 HR           15-SEP-26 INACTIVE

Now, we will enable the SQL tracing for the above session (SID=101 & Serial# = 25002) using DBMS_MONITOR.

SYS@orcl SQL >EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 101, serial_num => 25002, waits => TRUE, binds => TRUE);

PL/SQL procedure successfully completed.

SYS@orcl SQL >

You can confirm if the tracing is enabled from v$session view, using the below query:

SYS@orcl SQL >SELECT username, sid, serial#, status, sql_id, sql_trace, sql_trace_waits, sql_trace_binds FROM v$session where  username='HR';

USERNAME            SID    SERIAL# STATUS   SQL_ID        SQL_TRAC SQL_T SQL_T

------------ ---------- ---------- -------- ------------- -------- ----- -----

HR                  101      25002 INACTIVE               ENABLED  TRUE  TRUE

SYS@orcl SQL >

Now, you can execute few sample queries from the HR user session.

Then, find out the Trace File name and full path location using the below query:

SQL> set lines 200

SQL> col tracefile format A80

SQL> SELECT s.sid, s.serial#, p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr where s.sid=101;

       SID    SERIAL# TRACEFILE

---------- ---------- --------------------------------------------------------------------------------

       101      25002 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6945.trc

SQL>

 Now, disable the tracing from the SYS login using the below procedure:

SYS@orcl SQL >EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 101, serial_num => 25002);

PL/SQL procedure successfully completed.

SYS@orcl SQL >

You can confirm from v$session that the SQL tracing is disabled.

SYS@orcl SQL >SELECT username, sid, serial#, status, sql_id, sql_trace, sql_trace_waits, sql_trace_binds FROM v$session where  username='HR';

USERNAME            SID    SERIAL# STATUS   SQL_ID        SQL_TRAC SQL_T SQL_T

------------ ---------- ---------- -------- ------------- -------- ----- -----

HR                  101      25002 INACTIVE               DISABLED FALSE FALSE

SYS@orcl SQL >

The trace file is not in human-readable format. To read the Trace File, you can use the TKPROF utility to convert the trace file to a readable report format.

Example:

tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6945.trc output2.txt explain=/ as sysdba waits=yes aggregate=yes sys=no

or simply (using all default option):

tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6945.trc output2.txt

You can confirm from v$session that the SQL tracing is disabled.

Below are the list of different other ways to trace own sesion or another session, which were introduced in various oracle releases:

1. ALTER SESSION SET SQL_TRACE=TRUE;

2. DBMS_SESSION.SET_SQLTRACE

3. ALTER SESSION SET EVENT '10046 trace name context forever, level 12';

4. DBMS_SYSTEM.SET_EV

5. ALTER SESSION SET EVENTS 'sql_trace level 12';

6. ALTER SESSION SET EVENTS 'sql_trace [sql;<sql_id_to_trace>] level 12;