Profiling with SQL*Plus
So far the runs have been made with TOAD. There are situations, however, where you'll want to start a profiling process outside of TOAD in order to control the profiling session morefor instance, by profiling multiple procedures in batch mode, opening multiple SQL*Plus sessions and running profilings in parallel, etc. With TOAD you are limited to one instance of the program running and one procedure profiling at a time.
To profile outside of TOAD, you need to do TOAD's work by yourself:
- Add a wrapper around the procedure call, utilizing the START_PROFILER and STOP_PROFILER procedures of the DBMS_PROFILER package.
- Insert additional code to calculate totals, load source code, and do the cleanup.
I created the stored procedures ProfilerUnitsTotals (Listing 3
) and ProfilerLoadSource (Listing 4
) to do the second item. You may consider creating your custom profiling package where you can put these procedures and add more utilities later.
After doing this, you can use TOAD's nice graphic profiling interface, even though you will be running your procedure from SQL*Plus.
Start SQL*Plus, connect to the database as the same user connected via TOAD, and execute the PL/SQL anonymous block shown in Listing 5.
|Figure 5. Line-by-line Analysis of SQL*Plus Runs: A line-by-line analysis of the improved TestProc, SQL*Plus runs 8-10.|
Now you can look at the profiling data generated by SQL*Plus' runs via the graphical TOAD interface.
From Figure 5 it's clear that all six runs of the improved version of TestProc (TOAD runs 46 and SQL*Plus runs 810) are 1012 times faster than the original version (runs 13). This happened because we were able to locate the problem (line 18) using the Profiler and solve it, with a consequent significant performance gain.
As you can see, Oracle 8i Profiler is a powerful tool that allows Oracle database developers to collect execution time statistics, identify bottlenecks, and tune PL/SQL code efficiently.