Overview

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

Privilege requirements

The privilege for this 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.

Calling process

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 phase, 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, call DBMS_PROFILER.resume_profiler to resume recording. After the PL/SQL execution that needs performance analysis is completed, 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.

Examples

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 retrieve 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 view detailed performance statistics for each line within a PL/SQL unit, including the number of times the line was executed, total execution time, and minimum and maximum execution times.