Database Reference
In-Depth Information
Figure 3-8.
Profiling data of the call hierarchy displayed in SQL Developer
Using DBMS_PROFILER
With the
dbms_profiler
package you can enable and disable the line-level profiler at the session level. While enabled,
the following information is gathered for each line of code that's executed:
•
The total number of times it's executed
•
The total amount of time that's spent executing it
•
The minimum and maximum amount of time that's spent executing it
The gathering takes place at session level for all PL/SQL code that's neither wrapped nor natively compiled,
and for which the user has the
CREATE
privilege. In other words, the privilege to execute a piece of PL/SQL code isn't
enough to use the profiler. Therefore, in practice, the profiling is either done by the owner of the objects to be profiled,
or by a user with the
CREATE ANY
privilege.
The profiling data is stored in the database tables shown in Figure
3-9
. The
plsql_profiler_runs
table gives
information about which profiling sessions have been performed. The
plsql_profiler_units
table provides the list
of units that have been executed for each run. And the
plsql_profiler_data
table gives the profiling data, described
earlier, for each line of code that has been executed.