Overview of DBMS_PROFILER

The DBMS_PROFILER package is used to record the execution of each line in PL/SQL and summarize the results to provide detailed execution time for each statement during PL/SQL execution.

Privilege requirements

The privilege of 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 DBMS_PROFILER.start_profiler to start the profiler. After this, the execution count and time of each line in all PL/SQL executions in the session will be recorded. If the system requires a warm-up phase, you can call DBMS_PROFILER.pause_profiler to pause the profiler to prevent the warm-up data from being recorded. After the warm-up is complete, call DBMS_PROFILER.resume_profiler to resume recording. When the PL/SQL execution that needs performance analysis is completed, call DBMS_PROFILER.stop_profiler to stop the profiler. The recorded data will be summarized into the statistics table, which can then be queried for performance analysis.

Example

1. Create the PROC1 stored procedure.

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

2. Create the PROC0 stored procedure:

   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 enabled, the data from this call will be collected for subsequent analysis.

5. Stop the profiler:

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

6. View the profiler run information:

   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 the information of 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 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;
   +-------+-------------+-------+-------------+------------+----------+----------+--------+--------+--------+--------+
   | 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 specific lines within each PL/SQL unit, including the number of times the line was executed, total execution time, and minimum and maximum execution times.