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