Tracing a Session in Oracle

What is the correct way to trace a session in Oracle
What is the “correct” or recommended way to trace other sessions in Oracle 11g? I was wondering this lately because over the years I’ve seen a few different methods come up. So, this morning when prepping to trace something as part of a test, I got to thinking – I’d rather do it using the most appropriate method, but what is that? I did just a bit of research on the methods that I’m aware of, and here’s what I think is the way to go. I want to make it clear that this isn’t an “official” statement for Oracle, just what I’ve used and what I believe is what we should be using (if it really matters at all.) First, identify the session you want, in my case it is easy since I’m just mucking around:

SQL> select username, status, sid, serial# from v$session
where username ='PRODSUPT' order by 1;

USERNAME STATUS SID SERIAL#
-------- ------ --- -------
PRODSUPT ACTIVE 3   5023

Note, the serial# will change through this example because I need to logout and back in again, to verify I get a new trace file each time.

ORADEBUG

This goes way back to when I first started in 7.3 or 8.0 (and before?) and I’m not going to cover it since Oracle provides many newer methods.

ALTER SESSION SET EVENTS ’10046 TRACE NAME CONTEXT FOREVER,LEVEL 12′;

Then I learned the “alter session” command, which works if you’re tracing your own session. In the case of a DBA though, you are often tracing another session, not yourself. This is worth trying out sometime though.
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, SERIAL#, TRUE);
Next in my memory came the dbms_system procedure. This has been around since I believe 8i or 9i.

exec dbms_system.set_sql_trace_in_session(3,5023,true);
exec dbms_system.set_sql_trace_in_session(3,5023,false);

DBMS_SUPPORT.START_TRACE_IN_SESSION(SID, SERIAL#, WAITS, BINDS);

Next I learned about dbms_support, but the catch here is that it isn’t in the database by default. You have to create it first.

SQL> @?/rdbms/admin/dbmssupp.sql
Package created. Package body created.

exec dbms_support.start_trace_in_session(3,5025,true,true);
exec dbms_support.stop_trace_in_session(3,5025);

DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID, SERIAL_NUM,BINDS,WAITS);

So then comes dbms_monitor, which as best I can tell, is the official way we should be doing this as of 11g (since it is new in 11g). I really don’t know if there’s more to it than the previous methods, but it seems to be the future. I’m guessing the others might be deprecated over time, and this is what we’ll use. Anyone that knows more, please let me know.

SQL> exec dbms_monitor.session_trace_enable(session_id=>3,serial_num=>5027,binds=>true,waits=>true);
PL/SQL procedure successfully completed.

SQL> exec dbms_monitor.session_trace_disable(session_id=>3,serial_num=>5027);
PL/SQL procedure successfully completed.

WITH THAT COVERED
After you’ve done your trace, well – and while you’re doing it, you should be able to find a trace file in your trace directory:
->ll -rt *.trc
-rw-r----- 1 oracle dba 1057 May 24 10:35 T10AC_arc3_9069.trc
-rw-r----- 1 oracle dba 977 May 24 10:39 T10AC_lgwr_8922.trc
-rw-r----- 1 oracle dba 2127 May 24 10:49 T10AC_ora_3029.trc
-rw-r----- 1 oracle dba 65169 May 24 10:50 T10AC_ora_4412.trc

And the beginning of the file should look something like this:
Trace file /oracle/product/diag/rdbms/t10ac/T10AC/trace/T10AC_ora_4412.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /oracle/product/11.2.0.2
System name:    Linux
Node name:      ac-tst-db1.cmc.cable.comcast.com
Release:        2.6.18-128.1.1.el5
Version:        #1 SMP Mon Jan 26 13:58:24 EST 2009
Machine:        x86_64
Instance name: T10AC
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 4412, image: oracle@ac-tst-db1.cmc.cable.comcast.com (TNS V1)

TKPROF

You can now use tkprof to format you trace file into a more readable format. The basic syntax is:
tkprof <source trace file> <output file>
tkprof T10AC_ora_4412.trc T10AC_ora_4412.tkprofs

TRACING AND MAX_DUMP_FILE_SIZE
Today I was tracing a poor query. The database had max_dump_file_size set to prevent overwhelming the system with huge trace files. I needed to increase the size to unlimited to so I could get my trace to complete. When you do this

alter session set max_dump_file_size = unlimited;

you have to set it on the session running the query, not the session that you run the trace from