Author: Don Burleson

Oracle has provided many useful utilities that are at the fingertips of Oracle professionals. TKPROF is one of them. TKPROF reports each statement executed with the resources it has consumed, the number of times it was called, and the number of rows which it processed. This valuable information can help you easily locate statements that are using the greatest resource. In this article I will give some great tips for using Oracle TKPROF.

Enable Oracle TKPROF:

Enable Oracle TKPROF tracing only on those sessions that are having problems. Be selective to minimize the performance burden on the sessions and to retain more free space in the user dump destination directory.

Before tracing can be enabled, the environment must first be configured by performing the following steps:

1. Enable Timed Statistics . The command to enable timed statistics is: ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
2. Check the User Dump Destination Directory . Make sure that enough space exists on the device to support the number of trace files that you expect to generate.
3. Enable tracing. Tracing can be defined at the session level:
   
   ALTER SESSION SET SQL_TRACE = TRUE;
   
   DBMS_SESSION.SET_SQL_TRACE(TRUE);
   
   A DBA may enable tracing for another user's session by using the following statement:
   
   DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true); where sid (Session ID) and serial# can be obtained from the v$session view.
4. Once tracing with Oracle TKPROF is enabled, Oracle generates and stores the statistics in the trace file. The trace file name is version specific.

Explain Plan in conjunction with Oracle TKPROF:

Explain Plan is not as useful when used in conjunction with TKPROF since the trace file contains the actual execution path of the SQL statement. Explain Plan must be used when anticipated execution statistics are desired without actually executing the statement.

Be cautious while Tracing:

Remember that nothing in v$session indicates that a session is being traced during tracing a session. Therefore, trace with caution and remember to disable tracing after an adequate amount of trace data has been generated.

TKPROF does not control the contents:

TKPROF does not control the contents of a trace file rather it simply formats them. Oracle provides multiple ways to actually generate the trace file. TKPROF is valuable for detailed trace file analysis. For those Database administrators that prefer a simpler tracing mechanism with instant feedback, the autotrace utility should be used.

Rename trace files:

Rename trace files after tracing is disabled. The new file name should be something more meaningful that will be recognizable at a later date. The name employee_index.trc is much more meaningful than ORCL92_ora_3172.trc

Delete unused trace files:

Delete trace files that are no longer needed to reduce mess and free disk space.

Compare the number of parses to number of executions:  

Compare the number of parses to number of executions in TKPROF formatted files. A well-tuned system will have one parse per n executions of a statement and will eliminate the re-parsing of the same statement.

Use Bind Variables:

In TKPROF formatted files search for SQL statements that do not use bind variables (:variable). These statements should be modified to use bind variables.

Identify performance benchmarks:

Identify those statements in TKPROF formatted files that perform full table scans, multiple disk reads, and high CPU consumption. These performance benchmarks are defined by the DBA and need to be tailored to each database.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Secrets of Protecting Data in your Database Tables!!
   DBA Tips for Re-sequencing using Oracle Analytic Functions!!
   12 U’s for Good Database Design
   Troubleshooting Oracle Performance Problems!!