Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

Part Number B28419-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

60 DBMS_HPROF

The DBMS_HPROF package provides an interface for profiling the execution of PL/SQL applications. It provides services for collecting the hierarchical profiler data, analyzing the raw profiler output and profiling information generation.

See Also:

Oracle Database Advanced Application Developer's Guide for more information about the "PL/SQL Hierarchical Profiler"

This chapter contains the following topic:


Summary of DBMS_HPROF Subprograms

This table list the package subprograms in alphabetical order.

Table 60-1 DBMS_HPROF Package Subprograms

Subprogram Description
ANALYZE Function
Analyzes the raw profiler output and produces hierarchical profiler information in database tables
START_PROFILING Procedure
Starts hierarchical profiler data collection in the user's session
STOP_PROFILING Procedure
Stops profiler data collection in the user's session.s


ANALYZE Function

This function analyzes the raw profiler output and produces hierarchical profiler information in database tables.

Syntax

DBMS_HPROF.ANALYZE (
   location         VARCHAR2,
   filename         VARCHAR2,
   summary_mode     BOOLEAN     DEFAULT FALSE,
   trace            VARCHAR2    DEFAULT NULL,
   skip             PLS_INTEGER DEFAULT 0,
   collect          PLS_INTEGER DEFAULT NULL,
   run_comment      VARCHAR2    DEFAULT NULL)
  RETURN NUMBER;

Parameters

Table 60-2 ANALYZE Function Parameters

Parameter Description
location Name of a directory object. The raw profiler data file is read from the file system directory mapped to this directory object. Output files are also written to this directory.
filename Name of the raw profiler data file to be analyzed. The file must exist in the directory specified by the location parameter.
summary_mode By default (that is, when summary_mode is FALSE), the detailed analysis is done.When summary_mode is TRUE, only top-level summary information is generated into the database table.
trace Analyze only the subtrees rooted at the specified trace entry. By default (when trace is NULL), the analysis/reporting is generated for the entire run.

The trace entry must be specified in a special quoted qualified format (including the schema name, module name & function name) as in for example, '"SCOTT"."PKG"."FOO"' or '"".""."__plsql_vm"'. If multiple overloads exist for the specified name, all of them will be analyzed.

skip Used only when trace is specified.Analyze only the subtrees rooted at the specified trace, but ignore the first skip invocations to trace. The default value for skip is 0.
collect Used only when trace is specified.

Analyze collect number of invocations of traces (starting from skip+1'th invocation). By default only 1 invocation is collected.

run_comment User-provided comment for this run

Return Values

A unique run identifier for this run of the analyzer. This can then be used to look up the results corresponding to this run from the hierarchical profiler tables.

Usage Notes

Examples

The following snippet installs the hierarchical profiler tables in HR schema.

connect HR/HR;
@?/rdbms/admin/dbmshptab.sql

START_PROFILING Procedure

This procedure starts hierarchical profiler data collection in the user's session.

Syntax

DBMS_HPROF.START_PROFILING (
   location    VARCHAR2 DEFAULT NULL,
   filename    VARCHAR2 DEFAULT NULL,
   max_depth   PLS_INTEGER DEFAULT NULL);

Parameters

Table 60-3 START_PROFILING Procedure Parameters

Parameter Description
location Name of a directory object. The file system directory mapped to this directory object is where the raw profiler output is generated.
filename Output filename for the raw profiler data. The file is created in the directory specified by the location parameter.
max_depth By default (that is, when max_depth value is NULL) profile information is gathered for all functions irrespective of their call depth. When a non-NULL value is specified for max_depth, the profiler collects data only for functions up to a call depth level of max_depth.

Usage Notes

Even though the profiler does not individually track functions at depth greater than max_depth, the time spent in such functions is charged to the ancestor function at depth max_depth.


STOP_PROFILING Procedure

This procedure stops profiler data collection in the user's session. This subprogram also has the side effect of flushing data collected so far in the session, and it signals the end of a run.

Syntax

DBMS_HPROF.STOP_PROFILING;