Profile PL/SQL Performance with polar_plsql_profiler - PolarDB

Finding performance bottlenecks in complex PL/SQL functions or stored procedures can be challenging. A flat timer tells you that calculate_tax() took 1.3 ms — but not which lines drove that time or whether the cost came from a nested sub-function. The polar_plsql_profiler extension attaches to the PL/SQL execution engine to collect runtime statistics at the line level: the number of executions and total execution time for each line, plus the call hierarchy and time distribution across functions. The collected data is stored in the current session's memory. Call the provided functions to view results as structured data or export them as an HTML report.

Profiling is an iterative process: enable the profiler, run your code, analyze the output, fix the bottleneck, and repeat.

Key capabilities

Line-level code analysis: Pinpoints the number of executions and the execution time for each line of source code to quickly locate hot spots.
Call graph: Clearly shows the call relationships between functions, helping you understand how the total execution time is distributed across different function modules.
Visual reports: Generates detailed HTML reports with a single command for easy analysis and sharing.

Prerequisites

Before you begin, ensure that you have:

PolarDB for PostgreSQL (Compatible with Oracle) with Oracle syntax compatibility 2.0
Minor engine version 2.0.14.19.40.0 or later

To check your minor engine version, run SHOW polardb_version; or view it in the console. If your version does not meet the requirement, upgrade the minor engine version.

Usage notes

Performance overhead: Enabling the profiler adds runtime overhead. Use it only for development or performance diagnostics, not for continuous monitoring in a production environment.
Session-level data: All collected data is stored in the current session's memory. Data is lost when the session disconnects. Run the profiler, collect data, and analyze results — all within a single session.
Clean up after analysis: After you finish, disable the profiler and reset the data to free resources and remove the overhead from subsequent operations.

Quick start

This example walks through a basic profiling workflow on a simple function.

Step 1: Install the extension

-- Create the extension if it does not already exist.
CREATE EXTENSION polar_plsql_profiler;

Step 2: Prepare the target function

-- Create a function for testing.
CREATE OR REPLACE FUNCTION simple_test_func(p_times IN NUMBER)
RETURNS VARCHAR2 AS
    v_result VARCHAR2(100);
BEGIN
    FOR i IN 1 .. p_times LOOP
        v_result := 'Iteration ' || i;
    END LOOP;
    RETURN v_result;
END;

Step 3: Enable the profiler

SELECT plsql_profiler_enable();

Returns t if the profiler is enabled successfully.

Step 4: Execute the target code

SELECT simple_test_func(100);

Step 5: View the results

Function call overview

Use plsql_profiler_show_funclist() to see overall statistics for all functions called during the session. Focus on us_total to find the slowest functions. Compare us_self against us_children to determine whether the cost is in the function itself or in its callees — a high us_children value means the bottleneck is in a sub-function, not in this function's own code.

SELECT * FROM plsql_profiler_show_funclist();

Example output:

              func_call               | func_oid_list | call_count | us_total | us_declare | us_children | us_self |          time_stamp
--------------------------------------+---------------+------------+----------+------------+-------------+---------+-------------------------------
 oid=21749  public.simple_test_func() | {21749}       |          1 |      303 |         13 |           0 |     303 | 2026-01-05 06:01:20.619468+00

Line-level details

Once you identify a function to investigate, use plsql_profiler_show_function() to see per-line statistics. Lines with a high percent value are your primary optimization targets. A high exec_count on a line inside a loop indicates a frequently executed hot spot.

-- Pass the func_oid_list value from plsql_profiler_show_funclist().
SELECT * FROM plsql_profiler_show_function(ARRAY[21749]);

Example output:

 line_number | exec_count | total_time | percent | avg_time | min_time | max_time |              source_code               | time_stamp
