How to Create a 10046 Trace File in Oracle

This article will show you how to create a 10046 trace file in Oracle. There are multiple different ways of creating a SQL trace file, including doing an alter session set sql_trace = true but if you want the most detailed information possible, you’ll want to do this:

alter session set events '10046 trace name context forever, level 8';

-- ....run your query/queries here...

alter session set events '10046 trace name context off';

You can find your resulting trace file by running the following query:

select p.tracefile
from v$session s
   , v$process p
where s.sid = sys_context('userenv', 'sid')
  and p.addr = s.paddr;

That will give you the path and filename on the server to the trace file that corresponds to your session.

Once you have the output, it may be helpful to run the file through tkprof to aggregate the result into a more readable format. BUT! The raw trace file still contains very valuable information. So, I would say don’t discard the original trace file…use it in conjunction with the result of tkprof.

When you run tkprof, you specify tkprof <<input file>> <<output file>>

tkprof ora19c_ora_15929.trc output_file.txt

Both your trace file and the output of the tkprof program may contain recursive statements that are issued “under the hood” as part of executing your query–pay attention to these as well, as these can also be the cause of slow-running SQL.

Now you know how to create a 10046 trace file in Oracle. If you liked this article and you have an interest in Oracle SQL Tuning, why not check out my free Oracle SQL Tuning guide?

Free Oracle SQL Tuning Guide

Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets