Chat now with support
Chat with Support

SQL Navigator for Oracle 7.5 - User Guide

Quick Overview Working With SQL Navigator Navigation Oracle Logon Code Editor Visual Object Editors Team Coding and Version Control Systems Modules, Windows And Dialogs
Analyze Tool Auto Describe Tool Benchmark Factory Bookmarks Dialog Capture Web Output Change Logon Password Dialog Code Analysis Code Assistant Code Road Map Code Templates Code Test Database Source Code Search Dialog DB Navigator Describe Difference Viewer Edit Data ER Diagram Explain Plan Tool Export Table Find and Replace Dialog Find objects Dialog Find Recycle Bin Objects Dialog Formatting Options HTML Viewer Import HTML as PL/SQL Import Table Java Manager Job Scheduler Locate In Tree Output Window PL/SQL Profiler Profile Manager Project Manager Publish Java to PL/SQL Quick Browse Rename Object Search Knowledge Xpert Select DB Object Dialog Server Output Server Side Installation Wizard Session Browser Source Preview SQL Modeler SQL Optimizer Task Manager Web Support Configuration Dialog Wrap Code
View | Preferences About Us

PL/SQL Profiler

Analyze the execution time and efficiency of your stored programs. The Profiler is particularly useful for finding bottlenecks in stored code and quality assurance and testing.

Note:

  • Requires Oracle 8.1.5 or higher.
  • Before using the Profiler, debug your stored program, as there is no editing capability from within the Profiler.

 

Toolbar

TIP: Use the Runs / Groups tabs to select Runs and Groups.

 

Icon Tool Tip Description Keyboard Shortcut

Refresh

Update both Run and Group tree views with the latest profiling data.

Alt+R

Filters / Preferences

Open the Profiler Filter/Preferences Dialog.

You can sort and filter the result data according to thresholds that you set. This makes it easy to limit the amount of data displayed, and to isolate the most significant items. For example, you can select lines that were not called during the run, or runs with total times higher than average.

Alt+P

Delete

Delete the data for the selected run.

If a group is selected then delete the group. Removing the group does not remove the associated run data.

Del

Create New Group

Create a new group. Open the New Group / Group Properties Dialog.

Create groups to logically connect code units and runs. You determine which units and runs belong to a group. You can combine data for a single code unit across multiple runs in order to determine the real coverage and execution times. This is useful when testing stored code in several different runs with different parameters.

Ins

Locate the selected object in DB Navigator

Open DB Navigator with the tree expanded to highlight the selected object. See where the object is in the database tree.

 

Open the selected object

Open the selected object in the Code Editor.

Select a line of source code in the Source Viewer tab to open the Code Editor at that line.

 

Properties

Open the New Group / Group Properties Dialog to modify the selected group.

Alt+F2

Launch Xpert tuning

Open SQL Optimizer for Oracle.

 

 

Runs Tab

A run contains all code units that are called during execution. If a procedure or function is a part of a package, the whole package becomes part of the run. The same rule applies to type methods.

Selection Description of information

All Runs

Totals across all the available runs.

Coverage

Time Statistics

Run

Basic run characteristics (comment, date, number of lines, etc…), totals across all the run units.

Coverage

Time Statistics

 

Groups Tab

For each group, you have the option of displaying the data either separately by run, or with runs combined.

Selection Description of information

All Groups

Totals across all the groups.

Time Statistics

Group

Basic group characteristics, totals across the group units and runs.

Coverage

Time Statistics

 

Select PL/SQL Code on the Runs or Groups Tab

A PL/SQL code unit can be a stand-alone procedure or function, a package body, a type body or an anonymous block. A package body and type body contains further procedures and functions. For a procedure or function, it is sometimes possible to determine how many times it has been called during a run by parsing the available source code and combining the data about the lines where the procedure/function is called.

The Profiler will show you a line-by line analysis of the execution, including the number of times each line was executed and the time required for execution.

The Profiler:

  • stores data about each code unit executed during a run, down to the level of source code lines
  • divides all the available profiling data into hierarchically organized logical items
  • displays profiling and coverage statistics about each item and compares them with others

The Profiler displays the profiling data alongside the actual source code. (This feature is not available if the source code has changed since it was last tested in the currently selected run, or if it is not identical across the runs in a group.)

Selection Description

Procedure, Function, Trigger Body

Basic characteristics, totals across the runs. Combines the group runs if Combine Runs in Group Result Sets is selected in the Profiler Filter/Preferences Dialog.

Time Statistics

Call Distribution

Source Viewer

Type Body, Package Body

Basic characteristics, total across the runs. Combines the group runs if Combine Runs in Group Result Sets is selected in the Profiler Filter/Preferences Dialog.

Coverage

Call Distribution

Time Statistics

Source Viewer

 

Note: The Profiler uses the Oracle session it was activated in.

Profiler Filter/Preferences Dialog

Use the PL/SQL Profiler Filters/Preferences dialog to set the data filter options, sorting and chart drawing properties.

 

Data Filters

TIP: Threshold and sorting works only if there is a single series to be displayed in the Runs tree and ‘Combine Runs in Group Result Sets’ is enabled for the Groups tree.

 

Option Description

Data Value Filter in Runs Tree View

Option Description
Display only items with value When selected, charts and lists display only the items with the value specified
More than, Less than, Equal to Zero, Nonzero Set the method of filtering

50 or other value

Set the Threshold value.

Applicable when More than and Less than are selected.

Total/Average

Set the value against which the threshold value is to be compared.

Applicable when More than and Less than are selected.

Show Anonymous Blocks

Set the tree views to display the anonymous blocks executed during runs.

Parse Packages for Subroutines

Set the profiler to break package data down to discrete procedures/functions.

Display Line Data Directly

Set the data displays to always show line data for the selected tree view item.

For example, when a run is selected, the charts/lists will show all the lines executed in the run, not the run units.

Sort Result Sets in Runs Tree View

Enable sorting of the result data in the Runs display.

When selected, you can control the sort order by your selection of the Descending Order option.

Combine Runs in Group Result Sets

Enable the combining of data from different Runs in the Group (for example, to obtain correct coverage value across the Runs).

 

Chart Options

Option Description

3D

Select to add a 3D look and feel to the charts.

Group Chart Series

Select the color generation method for the chart series when displaying Group data (Groups tree view).

Run Chart Series

Select the color generation method for the chart series when displaying Run data (Runs tree view).

Chart Panel

Control the chart background color.

Series Color Gradient

Select the colors used when Gradient is selected.

Series Color Sequence

Select the colors used when Sequence is selected.

TIP: Click on the color selection rectangles. Select a color from the Windows-standard color selection dialog.

New Group / Group Properties Dialog

Use the Group Properties dialog to set group name, comment, used units and runs.

The Group Units and Group Runs list boxes display the units/runs used for analysis in the given group. You must specify at least one group unit.

PL/SQL Profiler Requirements

Required Oracle Conditions

To run the PL/SQL Profiler

The DBMS_PROFILER package needs to be installed under SYS.

This is does not happen by default on a new instance.

To collect session statistics

The users need access to V$SESSION, V$SESSTAT and V$STATNAME.

This condition is satisfied when the users have SELECT privilege on system views V_$SESSION, V_$SESSTAT and V_$STATNAME.

For schemas that use the PL/SQL Profiler

The profiler server side objectsInstalling_server_side_objects (tables and sequences) need to be installed.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating