Tracing Execution Using TKPROF

In the old days before Oracle debuggers and profilers became available, the only way to see what was going on inside your program was the TKPROF utility. This was an undocumented tool first available in Oracle version 5. But then Oracle support began having people utilize it to troubleshoot their problems, so Oracle made it an official utility in Oracle version 6. And although today's debugger and profilers are snazzy, TKPROF still offers some value during application development. So TOAD offers the TKProf Interface. However, there are some preparatory steps you must perform in order to effectively utilize this utility.

Capturing Trace Output to Profile

First, you must start the trace for the session you want to monitor. The easiest way to do this is via TOAD's Kill/Trace Session screen shown in Figure 9.49 and located on the main menu at DBA, Kill/Trace Session. You merely need to click the green lantern toolbar button to initiate tracing that session.

Figure 9.49. TOAD Kill/Trace Session ”start a trace.

Second, you need to run some application code such as that shown in Figure 9.50, which runs the CHECK_DUPLICATES package. Third, you should return to the Kill/Trace Session window and stop the trace (this time clicking the red lantern toolbar button).

Figure 9.50. TOAD Schema Browser ” execute some code.

Remember that Oracle's trace files are created on the database server under the UDUMP directory. So if you're working with UNIX instead of a local Windows database, you'll have to FTP down the recently generated trace file as shown in Figure 9.51. With that, you've now completed the preparatory steps for successfully using TOAD's TKProf Interface.

Figure 9.51. TOAD FTP ”download the trace file.

Profiling the Captured Trace Output

The TKProf Interface screen is shown in Figure 9.52 and is located on the main menu at Tools, TKProf Interface. It's a simple three-step wizard. First you specify the Oracle trace files you want to have decoded, as shown in Figure 9.52. When TKPROF is run, it will produce an output file by the same name but with a .OUT extension, which you can change here.

Figure 9.52. TOAD TKPROF Wizard ”Step 1.

Second, you check the sort criteria you want used during TKPROF's run as shown in Figure 9.53. Note that you can select multiple items here. The example shown as the selection for # of Physical Reads from Disk During Fetch is often a good place to start. Remember, disk IO is not our friend ”so finding high physical disk IO rates is generally the quickest and easiest way to make the most improvement for the least effort.

Figure 9.53. TOAD TKPROF Wizard ”Step 2.

And third, you can define additional TKPROF parameters such as who to connect as for explain plans and whose explain plan table to use. This is shown in Figure 9.54. After you click the Finish button, TOAD spawns a TKPROF command-line execution using the information you've supplied. And if View Output Files When Finished is checked, TOAD will open the results in Notepad as shown in Figure 9.55.