DBMS_PROFILER

DBMS_PROFILER collects and stores performance data for PL/pgSQL and SPL statements executed during a profiling session. Use it to identify slow lines of code in stored procedures and functions.

How it works

Performance profiling with DBMS_PROFILER follows an iterative cycle:

Start a profiler session with START_PROFILER.
Run the PL/pgSQL or SPL code to profile.
Stop the session with STOP_PROFILER, which flushes all collected data to the DBMS_PROFILER tables and views.
Query the result tables described in the PolarDB Performance Guide to identify hot spots.
Fix the problems and repeat.

Error reporting modes

Each routine in DBMS_PROFILER is available in two forms:

Function: Returns a status code (INTEGER) on both success and failure. Use this form when you want to handle errors in your own control flow.
Stored procedure: Returns normally on success and raises an exception on failure. Use this form when you want errors surfaced automatically.

The parameters are identical between the two forms — only the error-reporting mechanism differs.

Functions and stored procedures

Function or stored procedure	Type	Return value	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 installed version of `DBMS_PROFILER` 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_comment1 [, 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.

Status codes and exceptions

Functions return a status code. Stored procedures raise an exception only on failure.

Status code	Message	Exception	Description
-1	error version	version_mismatch	The profiler version and the database are incompatible.
0	success	N/A	The operation succeeded.
1	error_param	profiler_error	The specified parameter has an invalid format.
2	error_io	profiler_error	The flush operation failed.

FLUSH_DATA

Flushes performance data collected in the current session without ending the session. Data is written to the tables described in the PolarDB Performance Guide.

Syntax

-- Function form
status INTEGER FLUSH_DATA

-- Stored procedure form
FLUSH_DATA

Parameters

Parameter	Description
`status`	The returned status code.

GET_VERSION

Returns the major and minor version numbers 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

Confirms that the installed version of DBMS_PROFILER is compatible with the current database. Returns status code 0 on success or -1 (version_mismatch) if the versions are incompatible.

Syntax

status INTEGER INTERNAL_VERSION_CHECK

Parameters

Parameter	Description
`status`	The returned status code.

PAUSE_PROFILER

Suspends data collection in the current profiler session without ending it. Resume collection with RESUME_PROFILER.

Syntax

-- Function form
status INTEGER PAUSE_PROFILER

-- Stored procedure form
PAUSE_PROFILER

Parameters

Parameter	Description
`status`	The returned status code.

RESUME_PROFILER

Resumes data collection in a previously suspended profiler session.

Syntax

-- Function form
status INTEGER RESUME_PROFILER

-- Stored procedure form
RESUME_PROFILER

Parameters

Parameter	Description
`status`	The returned status code.

START_PROFILER

Starts a profiler session. Optionally attach comments to identify the run in the result tables.

Syntax

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

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

Parameters

Parameter	Description
`run_comment`	A label for the profiler session. Default: `SYSDATE`.
`run_comment1`	An additional label for the profiler session. Default: `''` (empty string).
`run_number`	The number of profiler sessions.
`status`	The returned status code (function form only).

STOP_PROFILER

Stops the profiler session and flushes all collected performance data to the DBMS_PROFILER tables and views.

Syntax

-- Function form
status INTEGER STOP_PROFILER

-- Stored procedure form
STOP_PROFILER

Parameters

Parameter	Description
`status`	The returned status code.