Return
-
Title
How to setup and run Profiler Analysis (or PL/SQL Profiler) -
Description
How do I setup and run the Profiler Analysis (or PL/SQL Profiler)?
-
Resolution
Setting up the Profiler:
Part I - Make sure you have the SYS.DBMS_PROFILER package. If this has not been loaded, you will need to create it as follows.1. Login to Oracle through Toad as SYS.2. Load the Oracle home>\RDBMS\ADMIN\PROFLOAD.SQL script into the Editor.3. From the SQL Editor menu, select Execute as Script (or press F5).4. 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.Part II - Install Profiler Server Side ObjectsIf working with Toad for Oracle 10.x and older...1. Run the Server Side Objects Wizard to install the objects necessary to run the Profiler Analysis by going to Database | Administrator | Server Side Object Wizard2. Choose add under Toad ProfilerNote: For details on how to run the Server Side Object Wizard, please consult Help | Contents | Index with key word “Server Side Object.”If working with Toad for Oralce 11.x and newer...There is no longer a specific Server Side Object Wizard. When first accessing Profiler, Toad will automatically check for the required server side objects. if they do not already exist, Toad will prompt you accordingly at that time.Note: To remove the profiler objects, click the arrow next to and select Remove Profiler when prompted.Part III - Running the Profiler Analysis:If working with Toad for Oracle 10.x and older...1. Load a procedure, package, or function into the Editor2. Click on the 'Session | Toggle PL\SQL Profiling' icon (or. the stop watch icon) to turn profiling on3. Click on the 'Execute PL/SQL with Debugger' button to run your code4. Select ‘Database | Optimize | Profiler Analysis’ to run the Profiler AnalysisIf working with Toad for Oracle 11.x and newer...1. Click on the main Toad toolbar to turn on profiling.Note: If the profiler is not set up, Toad notifies you.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.
- Click the Profiler tab beneath the Editor.
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.
-
Attachments
-
Additional Information
You can use the DBMS Profiler with Oracle 8i and later, or the hierarchical profiler with Oracle 11g and later. Additional Hierarchical Profiler Requirements 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.
