Chat now with support
Chat with Support

Toad for Oracle 12.12 - Getting Started Guide

Table of Contents Welcome to Toad Toad Resources Required Privileges Create and Manage Database Connections Editor Basics Schema Browser Basics Data Grid Basics Work with Data Work with Database Objects Work with Code
Write Statements and Scripts Work with the Query Builder Execute Statements and Scripts Save Statements (SQL Recall) Work with PL/SQL Objects Debug Analyze Code Optimize SQL
Customize your Toad Environment Use Other Quest Software Products with Toad

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.
    • 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:

    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.

 

Editor Profiler Tab

Within the Editor, the Profiler tab displays profiler runs, as root nodes, and profiler units as child nodes. The latter are the actual code units that were executed during a profiler run. They can include anonymous blocks, procedures, functions, and packages executed while the profiler run data was being collected. In the line item profiler, child nodes contain the actual line data. In the hierarchical profiler, child nodes contain sub program calls.

This tab provides an overview of the data, but does not offer the graphs that the Profiler Analysis window does.

Selecting a line item within the nodes automatically opens the referenced SQL source and displays the line referenced by the profiler.

Note: Because each Editor tab is associated with a separate Profiler instance, navigating through your code this way may reset the node display in the Profiler tab.

To display the Profiler Analysis window for the current data

Click Details.

Executable Line Indicators

When you open a profiler run or unit into the Editor and have the option show executable line indicators in gutters selected, executable line indicators display as follows:

Indicator

Meaning

Blue dot with green square

Line was executed

Blue dot with red circle

Line was not executed

If Toad cannot determine when the unit was last executed, then the standard blue dot line indicators will display.

Hierarchical Profiler Filters

You can filter the results of your hierarchical profiling session. This can be useful in making sure that you only see the results that are useful for you.

Toad will automatically filter out the system information that is added when the profiler is active. You can manually turn these on if you want to see that information.

To create a filter

  1. From the Profiler tab at the bottom of the Editor, right click the grid and select Filter.

    Note: If you do not see the filter option, make sure you are actually using the hierarchical profiler.

  2. Click Add to add a new filter to the filter grid. Enter the criteria you want to use to hide data. You may use the % wildcard within the filter.
  3. Enable or disable any filters desired by selecting or clearing the Enable box.
  4. Repeat steps 2 and 3 if necessary.
  5. Click OK.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating