80 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.
This chapter contains the following topic:
See Also:
Oracle Database Development Guide for more information about the "PL/SQL Hierarchical Profiler"
80.1 Summary of DBMS_HPROF Subprograms
This table lists and briefly describes the DBMS_HPROF
package subprograms.
Table 80-1 DBMS_HPROF Package Subprograms
Subprogram | Description |
---|---|
Analyzes the raw profiler output and produces hierarchical profiler information in database tables. |
|
Creates the hierarchical profiler database tables and data structures in the user's session. |
|
Starts hierarchical profiler data collection in the user's session. |
|
Stops profiler data collection in the user's session. |
80.1.1 ANALYZE Function
This function analyzes the raw profiler output and produces hierarchical profiler information in database tables or generates out-of-the-box HTML reports.
Syntax
DBMS_HPROF.ANALYZE ( trace_id IN NUMBER, summary_mode IN BOOLEAN DEFAULT FALSE, trace IN VARCHAR2 DEFAULT NULL, skip IN PLS_INTEGER DEFAULT 0, collect IN PLS_INTEGER DEFAULT NULL, run_comment IN VARCHAR2 DEFAULT NULL) RETURN NUMBER; DBMS_HPROF.ANALYZE ( trace_id IN NUMBER, report_clob OUT CLOB, trace IN VARCHAR2 DEFAULT NULL, skip IN PLS_INTEGER DEFAULT 0, collect IN PLS_INTEGER DEFAULT NULL);
Parameters
Table 80-2 ANALYZE Function Parameters
Parameter | Description |
---|---|
|
The |
|
By default (that is, when When |
|
The analyzed HTML report. |
|
Analyzes only the subtrees rooted at the specified trace entry. By default (when trace is The trace entry must be specified in a special quoted qualified format. For example, '" If multiple overloads exist for the specified name, all of them will be analyzed. |
|
Used only when The default value for skip is |
|
Used only when Analyze By default, only 1 invocation is collected. |
|
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
DBMS_HPROF.CREATE_TABLES
subprogram to create the hierarchical profiler database tables and other data structures required for persistently storing the results of analyzing the raw profiler data. -
Calling the
DBMS_HPROF.CREATE_TABLES
with default value (FALSE
) will raise error if table already exists. -
Use
DBMS_HPROF.CREATE_TABLES(TRUE)
to drop any previously created hierarchical profiler tables. -
Use the
DBMS_HPROF.CREATE_TABLES
to drop any previously created hierarchical profiler tables. By default,force_it
isFALSE
; therefore, to drop any previously created hierarchical profiler tables you must set the value offorce_it
toTRUE
. -
If
trace_id
entry isNULL
, error is raised. -
If
trace_id
entry in the raw profiler data table does not exist, error is raised. -
If raw data of the
trace_id
entry in the raw profiler data table isNULL
or iszero
size, error is raised.
Examples
The following snippet installs the hierarchical profiler tables in HR schema.
connect HR/HR;
The following example analyzes and generates HTML CLOB report from a raw profiler data table.
DECLARE reportclob clob; trace_id number; BEGIN -- create raw profiler data and analysis tables -- force_it =>TRUE will dropped the tables if table exists DBMS_HPROF.CREATE_TABLES(force_it =>TRUE); -- Start profiling -- Write raw profiler data in raw profiler data table trace_id := DBMS_HPROF.START_PROFILING; -- Run procedure to be profiled test; -- Stop profiling DBMS_HPROF.STOP_PROFILING; -- analyzes trace_id entry in raw profiler data table and produce -- analyzed HTML report in reportclob DBMS_HPROF.ANALYZE(trace_id , reportclob); END; /
80.1.2 CREATE_TABLES Procedure
Creates the hierarchical profiler database tables and data structures in the user's session.
Syntax
DBMS_HPROF.CREATE_TABLES ( force_it IN BOOLEAN DEFAULT FALSE);
Parameters
Table 80-3 CREATE_TABLES Procedure Parameters
Parameter | Description |
---|---|
|
If If |
Note:
Users need not use the dbmshptab.sql
script located in the rdbms/admin
directory to create the hierarchical profiler database tables and data structures anymore.
The dbmshptab.sql
script is deprecated starting in Oracle Database 18c.
80.1.3 START_PROFILING Procedure
This procedure starts hierarchical profiler data collection in the user's session.
Syntax
DBMS_HPROF.START_PROFILING( max_depth IN PLS_INTEGER DEFAULT NULL, sqlmonitor IN BOOLEAN DEFAULT TRUE, run_comment IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
Parameters
Table 80-4 START_PROFILING Procedure Parameters
Parameter | Description |
---|---|
|
By default (that is, when |
|
Generates a real-time monitoring report for a profiler run when the profiler run ends. The default value is |
|
User provided comment for the profiler data collection run. |
Return Values
Unique run identifier for this profiler run. This can then be used to look up the results corresponding to this run from the hierarchical profiler tables.
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 depthmax_depth
. -
Raw profiler data is generated in the raw profiler data table with an unique
trace_id
. -
The unique
trace_id
is used to manage the raw profiler output stored in the raw profiler data table.
80.1.4 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. When the STOP_PROFILING
procedure returns CLOB, it contains the Real-Time Monitoring report for the profiler run.
Syntax
DBMS_HPROF.STOP_PROFILING; DBMS_HPROF.STOP_PROFILING RETURN CLOB;
Examples
Profiling with raw profiler data table
DECLARE analyze_runid number; trace_id number; BEGIN -- create raw profiler data and analysis tables -- call create_tables with force_it =>FALSE (default) when -- raw profiler data and analysis tables do not exist already DBMS_HPROF.CREATE_TABLES; -- Start profiling -- Write raw profiler data in raw profiler data table trace_id := DBMS_HPROF.START_PROFILING; -- Run the procedure to be profiled test; -- Stop profiling DBMS_HPROF.STOP_PROFILING; -- analyzes trace_id entry in raw profiler data table and writes -- hierarchical profiler information in hprof’s analysis tables analyze_runid := DBMS_HPROF.ANALYZE(trace_id); END; /