, , , ,

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 for which you want the trace

  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 Jan 01 06:46 lalit_ora_30539942_test_plan1.trc

-rw-r-----    1 oracle   dba         1903274 Jan 01 06:51 lalit_ora_33030344_test_plan1.trc

So now we have the trace file, we need to get the tkprof output:

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ tkprof lalit_ora_30539942_test_plan1.trc tkprof.out

TKPROF: Release - Development on Wed Jan 1 18:53:33 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

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

-rw-r--r--    1 oracle   dba           17273 Jan 01 06:53 tkprof.out

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

From 10g on wards 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