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