Oracle: SQL_TRACE &TKPROF Usage

I saw fringe benefits of writing blogs. In child days, teachers were saying that - one time writing is better than ten times reading! This is kind of valuable statements I always realize.

I did use SQL_TRACE and TKPROF earlier - before 3 years may be. But again last week, I had to look at Oracle docs to refresh my memory! This time what I did, I am trying to put it here so that next time, if needed, I won't have to spend much time on that :-)

What is SQL_TRACE?

Simply, it is a dynamic parameter could be set in session or system level to enable some detailed tracing of database activities.

What is TKPROF?

Ha! This was a common question I asked many times in interviews earlier days. It is a command line tool supplied by Oracle to format tracing output. That's it!

Why SQL_TRACE & TKPROF come altogether?

TKPROF has made our life easier to understand the contents of trace file. When we enable tracing - I mean when we set SQL_TRACE=TRUE, Oracle does good stuffs for us which is not formatted well to retrieve useful information from those files. TKPROF makes the output readable.

SQL Trace facility and TKPROF are two basic performance diagnostic tools used to analyze query and server performance. Now a days, I am using other tools (AWR, ADDM etc) more than this for performance analysis. Actually I used the SQL_TRACE this time to diagnose a BLOB problem - I was interested to see some information when BLOB got inserted into database using jdbc, anyways.


Considerations before using SQL_TRACE

We need to check the following 3 parameter before enabling sql tracing.

1. TIMED_STATISTICS - > This enables and disables the collection of timed statistics, such as CPU and elapsed times

2. MAX_DUMP_FILE_SIZE - > When the SQL Trace facility is enabled at the instance level, every call to the server produces a text line in a file in the operating system's file format. This controls the size of the file.

3. USER_DUMP_DEST - > Determines the destination of log files.

Come to the point - how to enable this?

--session level
ALTER SESSION SET SQL_TRACE = TRUE;

--system level
ALTER SYSTEM SET SQL_TRACE = TRUE;

How to use TKPROF?

In simple form, TKPROF takes source trace file and then the name of output file that will be generated by TKPROF.

# tkprof trace_file output=output_file_name

In windows, the command would be -

(running from command prompt...)
C:\Users\shawon>tkprof C:\oracle\product\10.2.0\admin\shawon_db\udump\shawon_ora_4288.trc output = C:\oracle\product\10.2.0\admin\shawon_db\udump\tmp.out


The detailed documents could be found from this site -

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#8723