Database Reference
In-Depth Information
Profiling PL/SQL Code
The database engine provides two profilers integrated in the PL/SQL engine for you to use in profiling PL/SQL code.
One is a line-level profiler managed through the
dbms_profiler
package. The other is a call-level profiler (also known
as
hierarchical profiler
) managed through the
dbms_hprof
package. Table
3-2
summarizes the main advantages of
each.
Table 3-2.
Main Advantages of DBMS_HPROF and DBMS_PROFILER
DBMS_HPROF
DBMS_PROFILER
Imposes very small overhead when enabled
Provides information at the call level
Has notion of both “self time” and “total time”
Doesn't require additional privileges
Supports native-compiled PL/SQL
Provides information at the line level
Available in releases before version 11.1
Supported by all major development tools
The hierarchical profiler provides runtime statistics that aren't just more precise than those from the
line-level profiler, but also more useful. The one exception is when you actually require information at the line level.
Hence, I recommend using the hierarchical profiler, if it's available, unless you have a specific need for the line-level
information provided by the other.
Using DBMS_HPROF
With the
dbms_hprof
package, introduced in version 11.1, you can enable and disable the hierarchical profiler at the
session level. While enabled, the following information is gathered for each PL/SQL and SQL call that's executed:
•
The total number of times the call is executed
•
The time spent processing the call
•
The time spent processing subcalls
•
Information about the call hierarchy
The gathering takes place at session level for all PL/SQL code (for example, in packages and triggers) that a user
is able to execute (a limitation is that wrapped PL/SQL code allows you to gather only information about the top-level
calls). You need only the
EXECUTE
privilege on the
dbms_hprof
package to enable this profiling.
The data gathered during the profiling is stored in a trace file at the operating-system level. Then, for analysis
purposes, the data can be either loaded into the database tables shown in Figure
3-4
, or it can be processed with the
PLSHPROF utility.
