DBMS_PROFILER , 2 of 2

Summary of Subprograms

This section summarizes the subprograms of DBMS_PROFILER.

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.

Exceptions

Table 32-4 shows the exceptions for DBMS_PROFILER.

Table 32-4

Exception Description

version_mismatch

Corresponds to error_version.

profiler_error

Corresponds to either "error_param" or "error_io".

DBMS_PROFILER Exceptions

Exception	Description
version_mismatch	Corresponds to error_version.
profiler_error	Corresponds to either "error_param" or "error_io".

Error Codes

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

'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;

Table 32-5 DBMS_PROFILER Package Subprograms

Subprogram	Description
START_PROFILER Function	Starts profiler data collection in the user's session.
STOP_PROFILER Function	Stops profiler data collection in the user's session.
FLUSH_DATA Function	Flushes profiler data collected in the user's session.
PAUSE_PROFILER Function	Pauses profiler data collection.
RESUME_PROFILER Function	Resumes profiler data collection.
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 the user's session.

Syntax

There are two overloaded forms of the START_PROFILER function; one returns the run number of the started run, as well as the result of the call. The other does not return the run number. The first form is intended for use with GUI-based tools controlling the profiler.

The first form is:

DBMS_PROFILER.START_PROFILER(run_comment IN VARCHAR2 := sysdate,
run_comment1 IN VARCHAR2 :='',
run_number OUT BINARY_INTEGER)
RETURN BINARY_INTEGER;

The second form is:

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

Parameters

Table 32-6 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.
run_number	Stores the number of the run so you can store and later recall the run's data.
comment1	Allows you to make interesting comments about the run.

STOP_PROFILER Function

This function stops profiler data collection in the user's 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 the user's 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.

PAUSE_PROFILER Function

This function pauses profiler data collection.

RESUME_PROFILER Function

This function resumes profiler data collection.

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