Profiling with TOAD
I'm using TOAD (Tool for Oracle Application Developers) version 126.96.36.199 from Quest Software, Inc., to profile this TestProc stored procedure. Oracle doesn't have a graphic profiling tool of its own. You can download a free trial version of TOAD from the Quest Web site
To use the profiler in TOAD:
- Start TOAD and select Database > PL/SQL Profiling to turn on the profiler, or click the Toggle PL/SQL Profiling button in the main TOAD toolbar. The icon for the button is a stopwatch.
- Execute the procedure TestProc from the Schema Browser or the Procedure Editor using the Execute (lightning bolt) button. You will be prompted to enter a value for the p_Count IN parameter (10000) and a description of the procedure being executed ("TOAD, TestProc Run 1 - 12/19/2000"). This will show up in the Profile Analysis window when you analyze the results. You could run the procedure several times to get some data into the profiling tables.
Make sure you truncate the TestData table before each run by issuing the following SQL command:
Turn off profiling if you want at this point.
TRUNCATE TABLE TestData;
- Select Database > Profiler Analysis to bring up the Profiler Analysis window. There are three levels to the data. The top level represents the individual "Runs" of each procedure executed while profiling was enabled (see Figure 1).
- Double-click on one item to see the execution stats for that individual run. At this level, you can double-click to see the line-by-line performance times for individual procedures called during the profiling (see Figure 2).
The Code column, where you are supposed to see actual PL/SQL code lines, was actually empty after the run completed. I had to create my own stored procedure, ProfilerLoadSource, and run it to fix this problem by populating the Code column with lines of source code. According to the Quest Software support, it works fine there; they could not reproduce the problem.
Figure 1. The TOAD Profiler Analysis Window: The TOAD profiler analysis window shows three runs of the original procedure. Line-by-line analysis of TestProc original version, run 3. As you can see, line 18 took 99.61% of the total time to execute (10,000 passes, 5,852 ms. total).
|Figure 2. Line-by-line Analysis of TestProc: Line-by-line analysis of TestProc original version, run 3. As you can see, line 18 took 99.61% of the total time to execute (10,000 passes, 5,852 ms. total).|
Let's improve this code by utilizing the FORALL statement that Oracle 8i introduced to support bulk binds. (See my 10-Minute Solution, "New PL/SQL Features in Oracle 8i: Part II," where I describe the advantages of the bulk binds feature.)
Replace the keyword FOR with FORALL and remove the LOOP and END LOOP statements, which are no longer needed because all 10,000 inserts will be passed to the SQL engine in one step. Listing 2 shows the improved version of TestProc.
Figure 3. Improved version of TestProc: This figure shows the results of the next three runs. As you can see, the improved TestProc executes 10 to 12 times faster (average time 0.56 seconds) than the original one (average time 5.99 seconds).
|Figure 4. Line-by-line Analysis of Improved TestProc: Line 16 was executed only once instead of 10,000 times and it took only 584 ms. instead of 5,852 ms., as line 18 did in the original TestProc line-by-line analysis.|
Let's use TOAD to profile an improved version of the TestProc stored procedure. Figure 3 shows the results of the next three runs. As you can see, the improved TestProc executes 10 to 12 times faster (average time 0.56 sec.) than the original one (average time 5.99 sec.).
As Figure 4 shows, line 16 in the improved TestProc was executed only once instead of 10,000 times and it took only 584 ms. instead of 5,852 ms., as line 18 did in the original TestProc.