Overview of DBMS_PROFILER

The DBMS_PROFILER system package records the execution of each line in PL/SQL and aggregates the results to provide detailed execution time for each statement in the PL/SQL execution process.

Privilege requirements

The privilege for this system package is AUTHID CURRENT_USER.

Subprograms

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

Call flow

Call the DBMS_PROFILER.start_profiler procedure to start the profiler. After this, the execution count and time of each line in all PL/SQL executions within the session will be recorded. If the system requires a warm-up period, you can use DBMS_PROFILER.pause_profiler to pause the profiler to prevent the warm-up from being recorded. Once the warm-up is complete, use DBMS_PROFILER.resume_profiler to resume recording. After the PL/SQL execution that needs performance analysis is complete, call DBMS_PROFILER.stop_profiler to stop the profiler. All recorded data will be aggregated into the statistics table, which can then be queried for performance analysis.

Here is a diagram:

Example

1. Create the PROC1 stored procedure.

   obclient> CREATE OR REPLACE PROCEDURE PROC1 AS
   BEGIN
       NULL;
   END;
   /
   

2. Create the PROC0 stored procedure.

   obclient> CREATE OR REPLACE PROCEDURE PROC0 AS
   BEGIN
       PROC1();
   END;
   /
   

3. Start the profiler.

   obclient> CALL DBMS_PROFILER.start_profiler();
   

4. Call the PROC0 stored procedure.

   obclient> CALL PROC0();
   

   Execute the PROC0 stored procedure. Since the profiler is enabled, the data from this call will be collected for future analysis.

5. Stop the profiler.

   obclient> CALL DBMS_PROFILER.stop_profiler();
   

6. View the profiler run information.

   obclient> SELECT * FROM PLSQL_PROFILER_RUNS;
   

   Execution result:

   +-------+-------------+-----------+-----------+-------------+----------------+-----------------+--------------+--------+
   | 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 the information of the analyzed units.

   obclient> SELECT * FROM PLSQL_PROFILER_UNITS;
   

   Execution result:

   +-------+-------------+-----------+------------+-----------+----------------+------------+--------+--------+
   | 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 to obtain information about each analyzed PL/SQL unit, including the unit type, name, and total time.

8. View the performance data at the line level.

   obclient> SELECT * FROM PLSQL_PROFILER_DATA;
   

   Execution result:

   +-------+-------------+-------+-------------+------------+----------+----------+--------+--------+--------+--------+
   | 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 to obtain detailed performance statistics for specific lines within each PL/SQL unit, including the number of executions, total execution time, and minimum and maximum execution times.