Sunday, April 6, 2025

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 NamePurpose
PLSQL_PROFILER_RUNSStores metadata about each profiling run
PLSQL_PROFILER_UNITSStores info about procedures/functions
PLSQL_PROFILER_DATAStores 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

InsightHow It Helps
Execution time per linePinpoint slow code
Number of executions per lineIdentify loops or unnecessary calls
Most frequently called proceduresOptimize heavily-used code paths
Unexecuted codeDetect 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

FeaturePL/SQL Profiler
What it doesMeasures performance of PL/SQL code
How it worksTracks execution time, frequency, line info
Key packageDBMS_PROFILER
Tables involvedPLSQL_PROFILER_RUNS, UNITS, DATA
Use casesTuning 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

RUNIDRUN_COMMENTSTART_TIMEEND_TIME
101'Bonus Calculation Run'06-APR-25 10:02:1106-APR-25 10:02:15

This table shows metadata for the entire profiling session.


πŸ”Ή 2. PLSQL_PROFILER_UNITS

RUNIDUNIT_NUMBERUNIT_NAMEUNIT_TYPELINE_COUNTLANGUAGE
1011PROCESS_EMPLOYEE_BONUSPROCEDURE10PL/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.

RUNIDUNIT_NUMBERLINE#TOTAL_OCCURTOTAL_TIME
1011215
1011312
10114100050
101151000500
1011611

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 NameDescription
PLSQL_PROFILER_RUNSOverall metadata per profiling session
PLSQL_PROFILER_UNITSEach PL/SQL program unit (procedure/function)
PLSQL_PROFILER_DATALine-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

FeatureAvailable 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

FeatureSupported 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

SQL Interview Questions

Common SQL Interview Questions Common SQL Interview Questions 1. JOIN Types INNER JOIN: Only matching row...