Wednesday, June 1, 2016

-- A Quick handy guide to generating SQL Trace --

We all have done this a time or another, and then we have to do it again and we have to go back to our notes to remember the steps, this is well-known information, but I encourage you to bookmark it and have it handy as you will eventually need to use it as well.

Tracing the execution of a SQL

alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';

/* now you execute the sql that you want to trace */


The trace file will be located in your diagnostic destination and will have the number 10046 as part of the file_name, you want to go there to format it using tkprof.  

tkprof input_file output_file waits=yes explain=username/password

Note: you can use the sort clause to sort the output in many ways, for a complete description of options please check the following resources: