Skip to main content

Getting Started with DBMS_PROFILER in Oracle Database

Oracle PL/SQL · Performance Tuning

What is DBMS_PROFILER?

An interface to profile existing PL/SQL applications and identify performance bottlenecks - collecting and persistently storing granular execution data at the virtual machine level.

Overview

DBMS_PROFILER provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. It lets us collect and persistently store profiler data.

The profiler gathers information at the PL/SQL virtual machine level, including the total number of times each line has been executed, the total time spent on that line, and the minimum and maximum execution times for each line.

Data Tables
Table Name Description
PLSQL_PROFILER_RUNS Metadata per run (run ID, owner, date, comment, total time)
PLSQL_PROFILER_UNITS Info per PL/SQL unit profiled (type, owner, name, total time)
PLSQL_PROFILER_DATA Per-line stats (execution count, total/min/max time in nanoseconds)
Subprograms
START_PROFILER
Begins profiler data collection; accepts run comments and optionally returns a run number
PL/SQL 
DECLARE
  v_status BINARY_INTEGER;
BEGIN
  v_status := DBMS_PROFILER.START_PROFILER(
    run_comment  => 'Benchmark Test - Batch Invoice Processing',
    run_comment1 => 'Release 3.2 | ENV: UAT'
  );
  IF v_status != 0 THEN
    DBMS_OUTPUT.PUT_LINE('START_PROFILER failed, status: ' || v_status);
  END IF;
END;
/
STOP_PROFILER
Stops collection and flushes data to tables
PL/SQL 
DECLARE
  v_status BINARY_INTEGER;
BEGIN
  -- ... your PL/SQL code being profiled runs here ...
  v_status := DBMS_PROFILER.STOP_PROFILER;
  IF v_status = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Profiler stopped and data saved successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('STOP_PROFILER failed, status: ' || v_status);
  END IF;
END;
/
PAUSE_PROFILER
Temporarily pauses collection
PL/SQL 
DECLARE
  v_status BINARY_INTEGER;
BEGIN
  DBMS_PROFILER.START_PROFILER('Order Processing - Core Logic Only');

  -- Profile this section
  validate_order(p_order_id => 1001);

  -- Pause: skip logging/audit overhead - not interested in profiling this
  v_status := DBMS_PROFILER.PAUSE_PROFILER;
  DBMS_OUTPUT.PUT_LINE('Paused. Status: ' || v_status);

  write_audit_log(p_order_id => 1001); -- excluded from profiling

  -- Resume: back to profiling the real work
  v_status := DBMS_PROFILER.RESUME_PROFILER;
  DBMS_OUTPUT.PUT_LINE('Resumed. Status: ' || v_status);

  calculate_invoice(p_order_id => 1001);
  DBMS_PROFILER.STOP_PROFILER;
END;
/
RESUME_PROFILER
Resumes paused collection

Used in conjunction with PAUSE_PROFILER. See the PAUSE_PROFILER example above for combined usage.

FLUSH_DATA
Saves in-memory profiler data to tables mid-run (frees memory too)
PL/SQL 
DECLARE
  v_status BINARY_INTEGER;
BEGIN
  DBMS_PROFILER.START_PROFILER('Long Batch Job');
  FOR i IN 1..10000 LOOP
    -- simulate work
    process_order(i);
    -- flush every 1000 records to free memory
    IF MOD(i, 1000) = 0 THEN
      v_status := DBMS_PROFILER.FLUSH_DATA;
      DBMS_OUTPUT.PUT_LINE('Flushed at iteration: ' || i || ' | Status: ' || v_status);
    END IF;
  END LOOP;
  DBMS_PROFILER.STOP_PROFILER;
END;
/
GET_VERSION
Returns the major/minor version of the API
PL/SQL 
DECLARE
  v_major BINARY_INTEGER;
  v_minor BINARY_INTEGER;
BEGIN
  DBMS_PROFILER.GET_VERSION(
    major => v_major,
    minor => v_minor
  );
  DBMS_OUTPUT.PUT_LINE('DBMS_PROFILER Version: ' || v_major || '.' || v_minor);
END;
/
INTERNAL_VERSION_CHECK
Verifies package/database version compatibility
PL/SQL 
DECLARE
  v_status BINARY_INTEGER;
BEGIN
  v_status := DBMS_PROFILER.INTERNAL_VERSION_CHECK;
  IF v_status = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Version check passed. Package is compatible.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Version mismatch! Code: ' || v_status || '. Reinstall the correct DBMS_PROFILER version.');
  END IF;
END;
/
ℹ️
Function vs Procedure Forms Each subprogram comes in two forms - a function that returns a status code and never raises exceptions, and a procedure that raises an exception on failure.
Security
⚠️
CREATE Privilege Required The profiler only gathers data for units for which the user has CREATE privilege. Units with EXECUTE ONLY access cannot be profiled.
⚠️
NATIVE Mode Limitation Any program unit compiled in NATIVE mode is treated as if the user lacks CREATE privilege - no output will be generated.
Tips for Accurate Results
💡
One Statement Per Line Place each SQL/PL/SQL statement on its own line - multi-line statements may have code attributed to just one line, causing gaps in line number coverage.
💡
System-Wide Profiling For system-wide profiling across all users, have SYSADMIN grant SELECT/INSERT/UPDATE on the profiler tables and define public synonyms.
Combine Query - Performance Report
SQL 
SELECT
  plsql_profiler_runs.RUN_DATE,
  plsql_profiler_runs.RUN_COMMENT,
  plsql_profiler_units.UNIT_TYPE,
  plsql_profiler_units.UNIT_NAME,
  plsql_profiler_data.LINE#,
  plsql_profiler_data.TOTAL_OCCUR,
  plsql_profiler_data.TOTAL_TIME,
  plsql_profiler_data.MIN_TIME,
  plsql_profiler_data.MAX_TIME,
  round(plsql_profiler_data.total_time/1000000000) total_time_in_sec,
  trunc(((plsql_profiler_data.total_time)/(sum(plsql_profiler_data.total_time) over()))*100,2) pct_of_time_taken
