79 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.

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

Use the dbmshptab.sql script located in the rdbms/admin directory to create the hierarchical profiler database tables and other data structures required for persistently storing the results of analyzing the raw profiler data.

Running dbmshptab.sql drops the any previously created hierarchical profiler tables.

Examples

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

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.

79.1.3 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.