The Profiler API is implemented as PL/SQL package DBMS_PROFILER, which provides services for gathering and saving run-time statistics. The information is stored in database tables, which you can query later. For example, you can learn how much time was spent executing each PL/SQL line and subprogram.
To use the Profiler, you start the profiling session, run your application long enough to get adequate code coverage, flush the collected data to the database, then stop the profiling session.
The Profiler traces the execution of your program, computing the time spent at each line and in each subprogram. You can use the collected data to improve performance. For instance, you might focus on subprograms that run slowly.
This package enables the collection of profiler
(perfoprmance) data for performance improvement or for determining code
coverage for PL/SQL applications. Application developers can use code
coverage data to focus their incremental testing efforts.
With this interface, you can generate profiling
information for all named library units that are executed in a session.
The profiler gathers information at the PL/SQL virtual machine level.
This information includes the total number of times each line has been
executed, the total amount of time that has been spent executing that
line, and the minimum and maximum times that have been spent on a
particular execution of that line.
The profiling information is stored in database tables.
This enables querying on the data: you can build customizable reports
(summary reports, hottest lines, code coverage data, and so on. And you
can analyze the data.
The
PROFTAB.SQL script creates tables with the columns, datatypes, and definitions as shown below.
| Column |
Datatype |
Definition |
|---|
|
runid
|
NUMBER PRIMARY KEY
|
Unique run identifier from plsql_profiler_runnumber |
|
related_run
|
NUMBER
|
Runid of related run (for client/server correlation) |
|
run_owner
|
VARCHAR2(32),
|
User who started run |
|
run_date
|
DATE
|
Start time of run |
|
run_comment
|
VARCHAR2(2047)
|
User provided comment for this run |
|
run_total_time
|
NUMBER
|
Elapsed time for this run in nanoseconds |
|
run_system_info
|
VARCHAR2(2047)
|
Currently unused |
|
run_comment1
|
VARCHAR2(2047)
|
Additional comment |
|
spare1
|
VARCHAR2(256)
|
Unused |
Columns in Table PLSQL_PROFILER_UNITS
| Column |
Datatype |
Definition |
|
runid
|
NUMBER
|
Primary key, references plsql_profiler_runs, |
|
unit_number
|
NUMBER
|
Primary key, internally generated library unit # |
|
unit_type
|
VARCHAR2(32)
|
Library unit type |
|
unit_owner
|
VARCHAR2(32)
|
Library unit owner name |
|
unit_name
|
VARCHAR2(32)
|
Library unit name timestamp on library unit |
|
unit_timestamp
|
DATE
|
In the future will be used to detect changes to unit between runs |
|
total_time
|
NUMBER
|
Total time spent in this unit in nanoseconds. The profiler
does not set this field, but it is provided for the convenience of
analysis tools. |
|
spare1
|
NUMBER
|
Unused |
|
spare2
|
NUMBER
|
Unused |
Columns in Table PLSQL_PROFILER_DATA
| Column |
Datatype |
Definition |
|
runid |
NUMBER
|
Primary key, unique (generated) run identifier |
|
unit_number |
NUMBER
|
Primary key, internally generated library unit number |
|
line# |
NUMBER
|
Primary key, not null, line number in unit |
|
total_occur |
NUMBER
|
Number of times line was executed |
|
total_time |
NUMBER
|
Total time spent executing line in nanoseconds |
|
min_time
|
NUMBER
|
Minimum execution time for this line in nanoseconds |
|
max_time
|
NUMBER
|
Maximum execution time for this line in nanoseconds |
|
spare1
|
NUMBER
|
Unused |
|
spare2
|
NUMBER
|
Unused |
|
spare3
|
NUMBER
|
Unused |
|
spare4
|
NUMBER
|
Unused |
