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 or stored procedure Type Return value type Description
FLUSH_DATA Function or stored procedure Status code or exception Flushes performance data collected in the current session.
GET_VERSION(major OUT, minor OUT) Stored procedure N/A Returns the version of the DBMS_PROFILER package.
INTERNAL_VERSION_ CHECK Function Status code Confirms that the current version of the DBMS_PROFILER package is compatible with the current database.
PAUSE_PROFILER Function or stored procedure Status code or exception Suspends the profiler session.
RESUME_PROFILER Function or stored procedure Status code or exception Resumes the profiler session.
START_PROFILER(run comment, run commentl [, run number OUT ]) Function or stored procedure Status code or exception Starts the profiler session.
STOP_PROFILER Function or stored procedure Status code or exception Stops the profiler session and flushes performance data to the DBMS_PROFILER tables and views.

The functions within the DBMS_PROFILER package return a status code to indicate success or failure. The stored procedures within the DBMS_PROFILER package throw an exception only if they encounter a failure. The following table lists the status codes and messages returned by the functions, and the exceptions thrown 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 error message returned because the format of the specified parameter is invalid.
2 error_io profiler_error The flush operation fails.

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 PolarDB Performance Guide. Syntax:
status INTEGER FLUSH_DATA

FLUSH_DATA
Parameters
Parameter Description
status The returned status code.

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
Parameter Description
major The major version of the DBMS_PROFILER package.
minor The minor version of the DBMS_PROFILER package.

INTERNAL_VERSION_CHECK

The INTERNAL_VERSION_CHECK function confirms that the current version of the DBMS_PROFILER package is compatible with the current database. Syntax:
status INTEGER INTERNAL_VERSION_CHECK
Parameters
Parameter Description
status The returned status code.

PAUSE_PROFILER

The PAUSE_PROFILER function or stored procedure suspends a profiler session. Syntax:
status INTEGER PAUSE_PROFILER

PAUSE_PROFILER
Parameters
Parameter Description
status The returned status code.

RESUME_PROFILER

The RESUME_PROFILER function or stored procedure resumes a profiler session. Syntax:
status INTEGER RESUME_PROFILER

RESUME_PROFILER
Parameters
Parameter Description
status The returned status code.

START_PROFILER

The START_PROFILER function or stored procedure starts a profiler 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
Parameter Description
run comment A custom comment for the profiler session. The default value is SYSDATE.
run commentl An additional custom comment for the profiler session. The default value is TT.
run number The number of profiler sessions.
status The returned status code.

STOP_PROFILER

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

STOP_PROFILER
Parameters
Parameter Description
status The returned status code.