The DBMS_PROFILER package records the number of executions and execution duration of each code line in PL executions, and summarizes the information.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Privileges
You must have the AUTHID CURRENT_USER privilege to call this package.
Subprograms
The following table describes the DBMS_PROFILER subprograms supported by the current OceanBase Database version.
| Subprogram | Description |
|---|---|
| start_profiler | Starts the profiler. Then, the profiler will record the PL execution information in the current session. |
| stop_profiler | Stops the profiler. Then, the profiler will flush the information to the data statistical table. |
| pause_profiler | Pauses the profiler. Then, the profiler will stop recording execution information. |
| resume_profiler | Resumes the profiler. Then, the profiler will resume recording execution information. |
| flush_data | Flushes the collected data to the data statistical table. |
| get_version | Returns the current DBMS_PROFILER version. |
| internal_version_check | Verifies whether the database version matches the DBMS_PROFILER version. |
| rollup_unit | Calculates the execution duration of a specific unit during an execution. |
| rollup_run | Calculates the execution duration of each unit during an execution. |
| ob_init_objects | Creates a data statistical table under the schema user. |
| ob_drop_objects | Drops a data statistical table from the schema user. |
Call procedure
Call DBMS_PROFILER.start_profiler to start the profiler. Then, the profiler records the number of executions and execution duration of each statement in all PL executions in the current session. If system warm-up is required in some scenarios, you can call DBMS_PROFILER.pause_profiler to pause the profiler to prevent the warm-up process from being recorded. After the warm-up is completed, you can call DBMS_PROFILER.resume_profiler to resume the profiler. After performance analysis PL statements are executed, you can call DBMS_PROFILER.stop_profiler to stop the profiler. Then, all recorded information will be flushed to the data statistical table. You can query the table for performance analysis.