Overview

The DBMS_PROFILER system package records the execution of each line of PL/SQL code and summarizes the results to provide detailed information about the execution time of each statement during the PL/SQL execution.

DBMS_PROFILER privileges

The system package privilege is AUTHID CURRENT_USER.

Subprograms

The following table describes the DBMS_PROFILER subprograms supported in the current version of OceanBase Database and their brief descriptions.

Procedure

Call the DBMS_PROFILER.start_profiler procedure to start the profiler. After the profiler is started, the number of executions and the execution time of each line of PL statements in the current session are recorded. If the system requires a warm-up period, you can call the DBMS_PROFILER.pause_profiler procedure to pause the profiler to prevent the warm-up period from being recorded. After the warm-up period is completed, call the DBMS_PROFILER.resume_profiler procedure to start recording execution details. After the PL statements that need to be analyzed for performance are executed, call the DBMS_PROFILER.stop_profiler procedure to stop the profiler. All recorded data is aggregated into the statistics table. You can query the statistics table to analyze the performance.

Examples

1. Create a stored procedure named PROC1.

   obclient> CREATE OR REPLACE PROCEDURE PROC1 AS
       -> BEGIN
       -> NULL;
       -> END;
       -> /
   Query OK, 0 rows affected (0.057 sec)
   

2. Create a stored procedure named PROC0:

   obclient> CREATE OR REPLACE PROCEDURE PROC0 AS
       -> BEGIN
       -> PROC1();
       -> END;
       -> /
   Query OK, 0 rows affected (0.103 sec)
   

3. Start the profiler:

   obclient> CALL dbms_profiler.start_profiler();
   Query OK, 0 rows affected (1.178 sec)
   

4. Call the PROC0 stored procedure:

   obclient> CALL PROC0();
   Query OK, 0 rows affected (0.470 sec)
   

   Execute the PROC0 stored procedure. Since the profiler is started, data of this call is collected for later analysis.

5. Stop the profiler:

   call DBMS_PROFILER.stop_profiler();
   Query OK, 0 rows affected (1.238 sec)
   

6. View the runtime information of the profiler:

   obclient> SELECT * FROM PLSQL_PROFILER_RUNS;
   +-------+-------------+-----------+-----------+-------------+----------------+-----------------+--------------+--------+
   | RUNID | RELATED_RUN | RUN_OWNER | RUN_DATE  | RUN_COMMENT | RUN_TOTAL_TIME | RUN_SYSTEM_INFO | RUN_COMMENT1 | SPARE1 |
   +-------+-------------+-----------+-----------+-------------+----------------+-----------------+--------------+--------+
   |     1 |        NULL | SYS       | 10-JAN-25 | 10-JAN-25   |    34000000000 | NULL            | NULL         | NULL   |
   +-------+-------------+-----------+-----------+-------------+----------------+-----------------+--------------+--------+
   1 row in set (0.002 sec)
   

7. View information about the analyzed units:

   obclient> SELECT * FROM PLSQL_PROFILER_UNITS;
   +-------+-------------+-----------+------------+-----------+----------------+------------+--------+--------+
   | RUNID | UNIT_NUMBER | UNIT_TYPE | UNIT_OWNER | UNIT_NAME | UNIT_TIMESTAMP | TOTAL_TIME | SPARE1 | SPARE2 |
   +-------+-------------+-----------+------------+-----------+----------------+------------+--------+--------+
   |     1 |      500025 | PROCEDURE | SYS        | PROCL     | 10-JAN-25      |          0 |   NULL |   NULL |
   |     1 |      500026 | PROCEDURE | SYS        | PROC0     | 10-JAN-25      |          0 |   NULL |   NULL |
   +-------+-------------+-----------+------------+-----------+----------------+------------+--------+--------+
   2 rows in set (0.004 sec)
   

   Query the PLSQL_PROFILER_UNITS table for information about each PL/SQL unit, such as the unit type, name, and total execution time.

8. View the line-level performance data:

   obclient> SELECT * FROM PLSQL_PROFILER_DATA;
   +-------+-------------+-------+-------------+------------+----------+----------+--------+--------+--------+--------+
   | RUNID | UNIT_NUMBER | LINE# | TOTAL_OCCUR | TOTAL_TIME | MIN_TIME | MAX_TIME | SPARE1 | SPARE2 | SPARE3 | SPARE4 |
   +-------+-------------+-------+-------------+------------+----------+----------+--------+--------+--------+--------+
   |     1 |      500025 |     1 |           1 |        537 |      537 |      537 |   NULL |   NULL |   NULL |   NULL |
   |     1 |      500025 |     3 |           1 |       7118 |     7118 |     7118 |   NULL |   NULL |   NULL |   NULL |
   |     1 |      500026 |     1 |           1 |        406 |      406 |      406 |   NULL |   NULL |   NULL |   NULL |
   |     1 |      500026 |     3 |           1 |   16031017 | 16031017 | 16031017 |   NULL |   NULL |   NULL |   NULL |
   +-------+-------------+-------+-------------+------------+----------+----------+--------+--------+--------+--------+
   4 rows in set (0.002 sec)
   

   Query the PLSQL_PROFILER_DATA table for detailed performance statistics of specific lines within each PL/SQL unit. The statistics include the number of times the line was executed, the total execution time, and the minimum and maximum execution times.