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 in the following tables.

With Oracle database version 8.x, a sample textual report writer(profrep.sql) is provided with the PL/SQL demo scripts.

Note that prior to Oracle Database 10g, the DBMS_PROFILER package was not automatically loaded when the database was created, and the Oracle-supplied PROFLOAD.SQL script was used to create it. In 10g and beyond, the DBMS_PROFILER package is loaded automatically when the database is created, and PROFLOAD.SQL is no longer needed.

Typical Run

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.

The user must first create database tables in the profiler user's schema to collect the data. 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.

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 PROFTAB.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.

A typical run then 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 the database tables created earlier.

Interpreting Output

The table plsql_profiler_data contains one row for each line of the source unit for which code was generated. The line# value specifies which source line. If the row exists, and the total_occur value in that row is > 0, some code associated with that line was executed. If the row exists, and total_occur value is 0, no code associated with that line was executed. If the row doesn't exist in the table, no code was generated for that line, and therefore it should not be mentioned in reports

If the source of a single statement is on a single line, any code generated for that statement will be attributed to that line number. (In some cases, such as a simple declaration, or because of optimization, no code will be needed). To get coverage information, units should be compiled with PLSQL_OPTIMIZE_LEVEL=1.

If a statement spans multiple lines, any code generated for that statement will be attributed to some line in the range, but it is not guaranteed that every line in the range will have code attributed to it. In such a case there will be gaps in the set of line# values. In particular, multi-line SQL-related statements may appear to be on a single line (usually the first). This is because PL/SQL passes the processed text of the cursor to the SQL engine; therefore, as far as PL/SQL is concerned, the entire SQL statement is a single indivisible operation.

When multiple statements are on the same line, the profiler will combine the occurrences for each statement. This may be confusing if a line has embedded control flow. For example, if 'then ...' and 'else ...' are on the same line, it will not be possible to determine whether the 'then' or the 'else' was taken.

In general, profiler and coverage reports are most easily interpreted if each statement is on its own line.

Two Methods of Exception Generation

Each routine in this package has two versions that allow you to determine how errors are reported.

• A function that returns success/failure as a status value and will never raise an exception

• A procedure that returns normally if it succeeds and raises an exception if it fails

In each case, the parameters of the function and procedure are identical. Only the method by which errors are reported differs. If there is an error, there is a correspondence between the error codes that the functions return, and the exceptions that the procedures raise.

To avoid redundancy, the following section only provides details about the functional form.

A 0 return value from any function denotes successful completion; a nonzero return value denotes an error condition. The possible errors are as follows:

• 'A subprogram was called with an incorrect parameter.'

    error_param constant binary_integer := 1; 
 

• 'Data flush operation failed. Check whether 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. Oracle returns this error if an incorrect version of the DBMS_PROFILER package is installed, and if the version of the profiler package cannot work with this database version. The only recovery is to install the correct version of the package.

    error_version constant binary_integer := -1;