FROM
  plsql_profiler_data,
  plsql_profiler_runs,
  plsql_profiler_units
WHERE
  plsql_profiler_data.total_time > 0
  AND plsql_profiler_data.runid = plsql_profiler_runs.runid
  AND plsql_profiler_units.UNIT_NUMBER = plsql_profiler_data.UNIT_NUMBER
  AND plsql_profiler_units.runid = plsql_profiler_runs.runid
ORDER BY
  plsql_profiler_data.total_time DESC;
Column Source Table Description
RUN_DATEPLSQL_PROFILER_RUNSDate and time the profiling run was started
RUN_COMMENTPLSQL_PROFILER_RUNSComment label assigned when starting the profiler
UNIT_TYPEPLSQL_PROFILER_UNITSType of PL/SQL unit (PROCEDURE, FUNCTION, PACKAGE BODY, etc.)
UNIT_NAMEPLSQL_PROFILER_UNITSName of the profiled PL/SQL unit
LINE#PLSQL_PROFILER_DATASource line number within the unit
TOTAL_OCCURPLSQL_PROFILER_DATATotal number of times the line was executed
TOTAL_TIMEPLSQL_PROFILER_DATACumulative execution time (nanoseconds)
MIN_TIMEPLSQL_PROFILER_DATAMinimum single execution time (nanoseconds)
MAX_TIMEPLSQL_PROFILER_DATAMaximum single execution time (nanoseconds)
TOTAL_TIME_IN_SECDerivedTotal time converted to seconds
PCT_OF_TIME_TAKENDerivedPercentage of overall profiling run time for this line
Example
🔬
Scenario — Profiling a Slow Batch Insert Procedure

We have a procedure slow_proc that inserts 100,000 rows into emp_log inside a loop. We want to use DBMS_PROFILER to pinpoint exactly which line consumes the most time.

STEP 01 Create the procedure to be profiled
PL/SQL 
CREATE OR REPLACE PROCEDURE slow_proc AS
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO emp_log VALUES (SYSDATE, i);
  END LOOP;
  COMMIT;
END;
/
STEP 02 Start the profiler, execute the procedure, then stop
PL/SQL 
DECLARE
  v_run NUMBER;
BEGIN
  DBMS_PROFILER.START_PROFILER(
    run_comment => 'Testing slow_proc performance',
    run_number  => v_run
  );

  slow_proc();                           -- <-- the code under test

  DBMS_PROFILER.STOP_PROFILER;
  DBMS_OUTPUT.PUT_LINE('Run ID: ' || v_run);
END;
/
STEP 03 Query the profiler tables — sample output
Query Output — PLSQL_PROFILER_DATA
Oracle DBMS_PROFILER Reference  ·  PL/SQL Performance Profiling

Comments

Post a Comment

Popular posts from this blog

APEX - Tip: Fix Floating Label Issue

Oracle APEX's Universal Theme provides a modern and clean user experience through features like floating (above) labels for page items.  These floating labels work seamlessly when users manually enter data, automatically moving the label above the field on focus or input.  However, a common UI issue appears when page item values are set Dynamically the label and the value overlap, resulting in a broken and confusing user interface. once the user focuses the affected item even once, the label immediately corrects itself and displays properly. When an issue is reported, several values are populated based on a single user input, causing the UI to appear misaligned and confusing for the end user. Here, I'll share a few tips to fix this issue. For example, employee details are populated based on the Employee name. In this case, the first True Action is used to set the values, and in the second True Action, paste the following code setTimeout(function () {   $("#P29_EMAIL,#P29_...
Post a Comment
Read more

Oracle APEX UI Tip: Display Page Title Next to the APEX Logo

In most Oracle APEX applications, every page has a Page Title displayed at the top. While useful, this title occupies vertical space, especially in apps where screen real estate matters (dashboards, reports, dense forms). So the goal is simple: Show the page title near the APEX logo instead of consuming page content space. This keeps the UI clean, professional, and consistent across all pages. Instead of placing the page title inside the page body:         ✅ Fetch the current page title dynamically         ✅ Display it right after the APEX logo         ✅ Do it globally, so it works for every page All of this is achieved using:         ✅ Global Page (Page 0)         ✅ One Dynamic Action         ✅ PL/SQL + JavaScript Simple, effective, and reusable. 1️⃣ Create a Global Page Item On Page 0 (Global Page), create a hidden item:      P0_PAGE_TITLE This item wi...
Post a Comment
Read more

Building a Custom Debug Package for Oracle APEX Using PL/SQL

While developing Oracle APEX applications, debugging page processes and backend PL/SQL logic can be challenging—especially when values are lost between processes or execution flow is unclear.  Although DBMS_OUTPUT is useful, it doesn’t work well inside APEX runtime. To solve this, I built a custom PL/SQL debug Package that logs execution flow and variable values into a database table.  This approach helps trace exactly where the code reached, what values were passed, and whether a block executed or not - even inside page-level processes and packaged procedures Why a Custom Debug Package? Works seamlessly inside Oracle APEX page processes Persists debug information even after session ends Helps trace execution flow Captures runtime values Can be turned ON/OFF dynamically Does not interrupt business logic The Package consists of:- Debug Table                         -  Stores debug messages Sequence ...
Post a Comment
Read more