About the Profilers

You can use the DBMS Profiler or the hierarchical profiler, depending on their availability in your database version.

Hierarchical Profiler

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:

• Number of calls to the subprogram
• Time spent in the subprogram
• Time spent in the subprogram and descendent subprograms
• Detailed parent-child information

DBMS Profiler

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:

• 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 executing specified SQL commands through benchmark tests with profiler data collection enabled.

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.

Set Up the Profilers

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.

Additional Requirements for the DBMS Profiler

You must have the SYS.DBMS_PROFILER package to use the DBMS profiler.

To install the package

1. Login to an Oracle database through Toad as SYS.
2. Load the Oracle home>\RDBMS\ADMIN\PROFLOAD.SQL script into the Editor.
3. Click on the Execute toolbar (F5).
4. Make sure that GRANT EXECUTE on the DBMS_PROFILER package has been granted to PUBLIC or to the users that will use the profiling feature.

Additional Requirements for the Hierarchical Profiler

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

1. Login to Oracle through Toad as SYS.
2. Make sure that GRANT EXECUTE on the DBMS_HPROF package has been granted to PUBLIC or to the users that will use the profiling feature.

Use a PL/SQL Profiler

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

1. 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

2. Open the procedure in the Editor and click on the Execute toolbar. The Set Parameters and Execute window displays.

3. Complete the parameters and profiler settings.

   Notes:

   • The profiler options are described by Oracle.
   • See Oracle's documentation for more information.
   • For the hierarchical profiler, you must select a directory on the Profiler tab. If you do not, Toad displays an error. It is recommended that you consult your DBA on the appropriate directory to select.
4. Click again to turn off profiling.

   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.

5. Review the profiler information. Select one of the following options:

   • Select Database | Optimize | Profiler Analysis. The Profiler Analysis window displays. View Profiler Results

   • Click the Profiler tab beneath the Editor. Editor Profiler Tab

   Notes:

   • If the Profiler tab is not visible, you can display it by right-clicking in the tab area and selecting Desktop Panels | Profiler.
   • By default, anonymous blocks and lines not executed are not displayed. You can display them by right-clicking the tree-view and selecting them from the menu.

View Profiler Results

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.

Run Details

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.