You can use the DBMS Profiler or the hierarchical profiler, depending on their availability in your database version.
The PL/SQL hierarchical profiler organizes data by subprogram calls, and stores the results in database tables letting you create custom reports.
See Oracle's documentation for more information.
Information provided includes:
The Probe Profiler API profiles existing PL/SQL applications and identifies 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.
Improving application performance is an iterative process. Every iteration involves the following:
To support this process, the PL/SQL profiler supports the notion of a run. A run involves executing specified SQL commands through benchmark tests with profiler data collection enabled.
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.
This feature requires that certain objects exist on the server before you can use it. If they do not already exist, Toad prompts you to create them when you click .
Note: To remove the profiler objects, click the arrow next to and select Remove Profiler.
You must have the SYS.DBMS_PROFILER package to use the DBMS profiler.
To install the package
To use the hierarchical profiler, you must enable it in the Toad options. Select View | Toad Options | Execute/Compile and then select Use hierarchical profiler on Oracle 11g and newer.
You must also have the DBMS_HPROF package to use the hierarchical profiler, which is available in Oracle 11g and later.
To verify the package is installed
To use the hierarchical profiler, you must enable it in the Toad options.
To enable the hierarchical profiler
Select View | Toad Options | Execute/Compile and then select Use hierarchical profiler on Oracle 11g and newer.
To use a profiler
Click on the main Toad toolbar to turn on profiling.
Note: If the profiler is not set up, Toad notifies you. Set Up the Profiler
Complete the parameters and profiler settings.
Note: Be careful to not leave the profiler toggled on when you switch to other Toad windows. Otherwise, Toad collects profiler data from the queries performed to populate those windows.
Select Database | Optimize | Profiler Analysis. The Profiler Analysis window displays. View Profiler Results
Click the Profiler tab beneath the Editor. Editor Profiler Tab
The Profiler Analysis window provides data on profiler runs that is consistent with the data displayed in the Profiler tab of the Editor. Editor Profiler Tab
The top half of the window is a graph of the showing the percent of time required to run each component of the procedure.
Note: If you can see the pie chart labels but not the pie chart itself, resize the window horizontally to give it more space to draw.
To access the Profiler Analysis window
Select Database | Optimize | Profiler Analysis.
Opening a run: Selecting this displays the graph for all units within that run. Expanding a run in the tree view will list the details of the run including Unit Type, Owner, Unit Name, and Total Time to execute.
Opening a unit: You can also select a specific unit of the selected run.
When drilling down on a unit, we see the lines of code executed and profiled. The column headers include Line Number, Passes (how many times each line of code was executed), Total Time to execute the line, Min Time, Max Time, and the line of Code itself. The graph changes to display the information within that unit.
Displaying in Editor:If you select a valid unit in the tree view, right-click and select display in Editor, the Editor displays the selected unit.