32
DBMS_PROFILER

Oracle8i provides a Profiler API to profile existing PL/SQL applications and to identify performance bottlenecks. You can use the collected profiler (performance) 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.

The profiler API is implemented as a PL/SQL package, DBMS_PROFILER, that provides services for collecting and persistently storing PL/SQL profiler data.

Using DBMS_PROFILER

Improving application performance is an iterative process. Each iteration involves the following steps:

1. Running the application with one or more benchmark tests with profiler data collection enabled.

2. Analyzing the profiler data and identifying performance problems.

3. Fixing the problems.

The PL/SQL profiler supports this process using the concept of a "run". A run involves running the application through benchmark tests with profiler data collection enabled. You can control the beginning and the ending of a run by calling the START_PROFILER and STOP_PROFILER functions.

A typical run involves:

• Starting profiler data collection in the run.

• Executing PL/SQL code for which profiler and code coverage data is required.

• Stopping profiler data collection, which writes the collected data for the run into database tables

  Note: The collected profiler data is not automatically stored when the user disconnects. You must issue an explicit call to the FLUSH_DATA or the STOP_PROFILER function to store the data at the end of the session. Stopping data collection stores the collected data.

As the application executes, profiler data is collected in memory data structures that last for the duration of the run. You can call the FLUSH_DATA function at intermediate points during the run to get incremental data and to free memory for allocated profiler data structures.

Flushing the collected data involves storing collected data in database tables. The tables should already exist in the profiler user's schema. The PROFTAB.SQL script creates the tables and other data structures required for persistently storing the profiler data.

Note that running PROFTAB.SQL drops the current tables. The PROFTAB.SQL script is in the RDBMS/ADMIN directory. Some PL/SQL operations, such as the first execution of a PL/SQL unit, may involve I/O to catalog tables to load the byte code for the PL/SQL unit being executed. Also, it may take some time executing package initialization code the first time a package procedure or function is called.

To avoid timing this overhead, "warm up" the database before collecting profile data. To do this, run the application once without gathering profiler data.

System-wide Profiling

You can allow profiling across all users of a system, for example, to profile all users of a package, independent of who is using it. In such cases, the SYSADMIN should use a modified PROFLOAD.SQL script which:

• Creates the profiler tables and sequence

• Grants SELECT/INSERT/UPDATE on those tables and sequence to all users

• Defines public synonyms for the tables and sequence

  Note: Do not alter the actual fields of the tables.

  See Also: "FLUSH_DATA Function".

Requirements

DBMS_PROFILER must be installed as SYS.

Use the PROFLOAD.SQL script to load the PL/SQL Profiler packages.

Collected Data

With the Probe Profiler API, 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 that 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.

Note: It is possible to infer the code coverage figures for PL/SQL units for which data has been collected.

The profiling information is stored in database tables. This enables ad-hoc querying on the data: you can build customizable reports (summary reports, hottest lines, code coverage data, and so on. It also allows you to analyze the data.

PROFTAB.SQL

The PROFTAB.SQL script creates tables with the columns, datatypes, and definitions as shown in Table 32-1, Table 32-2, and Table 32-3.

Table PLSQL_PROFILER_RUNS

Table 32-1 Columns in Table PLSQL_PROFILER_RUNS

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

Table PLSQL_PROFILER_UNITS

Table 32-2 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

Table PLSQL_PROFILER_DATA

Table 32-3 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

With Oracle8, a sample textual report writer(profrep.sql) is provided with the PL/SQL demo scripts.

Security Considerations

The profiler only gathers data for units for which a user has CREATE privilege; you cannot use the package to profile units for which EXECUTE ONLY access has been granted. In general, if a user can debug a unit, the same user can profile it. However, a unit can be profiled whether or not it has been compiled DEBUG. Oracle advises that modules that are being profiled should be compiled DEBUG, since this provides additional information about the unit in the database