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;
-------- ------ --- -------

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.


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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/
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)


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.



11 responses to “What is the correct way to trace a session in Oracle

  1. This is excellent Article. Wow man. I was totally confused with all these different levels of Tracing. Any time I used to google search, they used to give us different methods. Every person had a different method to do it.
    You have clearly explained it. Gives me a lot better understanding.

  2. Hello from across the sea! This is just what I was looking for, and you wrote it nicely. Thx

  3. Absorbing info and interestingly written. Keep up the excellent stuff!

  4. This is an excellent article! Thank you for sharing. :-).

  5. Very helpful. Thanks a lot

  6. Nice…Easy to understand

  7. Thanks It is a very nice way to diagnosis any session, But How can I know in trace file database slow performance

    • A trace file won’t tell you if the database is performing poorly, but it can tell what is happening with a particular session. Presumably you’re tracing the session because you know you have an issue with its performance or functioning. If you’re looking at overall performance you should try using awrrpt.sql, addmrpt.sql, and ashrpt.sql in ?/rdbms/admin

  8. What if you don’t know the session id? You just want to turn trace on the entire database (knowing full well the barrage of data that could generate)?

    • Then I’d say you might need to spend more time figuring out where the problem is. If you really have to you can use alter system set sql_trace = true;

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s