-------------+------------+------------+---------+----------+----------+----------+----------------------------------------+------------
           0 |          1 |        303 |     100 |      303 |      303 |      303 |  ---- Function Totals ----             |
           1 |          0 |          0 |       0 |        0 |        0 |        0 | v_result VARCHAR2(100);                |
           2 |          1 |        287 |   94.72 |      287 |      287 |      287 | BEGIN                                  |
           3 |          1 |        276 |   91.09 |      276 |      276 |      276 |     FOR i IN 1 .. p_times LOOP         |
           4 |        100 |        145 |   47.85 |        1 |        0 |      145 |         v_result := 'Iteration ' || i; |
           5 |          0 |          0 |       0 |        0 |        0 |        0 |     END LOOP;                          |
           6 |          1 |          0 |       0 |        0 |        0 |        0 |     RETURN v_result;                   |
           7 |          0 |          0 |       0 |        0 |        0 |        0 | END;                                   |

In this output, line 4 has exec_count = 100 (executed once per loop iteration) and accounts for 47.85% of the total function time — investigate this line first.

Step 6: Clean up

-- 1. (Optional) Reset the collected data.
SELECT plsql_profiler_reset();

-- 2. Disable the profiler.
SELECT plsql_profiler_disable();

-- 3. (Optional) Drop the extension if no longer needed.
DROP EXTENSION polar_plsql_profiler;

Advanced analysis: profiling functions with sub-function calls

This example simulates a realistic scenario in which a main function calls a sub-function. It shows how to trace where the time actually goes across the full call hierarchy.

Step 1: Install the extension

CREATE EXTENSION polar_plsql_profiler;

Step 2: Create the target package

-- Create a package with a sub-function nested inside calculate_tax.
CREATE OR REPLACE PACKAGE complex_pkg AS
    FUNCTION calculate_tax(p_salary NUMBER) RETURN NUMBER;
    PROCEDURE process_payroll(p_dept_id NUMBER);
END complex_pkg;

CREATE OR REPLACE PACKAGE BODY complex_pkg AS
    FUNCTION calculate_tax(p_salary NUMBER) RETURN NUMBER IS
        v_tax_amount NUMBER;
        -- Sub-function
        FUNCTION get_tax_rate(p_income NUMBER) RETURN NUMBER IS
            v_rate NUMBER;
        BEGIN
            IF p_income <= 50000 THEN
                v_rate := 0.10;
            ELSIF p_income <= 100000 THEN
                v_rate := 0.20;
            ELSE
                v_rate := 0.30;
            END IF;
            RETURN v_rate;
        END get_tax_rate;
    BEGIN
        v_tax_amount := p_salary * get_tax_rate(p_salary);
        RETURN v_tax_amount;
    END calculate_tax;

    PROCEDURE process_payroll(p_dept_id NUMBER) IS
    BEGIN
        -- Perform some operations.
        NULL;
    END process_payroll;
END complex_pkg;

Step 3: Enable the profiler

SELECT plsql_profiler_enable();

Step 4: Execute the target function

SELECT complex_pkg.calculate_tax(75000);

Step 5: Analyze the call hierarchy

Function call overview

SELECT * FROM plsql_profiler_show_funclist();

Example output:

                  func_call                  |   func_oid_list    | call_count | us_total | us_declare | us_children | us_self |          time_stamp
---------------------------------------------+--------------------+------------+----------+------------+-------------+---------+-------------------------------
 oid=0  inline_code_block                    | {0}                |          1 |       24 |         19 |           0 |      24 | 2026-01-05 04:03:37.68941+00
 oid=21721  complex_pkg.calculate_tax()      | {21721}            |          1 |     1309 |          8 |         815 |     494 | 2026-01-05 04:03:37.689697+00
 oid=21721  complex_pkg.calculate_tax()     +| {21721,4294957297} |          1 |      815 |          9 |           0 |     815 | 2026-01-05 04:03:37.690177+00
 oid=4294957297    ->  public.get_tax_rate() |                    |            |          |            |             |         |
(3 rows)

Reading the output: calculate_tax() had a us_total of 1309 µs. Of that, 815 µs (us_children) came from the get_tax_rate() sub-function, while only 494 µs (us_self) was spent in calculate_tax() itself. Because us_children accounts for the majority of us_total, the real bottleneck is in get_tax_rate(), not in calculate_tax(). The arrow (->) in func_call denotes the parent-child relationship.

Line-level details for the sub-function

To drill into the sub-function, pass the func_oid_list containing both Object Identifiers (OIDs):

SELECT * FROM plsql_profiler_show_function(ARRAY[21721,4294957297]);

