How to generate trace file - SQL Trace and TKPROF in Oracle

It is a frequently asked question in almost all the Oracle forums. There have had been numerous questions/posts regarding "But how to generate the trace file?" Well, it might seem a heck of a task, however, looking it step by step will make you understand that it is actually not that difficult.

Usually, database application developers do not have the required/necessary permissions/privileges to do all the steps that I will mention below. However, most of the steps could be done by application developer. A few steps will need a DBA privilege.

Let me take you through the steps :

Turning the tracing event ON with a proper level

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

The different levels of tracing event:-

0 - No trace. Like switching sql_trace off.

2 - The equivalent of regular sql_trace.

4 - The same as 2, but with the addition of bind variable values.

8 - The same as 2, but with the addition of wait events.

12 - The same as 2, but with both bind variable values and wait events.

Giving a proper name/number for the trace file identifier

SQL> alter session set tracefile_identifier = 'test_plan1';

Execute the SQL query which you want to trace

SQL> SELECT col1, col2…..FROM t1, t2…..WHERE…..;

Turning the tracing event OFF

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

Finding the directory where the trace files are generated(this is the directory which is in “init.ora” file)

SQL> SHOW PARAMETER user_dump_dest;

NAME                                 TYPE        VALUE

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

user_dump_dest                       string      /opt/oracle/diag/rdbms/lalit/trace

Finding the trace file in the UNIX directory

lalit@Orcl:DEV$ cd /opt/oracle/diag/rdbms/lalit/trace

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ ls -lrt *test_plan1.trc

-rw-r—–    1 oracle   dba         1076036 May 02 04:46 lalit_ora_30539942_test_plan1.trc

-rw-r—–    1 oracle   dba         1903274 May 02 04:51 lalit_ora_33030344_test_plan1.trc

So now we have the trace file, just give the trace file name to DBA to get the tkprof output and take read access on tkprof.out file

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ls -lrt *.out

-rw-r–r–    1 oracle   dba          17273 May 02 04:52 tkprof.out

Analyze the tkprof.out file to find the issue with the SQL query.

Simple, isn't it :-)

From 10g onwards all the tracing options have been centralized into DBMS_MONITOR package. You can look for the package in docs http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm