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 Objects
If 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 Wizard
2. Choose add under Toad Profiler
Note: 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 Editor
2. Click on the 'Session | Toggle PL\SQL Profiling' icon (or. the stop watch icon) to turn profiling on
3. Click on the 'Execute PL/SQL with Debugger' button to run your code
4. Select ‘Database | Optimize | Profiler Analysis’ to run the Profiler Analysis
If 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.