Example output:

 line_number | exec_count | total_time | percent | avg_time | min_time | max_time |                source_code                | time_stamp
-------------+------------+------------+---------+----------+----------+----------+-------------------------------------------+------------
           0 |          1 |        351 |     100 |      351 |      351 |      351 |  ---- Function Totals ----                |
           1 |          0 |          0 |       0 |        0 |        0 |        0 | v_rate NUMBER;                            |
           2 |          1 |        340 |   96.87 |      340 |      340 |      340 |         BEGIN                             |
           3 |          1 |        338 |    96.3 |      338 |      338 |      338 |             IF p_income <= 50000 THEN     |
           4 |          0 |          0 |       0 |        0 |        0 |        0 |                 v_rate := 0.10;           |
           5 |          0 |          0 |       0 |        0 |        0 |        0 |             ELSIF p_income <= 100000 THEN |
           6 |          1 |         24 |    6.84 |       24 |       24 |       24 |                 v_rate := 0.20;           |
           7 |          0 |          0 |       0 |        0 |        0 |        0 |             ELSE                          |
           8 |          0 |          0 |       0 |        0 |        0 |        0 |                 v_rate := 0.30;           |
           9 |          0 |          0 |       0 |        0 |        0 |        0 |             END IF;                       |
          10 |          1 |          0 |       0 |        0 |        0 |        0 |             RETURN v_rate;                |
          11 |          0 |          0 |       0 |        0 |        0 |        0 |         END get_tax_rate;                 |

Query all functions in one pass

To view line-level data for every profiled function at once, join the two functions:

SELECT * FROM plsql_profiler_show_funclist() F
CROSS JOIN LATERAL plsql_profiler_show_function(F.func_oid_list) C;

Step 6: Generate an HTML report

For a shareable, visual summary, generate an HTML report using the psql client:

-- Redirect output to a file.
\o plsql_profiler_report.html
-- Enable tuples-only mode to output clean HTML.
\t
-- Generate the report.
SELECT plsql_profiler_get_report('My Performance Report');
-- Stop redirecting.
\o
-- Disable tuples-only mode.
\t

After the command runs, it returns an HTML string containing the full report. Open the .html file in a browser to view the detailed performance analysis report.

Step 7: Clean up

-- 1. (Optional) Reset the collected data.
SELECT plsql_profiler_reset();

-- 2. Disable the profiler.
SELECT plsql_profiler_disable();

-- 3. (Optional) Drop the extension if no longer needed.
DROP EXTENSION polar_plsql_profiler;

Function reference

Control functions

plsql_profiler_enable()

Enables the profiler in the current session.

Syntax: sys.plsql_profiler_enable() RETURNS bool
Returns: true if the profiler is enabled successfully; false otherwise.

plsql_profiler_disable()

Disables the profiler in the current session.

Syntax: sys.plsql_profiler_disable() RETURNS bool
Returns: true if the profiler is disabled successfully; false otherwise.

plsql_profiler_reset()

Clears all collected performance statistics in the current session.

Syntax: sys.plsql_profiler_reset() RETURNS void
Returns: Nothing.

Reporting and analysis functions

plsql_profiler_show_funclist()

Displays the call chain and overall performance summary for all profiled functions, excluding the profiler's own functions.

Syntax: sys.plsql_profiler_show_funclist() RETURNS TABLE(...)
Returns: A table with the following fields.

Field	Description
`func_call`	The function call chain in text format. An arrow (`->`) indicates a parent-child relationship.
`func_oid_list`	The list of function Object Identifiers (OIDs) in the call stack. Pass this array to `plsql_profiler_show_function()` to get line-level details.
`call_count`	The number of times the function was called.
`us_total`	Total execution time in microseconds, including time spent in child functions. Use this to rank functions by overall cost.
`us_declare`	Time spent in the declaration section in microseconds.
`us_children`	Time spent in child functions in microseconds. A high value relative to `us_total` means the bottleneck is in a callee, not in this function.
`us_self`	Time spent in the function's own code in microseconds. A high value relative to `us_total` means the bottleneck is in this function itself.
`time_stamp`	The timestamp of the function call.

plsql_profiler_show_function()

Displays per-line performance statistics for one or more specified functions.

