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)
-
Enable profiling using
DBMS_PROFILER.START_PROFILER
. -
Run your PL/SQL block, procedure, or application.
-
Stop profiling using
DBMS_PROFILER.STOP_PROFILER
. -
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
plsqlBEGIN DBMS_PROFILER.START_PROFILER('Test Run of My Procedure'); END;
πΈ Step 3: Run the Code You Want to Profile
plsqlBEGIN my_package.my_procedure(); END;
πΈ Step 4: Stop the Profiler
plsqlBEGIN DBMS_PROFILER.STOP_PROFILER; END;
πΈ Step 5: Query the Results
Example: View execution details
sqlSELECT 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:
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:
plsqlCREATE 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
-
Start profiler
-
Run
process_employee_bonus
-
Stop profiler
-
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
andUPDATE
β 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
sqlSELECT 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:
sqlSELECT * FROM all_objects WHERE object_name = 'DBMS_PROFILER';
If it returns results β β itβs available.
π Privileges Needed
To use DBMS_PROFILER
, the user needs:
sqlGRANT EXECUTE ON DBMS_PROFILER TO your_user;
Also, to insert into the profiler tables:
sqlGRANT 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.
No comments:
Post a Comment