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.

To Install the Profiler

  1. Make sure you have the Oracle SYS.DBMS_PROFILER package installed on your database.

  2. Make sure that GRANT EXECUTE on the DBMS_PROFILE package has been granted to PUBLIC or to the users that will use the profiling feature.

  3. Use the Server Side Objects wizard to install database objects required.

To use the Profiler

  1. Start Toad, select the Database|PL/SQL Profiling menu option to turn on the profiler,

Or

Click the Toggle PL/SQL Profiling button in the main Toad toolbar.

  1. Execute a procedure from the Schema Browser or the Editor using the Execute (lightning bolt) button. You will be prompted to enter a description of the procedure being executed. This appears in the Profiler Analysis window or the Editor Profiler tab when you are analyzing the results. Run the procedure several times to get some data into the profiling tables.

  2. Turn off profiling when you are finished by clicking the Toggle PL/SQL Profiling button.

Note: Be careful to not leave the profiler toggled on when you switch to other Toad windows. Otherwise, profiler data will be collected from the queries Toad performs to populate those windows.

 

  1. Select the Database|Profiler Analysis menu item. The Profiler Analysis window appears. For more information on reading the information provided, see Profiler Analysis in the main help file.