Syntax: sys.plsql_profiler_show_function(func_oid OID[]) RETURNS TABLE(...)
Parameters:

Parameter	Description
`func_oid`	An array of function OIDs. Obtain this value from the `func_oid_list` column of `plsql_profiler_show_funclist()`.

Returns: A table with the following fields.

Field	Description
`line_number`	The line number in the source code. Line 0 shows function totals.
`exec_count`	The number of times this line was executed. A high value on a line inside a loop indicates a frequently executed hot spot.
`total_time`	Total execution time for this line in microseconds.
`percent`	This line's share of the function's total execution time. Lines with a high `percent` are the primary optimization targets.
`avg_time`	Average execution time per call in microseconds.
`min_time`	Minimum execution time across all executions in microseconds.
`max_time`	Maximum execution time across all executions in microseconds. A large gap between `min_time` and `max_time` can indicate variable-cost operations such as cache misses or I/O.
`source_code`	The source code of this line.
`time_stamp`	The timestamp for anonymous block execution. `null` for named functions.

plsql_profiler_get_report()

Generates a complete performance analysis report in HTML format.

Syntax: sys.plsql_profiler_get_report(IN title text DEFAULT 'PL/SQL PROFILER REPORT') RETURNS text
Parameters:

Parameter	Description
`title`	The report title. Optional. Default: `PL/SQL PROFILER REPORT`.

Returns: An HTML string containing the full report.

Low-level data retrieval functions

These functions expose raw profiler data and are intended for custom analysis or tooling built on top of the profiler.

plsql_profiler_linestats_local()

Retrieves raw line-level statistics, including function execution counts and timing details.

Syntax: sys.plsql_profiler_linestats_local() RETURNS SETOF record
Returns: A set of records with the following fields: func_oid, line_number, exec_count, total_time, percent, min_time, max_time, source, time_stamp.

plsql_profiler_callgraph_local()

Retrieves raw function call graph information, including function call counts and timing.

Syntax: sys.plsql_profiler_callgraph_local() RETURNS SETOF record
Returns: A set of records with the following fields: stack, call_count, us_total, us_declare, us_children, us_self, time_stamp.

plsql_profiler_func_oids_local()

Retrieves the OIDs of all profiled functions.

Syntax: sys.plsql_profiler_func_oids_local() RETURNS oid[]
Returns: An oid[] array containing the OIDs of all profiled functions.

plsql_profiler_funcs_source()

Returns the source code for a given array of function OIDs.

Syntax: sys.plsql_profiler_funcs_source(func_oids oid[]) RETURNS SETOF record
Parameters:

Parameter	Description
`func_oid`	An array of function OIDs. Obtain this value from the `func_oid` column of `plsql_profiler_linestats_local()`.

Returns: A set of records with the following fields: func_oid, line_number, source.

plsql_profiler_get_stack()

Converts a function OID stack into an array of function signatures.

Syntax: sys.plsql_profiler_get_stack(stack oid[]) RETURNS text[]
Parameters:

Parameter	Description
`stack`	An array of function OIDs representing the call stack. Obtain this value from the `stack` column of `plsql_profiler_callgraph_local()`.

Returns: A text[] array containing the function signatures.

plsql_profiler_get_stack_formatted()

Converts a function OID stack into a formatted call chain string and OID list.

Syntax: sys.plsql_profiler_get_stack_formatted(stack oid[]) RETURNS text[]
Parameters:

Parameter	Description
`stack`	An array of function OIDs representing the call stack. Obtain this value from the `stack` column of `plsql_profiler_callgraph_local()`.

Returns: A text[] array containing the formatted call chain and OID list.

FAQ

No data appears after I run my PL/SQL code. What should I check?

Check the following:

Run SELECT plsql_profiler_enable(); in the same session before executing the target PL/SQL code. The profiler only captures code that runs after it is enabled in the current session.
Analytical data is session-level and is lost if the connection breaks. Check if your database session was disconnected and then reconnected.

Can I use this tool in a production environment?

Do not keep the profiler enabled continuously in a production environment because it introduces performance overhead. Use it only for temporary performance diagnostics and tuning. After you finish the analysis, disable the profiler with SELECT plsql_profiler_disable();.