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 18.104.22.168.0 - 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