TKPROF is one of the most practical and powerful utilities available to DBAs for diagnosing performance issues. In simple terms, it is used to format a trace file into a more readable format for performance analysis. The DBA can then identify and deal with any performance issues such as poor SQL, indexing, and wait events.
For many users, and in many different ways, the TKPROF utility has been quite difficult to use for a number of reasons. Firstly, the whole process of enabling tracing, finding trace files, and executing the utility against them is a troublesome task. When the DBA has the trace file output users are often unsure what to do next.
Additionally, even though TKPROF formats the data, it does not bring any other data, which would enable the solution of the problems revealed. It should be noted that many times the problems are not even highlighted, which means more work for the DBA in order to analyze the output, assess the problems, and determine what to do.
TKPROF will be used by the DBA when the database or a certain session has to deal with performance issues and problems. Overall, TKPROF will be used sparingly, as it is not the most commonly used component when searching for a specific performance issue.
For example, a system user may complain about the response time for a session being much worse than previously. In such circumstances, session tracing and TKPROF can be used to find out exactly what is happening on the database, enabling the DBA to take corrective action. This very functional utility can also be used to view SQL that is being executed for an application.
In certain situations, this will be the only mechanism a DBA will have to view SQL, and such situations include the execution of encrypted PL/SQL code on the database or submission of SQL statements from third party applications.
It is important to know what DBAs need to be looking for. As such, here is a list of the most important items to look out for:
Here is a brief overview of how the TKPROF process works. It should be noted that before tracing can be enabled, the environment needs to be configured by going through the following stages.
The first step taken is to enable timed statistics. This is a parameter that enables a collection of certain vital statistics. These things include CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is: ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
The second step is to check the user dump destination directory. It is here that the trace files that are generated by Oracle (which can often be great in number), which Oracle places in user_dump_dest directory, as specified in the init.ora.
The user dump destination can also be specified for a single session using the alter session command. You need to ensure that enough space exists on the device to support the number of trace files that you expect to generate.
You also need to turn tracing on, or enable it. The tracing is disabled by default due to the burden it places on the database. Once tracing is enabled, Oracle generates and stores the statistics in the trace file, and the trace file name is version specific.
You can also disable tracing later, using the same process that you used to enable it.
