Oracle PL/SQL‎ > ‎

Profiling and Tracing PL/SQL Programs

posted Sep 26, 2010, 7:50 AM by Sachchida Ojha   [ updated Sep 26, 2010, 11:28 AM ]
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


Comments