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.
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
-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, just give the trace file name to DBA to get the tkprof output and take read access on tkprof.out file
-rw-r–r– 1 oracle dba 17273 Jan 01 06:52 tkprof.out
Analyze the tkprof.out file to find the issue with the SQL query.
From 10g onwards all the tracing options have been centralized into
DBMS_MONITOR package. You can look for the package in docshttp://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm
No comments:
Post a Comment