Procedure to enable SQL trace for users on your database

What is tkprof

tkprof is one of the most helpful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.

Analyzing Results

· Compare the number of parses to number of executions.
· Search for SQL statements that do not use bind variables
· Identify those statements that perform full table scans, multiple disk reads, and high CPU consumption.

1. Find the User Dump Directory

SQL> select value from v$parameter where name = 'user_dump_dest';


2. Get the SID and SERIAL# for the process you want to trace.

SQL> select sid, serial# from sys.v_$session
SID SERIAL#
---------- ----------
8 13607

3. Enable tracing for your selected process:

SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, true);

4. Ask user to run just the necessary to demonstrate his problem.

5. Disable tracing for your selected process:

SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);
SQL> ALTER SYSTEM SET TIMED_STATISTICS = FALSE;

6. Look for trace file in USER_DUMP_DEST

$ cd /app/oracle/admin/oradba/udump
$ ls -ltr
total 8
-rw-r----- 1 oracle dba 2764 Mar 30 12:37 ora_9294.trc

7. Run TKPROF to analyse trace output

$ tkprof ora_9294.trc OUTPUT=ora_9294.lst EXPLAIN=SCHEMA_NAME/PASSWD

8. View/print output