There is more information about using this feature in Toad in the Help | Content, search for Profiler Analysis.
Profiler Analysis Overview
Oracle8i provides a Probe Profiler API to profile existing PL/SQL applications and to identify performance bottlenecks. The collected profiler (performance) data can be used for performance improvement efforts or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts.
The profiler API is implemented as a PL/SQL package, DBMS_PROFILER, that provides services for collecting and persistently storing PL/SQL profiler data.
Caution: Statistics may not be collected properly if you are running the Profiler on an Oracle server on a Tru64 platform.
Using DBMS_PROFILER
Improving application performance is an iterative process. Every iteration involves the following:
. Exercising the application with one or more benchmark tests, with profiler data collection enabled.
. Analyzing the profiler data, and identifying performance problems.
. Fixing the problems.
To support this process, the PL/SQL profiler supports the notion of a run. A run involves running the application through benchmark tests with profiler data collection enabled. You can control the beginning and the end of the run by clicking the Toggle PL/SQL Profiling button in the main Toad toolbar. The icon for the button is a stopwatch.
A typical session involves:
. Starting profiler data collection in session.
. Executing PL/SQL code for which profiler/code coverage data is required
. Stopping profiler data collection.
Some PL/SQL operations, such as the very first execution of a PL/SQL unit, may involve I/O to catalog tables to load the byte code for the PL/SQL unit being executed. Also, it may take some time executing package initialization code the first time a package procedure or function is called. To avoid timing this overhead, you should warm up the database before collecting profile data. Warming up involves running the application once without gathering profiler data.
Collected Data
With the Probe Profiler API, you can generate profiling information for all named library units that are executed in a session. The profiler gathers information at the PL/SQL virtual machine level that includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
The profiling information is stored in database tables. This enables the ad-hoc querying on the data: It lets you build customizable reports (summary reports, hottest lines, code coverage data, and so on) and analysis capabilities.
With Oracle 8i, a sample textual report writer is provided with the PL/SQL demo scripts.
Using DBMS_PROFILER with the Java debugger
Toad lets you use the Profiler in connection with the Java debugger as well as when debugging PL/SQL. Output, however is different.
When you profile Java code, all of the code is wrapped into an Anonymous Block, and then only that block is profiled. When debugging PL/SQL you are given the time per statement. In JDWP debugging, Toad returns the time for the entire procedure.
