PL/SQL Profiler

 

What is PL/SQL Profiler?

The PL/SQL Profiler is a diagnostic tool that helps you:

• Measure performance metrics for PL/SQL programs.

• Identify slow lines or procedures.

• Understand which parts of your code are executed, how often, and how long they take.

It records execution time, number of executions, and ordering of PL/SQL units like procedures, functions, packages, and triggers.

---

⚙️ Tool Behind It: DBMS_PROFILER

Oracle provides a built-in package called DBMS_PROFILER that enables you to profile your PL/SQL code.

---

🧱 How PL/SQL Profiler Works (High-Level)

1. Enable profiling using DBMS_PROFILER.START_PROFILER.

2. Run your PL/SQL block, procedure, or application.

3. Stop profiling using DBMS_PROFILER.STOP_PROFILER.

4. Data is stored in profiler tables like PLSQL_PROFILER_DATA, which you can query to analyze performance.

---

🛠️ Step-by-Step Setup and Usage

🔸 Step 1: Create Profiler Tables (one-time setup)

Oracle doesn’t automatically create profiler tables. Use the profload.sql script provided by Oracle to create them.

Run this as SYS or DBA user:

sql
@$ORACLE_HOME/rdbms/admin/profload.sql

This script creates these key tables:

Table Name	Purpose
PLSQL_PROFILER_RUNS	Stores metadata about each profiling run
PLSQL_PROFILER_UNITS	Stores info about procedures/functions
PLSQL_PROFILER_DATA	Stores line-by-line execution stats

---

🔸 Step 2: Start the Profiler

plsql
BEGIN
  DBMS_PROFILER.START_PROFILER('Test Run of My Procedure');
END;

---

🔸 Step 3: Run the Code You Want to Profile

plsql
BEGIN
  my_package.my_procedure();
END;

---

🔸 Step 4: Stop the Profiler

plsql
BEGIN
  DBMS_PROFILER.STOP_PROFILER;
END;

---

🔸 Step 5: Query the Results

Example: View execution details

sql
SELECT u.unit_name, d.line#, d.total_occur, d.total_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = (SELECT MAX(runid) FROM plsql_profiler_runs)
ORDER BY d.total_time DESC;

This shows:

• Line numbers in the code

• Number of times each line was executed

• Time spent on each line (in hundredths of milliseconds)

---

📊 What You Can Learn from PL/SQL Profiler

Insight	How It Helps
Execution time per line	Pinpoint slow code
Number of executions per line	Identify loops or unnecessary calls
Most frequently called procedures	Optimize heavily-used code paths
Unexecuted code	Detect dead code or missed logic paths

---

⚠️ Important Notes

• Profiling adds some overhead — use it in dev/test environments only.

• It captures CPU time only, not wait time (I/O, locks, etc.).

• Does not profile SQL queries themselves — just PL/SQL logic.

• For SQL tuning, use SQL Trace, AWR, or SQL Monitor.

---

🧠 Bonus: Tools That Visualize Profiler Data

• Oracle SQL Developer: Has a visual profiler UI under Tools → Profiler

• Third-party tools: TOAD, PL/SQL Developer, etc.

• You can also build a custom report using the profiler tables.

---

✅ Summary

Feature	PL/SQL Profiler
What it does	Measures performance of PL/SQL code
How it works	Tracks execution time, frequency, line info
Key package	DBMS_PROFILER
Tables involved	PLSQL_PROFILER_RUNS, UNITS, DATA
Use cases	Tuning slow procedures, identifying hotspots

---

Let me know if you'd like a ready-made SQL Developer Profiler guide or a sample custom report for profiler results!

You said:

Show me with a realistic Prod Scenario ..What all values will be present in each table

ChatGPT said:

Absolutely! Let’s walk through a realistic production-like scenario using the PL/SQL Profiler, showing what it captures and what data you’ll see in the profiler tables.

---

🏭 Real-World Scenario: Monthly Salary Bonus Update

Imagine you're profiling a procedure in production that:

• Processes all employees.

• Calculates a bonus.

• Updates the employees table.

PL/SQL Procedure:

plsql
CREATE OR REPLACE PROCEDURE process_employee_bonus IS
BEGIN
  FOR emp IN (SELECT employee_id, salary FROM employees) LOOP
    UPDATE employees
    SET bonus = salary * 0.10
    WHERE employee_id = emp.employee_id;
  END LOOP;
END;

---

🔍 Profiling Steps

1. Start profiler

2. Run process_employee_bonus

3. Stop profiler

4. Query profiler tables

---

📊 What the Profiler Captures

🔹 1. PLSQL_PROFILER_RUNS

RUNID	RUN_COMMENT	START_TIME	END_TIME
101	'Bonus Calculation Run'	06-APR-25 10:02:11	06-APR-25 10:02:15

