🔍 How Oracle DBMS_PROFILER Works: A Real-World Example to Optimize PL/SQL Code
When working with large PL/SQL procedures or packages, understanding where your code spends time is key to optimizing performance. Unlike SQL, which has tools like EXPLAIN PLAN for pre-execution analysis, PL/SQL does not offer native pre-run estimators. That’s where Oracle's DBMS_PROFILER comes into play.
In this blog, we’ll walk through how the DBMS_PROFILER works, step by step, using a real-world example — calculating the sum of numbers from 1 to 100 — and how it helps in diagnosing and optimizing your PL/SQL logic.
📘 What is DBMS_PROFILER?
DBMS_PROFILER is a built-in Oracle package that enables line-by-line performance profiling of PL/SQL code. It captures:
How many times each line of code is executed
How much time is spent on each line
Which blocks of code are the most resource-intensive
This makes it an essential tool for PL/SQL performance tuning, especially when dealing with procedures, functions, and packages.
🔧 When and Why to Use It?
Unlike SQL EXPLAIN PLAN (used before execution), DBMS_PROFILER gives post-execution runtime insights, which are crucial when:
You want to identify performance bottlenecks inside loops or conditionals
You need to trace inefficient logic or SQLs embedded in PL/SQL blocks
You want real metrics to guide code optimization
✅ Real-Life Use Case: Summing Numbers from 1 to 100
Let’s take a simple example — a procedure that calculates the sum from 1 to 100.
CREATE OR REPLACE PROCEDURE calc_sum IS
v_sum NUMBER := 0;
BEGIN
FOR i IN 1..100 LOOP
v_sum := v_sum + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum is: ' || v_sum);
END;
🪜 5 Steps to Use DBMS_PROFILER
Here’s how I profiled this procedure using the five key steps:
1️⃣ Create the Procedure
I created the calc_sum procedure to perform a simple loop and print the result.
2️⃣ Start the Profiler
Before executing the procedure, I started the profiler:
BEGIN
DBMS_PROFILER.START_PROFILER('Run: calc_sum');
END;
/
This tells Oracle to begin capturing performance metrics from this point forward.
3️⃣ Execute the Procedure
Then, I called the actual PL/SQL logic:
BEGIN
calc_sum;
END;
/
4️⃣ Stop the Profiler
After execution, I stopped the profiler to write all runtime stats to internal tables:
BEGIN
DBMS_PROFILER.STOP_PROFILER;
END;
/
5️⃣ Analyze the Results
Now, I queried Oracle’s system profiler tables to get the performance details:
-- View execution data
SELECT u.unit_name, d.line#, d.total_occur, d.total_time
FROM plsql_profiler_data d
JOIN plsql_profiler_units u ON d.unit_number = u.unit_number
WHERE d.runid = (SELECT MAX(runid) FROM plsql_profiler_runs)
ORDER BY d.total_time DESC;
📊 Sample Output Explained
UNIT_NAME LINE# TOTAL_OCCUR TOTAL_TIME
CALC_SUM 4 100 85
CALC_SUM 5 1 2
Line 4 (v_sum := v_sum + i;) ran 100 times and took the most time.
Line 5 (DBMS_OUTPUT) ran once and had negligible impact.
🎯 Final Thoughts: Why DBMS_PROFILER Matters
Helps identify which lines are slow and how often they run
Enables data-driven performance tuning
Essential for complex procedures with loops, conditionals, and embedded SQL
✍️ Summary Statement
"I used DBMS_PROFILER to analyze and optimize PL/SQL procedures by capturing line-wise execution stats and identifying the most time-consuming operations for targeted performance tuning."
Top comments (0)