Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 7.6 DBMS_PROFILER

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

7.6 DBMS_PROFILER

The DBMS_PROFILER package collects and stores performance information about the PL/pgSQL and SPL statements that are executed during a performance profiling session; use the functions and procedures listed below to control the profiling tool.

For more information about the DBMS_PROFILER built-in package (including usage examples and a reference guide to the DBMS_PROFILER tables and views), see the Postgres Plus Advanced Server Performance Features Guide.

Table 7-11 DBMS_PROFILER Functions/Procedures

Function/Procedure

Function or Procedure

Return Type

Description

FLUSH_DATA

Both

Status Code or Exception

Flushes performance data collected in the current session without terminating the session (profiling continues).

GET_VERSION(major OUT, minor OUT)

Procedure

n/a

Returns the version number of this package.

INTERNAL_VERSION_CHECK

Function

Status Code

Confirms that the current version of the profiler will work with the current database.

PAUSE_PROFILER

Both

Status Code or Exception

Pause data collection.

RESUME_PROFILER

Both

Status Code or Exception

Resume data collection.

START_PROFILER(run_comment, run_comment1 [, run_number OUT ])

Both

Status Code or Exception

Start data collection.

STOP_PROFILER

Both

Status Code or Exception

Stop data collection and flush performance data to the PLSQL_PROFILER_RAWDATA table.

The functions within the DBMS_PROFILER package return a status code to indicate success or failure; the DBMS_PROFILER procedures raise an exception only if they encounter a failure. The status codes and messages returned by the functions, and the exceptions raised by the procedures are listed in the table below.

Table 7-12 DBMS_PROFILER Status Codes and Exceptions

Status Code

Message

Exception

Description

-1

error version

version_mismatch

The profiler version and the database are incompatible.

0

success

n/a

The operation completed successfully.

1

error_param

profiler_error

The operation received an incorrect parameter.

2

error_io

profiler_error

The data flush operation has failed.

7.6.1 FLUSH_DATA

The FLUSH_DATA function/procedure flushes the data collected in the current session without terminating the profiler session. The data is flushed to the tables described in the Postgres Plus Advanced Server Performance Features Guide. The function and procedure signatures are:

status INTEGER FLUSH_DATA

FLUSH_DATA

Parameters

status

Status code returned by the operation.

7.6.2 GET_VERSION

The GET_VERSION procedure returns the version of DBMS_PROFILER. The procedure signature is:

GET_VERSION(major OUT INTEGER, minor OUT INTEGER)

Parameters

major

The major version number of DBMS_PROFILER.

minor

The minor version number of DBMS_PROFILER.

7.6.3 INTERNAL_VERSION_CHECK

The INTERNAL_VERSION_CHECK function confirms that the current version of DBMS_PROFILER will work with the current database. The function signature is:

status INTEGER INTERNAL_VERSION_CHECK

Parameters

status

Status code returned by the operation.

7.6.4 PAUSE_PROFILER

The PAUSE_PROFILER function/procedure pauses a profiling session. The function and procedure signatures are:

status INTEGER PAUSE_PROFILER

PAUSE_PROFILER

Parameters

status

Status code returned by the operation.

7.6.5 RESUME_PROFILER

The RESUME_PROFILER function/procedure pauses a profiling session. The function and procedure signatures are:

status INTEGER RESUME_PROFILER

RESUME_PROFILER

Parameters

status

Status code returned by the operation.

7.6.6 START_PROFILER

The START_PROFILER function/procedure starts a data collection session. The function and procedure signatures are:

status INTEGER START_PROFILER(run_comment TEXT := SYSDATE,
  run_comment1 TEXT := '' [, run_number OUT INTEGER ])

START_PROFILER(run_comment TEXT := SYSDATE,
  run_comment1 TEXT := '' [, run_number OUT INTEGER ])

Parameters

run_comment

A user-defined comment for the profiler session. The default value is SYSDATE.

run_comment1

An additional user-defined comment for the profiler session. The default value is ''.

run_number

The session number of the profiler session.

status

Status code returned by the operation.

7.6.7 STOP_PROFILER

The STOP_PROFILER function/procedure stops a profiling session and flushes the performance information to the DBMS_PROFILER tables and view. The function and procedure signatures are:

status INTEGER STOP_PROFILER

STOP_PROFILER

Parameters

status

Status code returned by the operation.

Previous PageTable Of ContentsNext Page