This table shows metadata for the entire profiling session.

---

🔹 2. PLSQL_PROFILER_UNITS

RUNID	UNIT_NUMBER	UNIT_NAME	UNIT_TYPE	LINE_COUNT	LANGUAGE
101	1	PROCESS_EMPLOYEE_BONUS	PROCEDURE	10	PL/SQL

Shows which program units (procedures, packages, triggers, etc.) were profiled and how many lines they contain.

---

🔹 3. PLSQL_PROFILER_DATA

This table contains the core performance data per line of code.

RUNID	UNIT_NUMBER	LINE#	TOTAL_OCCUR	TOTAL_TIME
101	1	2	1	5
101	1	3	1	2
101	1	4	1000	50
101	1	5	1000	500
101	1	6	1	1

Explanation:

• LINE 4–5: These are the FOR LOOP and UPDATE — run 1000 times (one per employee).

• TOTAL_TIME: Time spent per line in hundredths of milliseconds (e.g., 500 = 5ms).

• Line 5 (the UPDATE) clearly takes the most time.

---

🔁 Query to See Top Time-Consuming Lines

sql
SELECT u.unit_name, d.line#, d.total_occur, d.total_time
FROM plsql_profiler_data d
JOIN plsql_profiler_units u ON d.runid = u.runid AND d.unit_number = u.unit_number
WHERE d.runid = 101
ORDER BY d.total_time DESC;

You’ll instantly spot that the UPDATE line dominates runtime.

---

🧠 What You Learn

• You get execution frequency per line — to identify hotspots or loops.

• You see total time per line — to focus optimization efforts.

• You detect unexecuted lines (i.e., dead code).

• You spot inefficient SQL inside PL/SQL — like UPDATE in a loop.

---

✅ Production Tuning Example

Problem Identified: Line with UPDATE inside the loop took most of the time.

Optimization Idea: Replace with a bulk update or use FORALL.

---

📌 Summary of Profiler Tables (Simplified)

Table Name	Description
PLSQL_PROFILER_RUNS	Overall metadata per profiling session
PLSQL_PROFILER_UNITS	Each PL/SQL program unit (procedure/function)
PLSQL_PROFILER_DATA	Line-level performance: time spent & executions

---

Would you like a full script that sets up the profiler, runs this test procedure, and queries results automatically? I can generate that for you step-by-step.

Yes! ✅ The PL/SQL Profiler is officially available in Oracle Database, but there are a few things to know based on Oracle version and edition.

---

📦 PL/SQL Profiler Availability

Feature	Available In
DBMS_PROFILER	✅ Yes — Included in Oracle 8i+
Profiler tables (profload.sql)	✅ Yes — Must be manually installed
SQL Developer GUI support	✅ Yes — For versions that support profiling
Oracle Editions	✅ Enterprise, Standard, XE (with some limitations)

⚠️ Note: Oracle Cloud services (Autonomous DB) do not support DBMS_PROFILER directly — you would need alternative tracing or AWR tools.

---

📍 Where It Lives

• The DBMS_PROFILER package is in the schema: SYS

• The profiler tables are created via:

  sql
  @$ORACLE_HOME/rdbms/admin/profload.sql
  

This creates:

• PLSQL_PROFILER_RUNS

• PLSQL_PROFILER_UNITS

• PLSQL_PROFILER_DATA

If you're in SQL Developer, you can also run profiling using the GUI:

Tools → PL/SQL Profiler

---

🧪 How to Check if Profiler Is Installed

Run this:

sql
SELECT * FROM all_objects WHERE object_name = 'DBMS_PROFILER';

If it returns results — ✅ it’s available.

---

🔐 Privileges Needed

To use DBMS_PROFILER, the user needs:

sql
GRANT EXECUTE ON DBMS_PROFILER TO your_user;

Also, to insert into the profiler tables:

sql
GRANT INSERT, UPDATE, DELETE ON PLSQL_PROFILER_RUNS TO your_user;
GRANT INSERT, UPDATE, DELETE ON PLSQL_PROFILER_UNITS TO your_user;
GRANT INSERT, UPDATE, DELETE ON PLSQL_PROFILER_DATA TO your_user;

---

🔁 Alternative in Newer Oracle Versions

If you're on Oracle 12c or later, you can also use:

• Hierarchical Profiler (DBMS_HPROF) — deeper analysis with call graphs.

• SQL Trace / TKPROF / AWR / SQL Monitor — more on SQL + waits.

---

✅ Summary

Feature	Supported in Oracle?
DBMS_PROFILER Package	✅ Yes
SQL Developer Profiler UI	✅ Yes
Profiler Tables	✅ Yes (via profload.sql)
Cloud/Autonomous DB	❌ Not directly supported

---

Want a quick script to check availability and install the profiler tables in your DB? I can give you a full setup script if you're ready to try it.