29
DBMS_PROFILER

Oracle8i provides a Probe Profiler API to profile existing PL/SQL applications and to identify performance bottlenecks. The collected profiler (performance) data can be used for performance improvement efforts or for determining code coverage for PL/SQL applications. Code coverage data can be used by application developers 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:

1. Exercising 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.

To support this process, the PL/SQL profiler supports the notion of a run. A run involves running the application through benchmark tests with profiler data collection enabled. You can control the beginning and the end of the run by calling the START_PROFILER and STOP_PROFILER functions.

A typical session involves:

• Starting profiler data collection in session.

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

• Stopping profiler data collection.

  Note: Stopping data collection flushes out the collected data.

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

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

See Also: "FLUSH_DATA function".

Alternately, the tables can be created in one centrally administered schema, and profiler users can get access to the tables by creating public synonyms and granting INSERT/SELECT privileges on the tables and sequence.

Note: The collected profiler data is not automatically flushed at the end of the session. You must issue an explicit call to the FLUSH_DATA or the STOP_PROFILER function to flush the data at the end of the session.

Some PL/SQL operations, such as the very 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, you should warm up the database before collecting profile data. Warming up involves running the application once without gathering profiler data.

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 the ad-hoc querying on the data: It lets you build customizable reports (summary reports, hottest lines, code coverage data, etc.) and analysis capabilities.

With Oracle8i, a sample textual report writer is provided with the PL/SQL demo scripts.

Requirements

DBMS_PROFILER must be installed as SYS.

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

Error Codes

A 0 return value from any function denotes successful completion; a non-zero return value denotes an error condition. The possible error returns are listed below:

• A subprogram was called with an incorrect parameter.

  error_param constant binary_integer := 1; 
      
   
           

• Data flush operation failed. Check to see if the profiler tables have been created, are accessible, and that there is adequate space.

  error_io    constant binary_integer := 2; 
      
   
           

• There is a mismatch between package and database implementation. You get this error if an incorrect version of the DBMS_PROFILER package is installed, and this version of the profiler package cannot work with this database version. The only possible recovery is to install the correct version of the package.

  error_version constant binary_integer := -1; 
  

Summary of Subprograms

Table 29-1 DBMS_PROFILER Package Subprograms

Subprogram	Description
START_PROFILER function	Starts profiler data collection in session.
STOP_PROFILER function	Stops profiler data collection in session.
FLUSH_DATA function	Flushes profiler data collected in session.
GET_VERSION procedure	Gets the version of this API.
INTERNAL_VERSION_CHECK function	Verifies that this version of the DBMS_PROFILER package can work with the implementation in the database.

START_PROFILER function

This function starts profiler data collection in session.

Syntax

DBMS_PROFILER.START_PROFILER ( 
   run_comment IN VARCHAR2 := sysdate) 
  RETURN BINARY_INTEGER; 

Parameters

Table 29-2 START_PROFILER Function Parameters

Parameter	Description
run_comment	Each profiler run can be associated with a comment. For example, the comment could provide the name and version of the benchmark test that was used to collect data.

STOP_PROFILER function

This function stops profiler data collection in session.

This function has the side effect of flushing data collected so far in the session, and it signals the end of a run.

Syntax

DBMS_PROFILER.STOP_PROFILER 
  RETURN BINARY_INTEGER; 

Parameters

None.

FLUSH_DATA function

This function flushes profiler data collected in session. The data is flushed to database tables, which are expected to pre-exist.

Note: Use the PROFTAB.SQL script to create the tables and other data structures required for persistently storing the profiler data.

Syntax

DBMS_PROFILER.FLUSH_DATA 
  RETURN BINARY_INTEGER; 

Parameters

None.

GET_VERSION procedure

This procedure gets the version of this API.

Syntax

DBMS_PROFILER.GET_VERSION ( 
   major OUT BINARY_INTEGER, 
   minor OUT BINARY_INTEGER); 

Parameters

Table 29-3 GET_VERSION Procedure Parameters

Parameter	Description
major	Major version of DBMS_PROFILER.
minor	Minor version of DBMS_PROFILER.

INTERNAL_VERSION_CHECK function

This function verifies that this version of the DBMS_PROFILER package can work with the implementation in the database.

Syntax

DBMS_PROFILER.INTERNAL_VERSION_CHECK 
  RETURN BINARY_INTEGER; 

Parameters

None.

---