Profiling PL/SQL Performance With DBMS_PROFILER and DBMS_HPROF

Finding the Real Bottleneck

When a PL/SQL procedure runs slowly, developers often guess at the bottleneck and optimize the wrong section. Oracle provides two profiling tools that replace guesswork with data: DBMS_PROFILER for line-level execution time profiling and DBMS_HPROF for hierarchical call-tree profiling.

DBMS_PROFILER: Line by Line Analysis

DBMS_PROFILER measures the time spent on each line of each PL/SQL unit. To use it, install the profiler tables by running @?/rdbms/admin/proftab.sql as the schema owner, then bracket your code with start and stop calls:

BEGIN
    DBMS_PROFILER.START_PROFILER('Order Processing Test');

    -- Call the procedure you want to profile
    process_all_orders;

    DBMS_PROFILER.STOP_PROFILER;
END;
/

-- Query the results
SELECT u.unit_name, d.line#,
       ROUND(d.total_time / 1e9, 3) AS seconds,
       d.total_occur AS executions,
       s.text AS source_line
  FROM plsql_profiler_data d
  JOIN plsql_profiler_units u ON d.runid = u.runid AND d.unit_number = u.unit_number
  LEFT JOIN all_source s ON s.owner = u.unit_owner AND s.name = u.unit_name
       AND s.type = u.unit_type AND s.line = d.line#
 WHERE d.runid = (SELECT MAX(runid) FROM plsql_profiler_runs)
   AND d.total_time > 0
 ORDER BY d.total_time DESC
 FETCH FIRST 20 ROWS ONLY;

DBMS_HPROF: Hierarchical Profiling

DBMS_HPROF shows the call tree with time spent in each function including sub-calls. This is better for understanding where time is spent across multiple packages:

BEGIN
    DBMS_HPROF.START_PROFILING('PLSHPROF_DIR', 'order_test.trc');
    process_all_orders;
    DBMS_HPROF.STOP_PROFILING;
END;
/

-- Analyze the trace file
SELECT DBMS_HPROF.ANALYZE('PLSHPROF_DIR', 'order_test.trc') AS runid FROM DUAL;

Interpreting Results

Look for lines or functions with high total_time values. A line executed once that takes 5 seconds is different from a line executed 100,000 times that takes 5 seconds total. The first suggests an expensive SQL statement; the second suggests a loop that should use BULK COLLECT. Focus optimization effort on the top 3 to 5 hotspots, as they typically account for 80% or more of the total execution time.

Practical Tips

Profile with realistic data volumes, not tiny test sets. Run the profile multiple times and average the results to account for caching effects. After optimizing, re-profile to confirm the improvement and watch for regressions in other areas. Remove profiling calls before deploying to production, as profiling itself adds overhead.