The DBMS_PROFILER package collects and stores the performance information about PL/pgSQL and SPL statements that are executed during a performance profiling session.

The following table lists functions and stored procedures that can be used to control the profiling tool.

Table 1. DBMS_PROFILER functions and stored procedures
Function/stored procedure Function or stored 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 Pauses data collection.
PAUSE_PROFILER Both Status code or exception Resumes data collection.
START PROFILER(run_comment, run_comment1 [, run_number OUT ]) Both Status code or exception Starts data collection.
STOP PROFILER Both Status code or exception Stops 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 stored procedures within the DBMS_PROFILER package raise an exception only if they encounter a failure. The following table lists the status codes and messages returned by the functions, and the exceptions raised by the stored procedures.

Table 2. 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 is successful.
1 error_param profiler_error The operation received an incorrect parameter.
2 error_io profiler_error The data flush operation has failed.

FLUSH_DATA

The FLUSH_DATA function or stored procedure flushes the data collected in the current session without terminating the profiler session. The data is flushed to the tables described in the PolarDB-O Performance Features Guide. The syntax for FLUSH_DATA functions and stored procedures is as follows: 

status INTEGER FLUSH_DATA

FLUSH_DATA

Parameters

Table 3.
Parameter Description
status The status code returned by the operation.

GET_VERSION

The GET_VERSION stored procedure returns the version of the DBMS_PROFILER package. Syntax: 

GET_VERSION(major OUT INTEGER, minor OUT INTEGER)

Parameters

Table 4.
Parameter Description
major The major version number of the DBMS_PROFILER package.
minor The minor version number of the DBMS_PROFILER package.

INTERNAL_VERSION_CHECK

The INTERNAL_VERSION_CHECK function confirms that the current version of the DBMS_PROFILER package will work with the current database. The syntax of the INTERNAL_VERSION_CHECK function is as follows: 

status INTEGER INTERNAL_VERSION_CHECK

Parameters

Table 5.
Parameter Description
status The status code returned by the operation.

PAUSE_PROFILER

The PAUSE_PROFILER function or stored procedure pauses a profiling session. Syntax:

status INTEGER PAUSE_PROFILER

PAUSE_PROFILER

Parameters

Table 6.
Parameter Description
status The status code returned by the operation.

RESUME_PROFILER

The RESUME_PROFILER function or stored procedure resumes a profiling session. The syntax of the RESUME_PROFILER function or stored procedure is as follows: 

status INTEGER RESUME_PROFILER

RESUME_PROFILER

Parameters

Table 7.
Parameter Description
status The status code returned by the operation.

START_PROFILER

The START_PROFILER function or stored procedure starts a data collection session. Syntax: 

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

Table 8.
Parameter Description
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 The status code returned by the operation.

STOP_PROFILER

The STOP_PROFILER function or stored procedure stops a profiling session and flushes the performance information to the DBMS_PROFILER tables and views. Syntax: 

status INTEGER STOP_PROFILER

STOP_PROFILER

Parameters

Table 9.
Parameter Description
status The status code returned by the operation.