Oracle DBA

In the silent architecture of the Oracle Database, every line of PL/SQL code carries weight. Performance issues arise not with a roar, but with a whisper—a process that once danced now stumbles, and somewhere deep in the code, time leaks away.

As Oracle professionals, our calling is to seek out these shadows and bring them into the light—not through guesswork, but by embracing Oracle’s own beacon: the DBMS_PROFILER.
Let us embark on an iterative, data-driven journey from confusion to clarity, and from complexity to actionable results.

---

1. The Challenge of PL/SQL Performance Tuning

PL/SQL codebases often evolve into labyrinths—procedures invoking procedures, recursive logic, and dense business rules.
Manual inspection quickly becomes overwhelming. Logging statements, though occasionally effective, are neither scalable nor elegant for large or frequently changing code.

How, then, do we find the true bottleneck?

2. DBMS_PROFILER: The Tuner’s Oracle

Oracle’s DBMS_PROFILER is an in-built package designed to profile PL/SQL code at the line-by-line level.
It records:

• How much time each statement takes
• How often each statement is executed

Armed with this empirical evidence, you target exactly what matters—no more, no less.

3. The Four Pillars: End-to-End Profiler Workflow

Step 1: Environment Setup

1. Privilege Check
   Ensure your schema has EXECUTE on DBMS_PROFILER. sqlCopyEditGRANT EXECUTE ON DBMS_PROFILER TO your_schema;
2. Create Profiler Tables
   Run the script proftab.sql found in your Oracle installation directory (typically $ORACLE_HOME/rdbms/admin/proftab.sql): sqlCopyEdit-- As your_schema @?/rdbms/admin/proftab.sql This creates:
   • PLSQL_PROFILER_RUNS
   • PLSQL_PROFILER_UNITS
   • PLSQL_PROFILER_DATA

Step 2: Profiling Execution – With a Real Example

Let’s walk through a concrete example:

Suppose you have three procedures with nested calls:

CREATE OR REPLACE PROCEDURE proc_c IS
BEGIN
  FOR i IN 1..5000 LOOP
    -- Simulate workload
    NULL;
  END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE proc_b IS
BEGIN
  FOR i IN 1..1000 LOOP
    proc_c;
  END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE proc_a IS
BEGIN
  FOR i IN 1..10 LOOP
    proc_b;
  END LOOP;
END;
/

You notice that calling proc_a takes unexpectedly long. Where is the time going?

Profiling Session:

-- Start the profiler and add a comment for traceability
EXEC DBMS_PROFILER.START_PROFILER(run_comment => 'Tuning PROC_A chain');

-- Execute the top-level procedure
EXEC proc_a;

-- Stop the profiler
EXEC DBMS_PROFILER.STOP_PROFILER;

Step 3: Data Analysis – Unmasking the Bottleneck

With the execution complete, analyze where your code spent its time.

Sample Query to Isolate Hot Spots:

SELECT
  u.unit_name,
  d.line#,
  d.total_occur,
  ROUND(d.total_time/1000000,2) AS total_time_seconds,
  ROUND((d.total_time/SUM(d.total_time) OVER())*100,2) AS pct_time
FROM
  plsql_profiler_data d
  JOIN plsql_profiler_units u ON d.unit_number = u.unit_number
  JOIN plsql_profiler_runs r ON d.runid = r.runid
WHERE
  r.run_comment = 'Tuning PROC_A chain'
ORDER BY
  total_time_seconds DESC;

Sample Output Interpretation:

UNIT_NAME	LINE#	TOTAL_OCCUR	TOTAL_TIME_SECONDS	PCT_TIME
PROC_C	4	50000	8.12	95.2
PROC_B	4	10000	0.32	3.8
PROC_A	4	10	0.05	0.6

• Observation:
  Nearly all the time (95.2%) is spent in PROC_C, specifically at Line 4.
• Insight:
  There is a high-frequency loop, and optimization should focus here.

Step 4: Iterative Optimization – Science, Not Guesswork

Armed with this clarity, you might refactor PROC_C (for example, by replacing procedural loops with set-based SQL, or optimizing loop logic).

Repeat Steps 2 & 3 after each change.
Keep a log or spreadsheet:

• Run 1: Baseline, before tuning
• Run 2: After first refactor
• Run 3: After further tuning, etc.

Present before/after metrics to stakeholders, quantifying performance gains and validating tuning efforts.

4. Essential PL/SQL Tuning Techniques (with Examples)

• Leverage Built-In Functions
  Replace procedural aggregation with SQL aggregate functions.
  
  -- Inefficient: total := 0;
FOR rec IN (SELECT value FROM my_table) LOOP
total := total + rec.value;
END LOOP;

-- Efficient:
SELECT SUM(value) INTO total FROM my_table;
• Bulk Processing
  -- Inefficient row-by-row processing
FOR rec IN (SELECT * FROM my_table) LOOP
process_row(rec);
END LOOP;

-- Efficient bulk processing
BULK COLLECT/FORALL:
TYPE t_tab IS TABLE OF my_table%ROWTYPE;
l_tab t_tab;
SELECT * BULK COLLECT INTO l_tab FROM my_table;
FORALL i IN 1..l_tab.COUNT
process_row(l_tab(i));
  
• Use Global Temporary Tables
  When dealing with large datasets, offload intermediate results to GTTs for efficiency.
• Minimize Logging/Debug Code
  Remove or comment out unnecessary log statements in production code paths.

5. Pro Tips: Managing Profiler Data

• Delete Old Data:
  The profiler tables are linked via foreign keys.
  Always delete from PLSQL_PROFILER_DATA first, then PLSQL_PROFILER_UNITS, then PLSQL_PROFILER_RUNS.
  DELETE FROM plsql_profiler_data WHERE runid = :your_runid;
DELETE FROM plsql_profiler_units WHERE runid = :your_runid;
DELETE FROM plsql_profiler_runs WHERE runid = :your_runid;
• Preserve Each Run:
  Don’t overwrite; instead, maintain multiple runs for historical analysis and improvement tracking.

6. Conclusion: From Shadows to Symphony

The path to high-performing PL/SQL is iterative, scientific, and data-driven.
With DBMS_PROFILER, you are no longer tuning in the dark, but orchestrating performance—one line, one loop, one insight at a time.
Performance tuning thus becomes less of a desperate race and more of a graceful, evidence-led waltz.

“Let every bottleneck you unveil become a stanza in the poetry of optimization. May your PL/SQL code sing in harmony with your business needs.”

---

Would you like the example scripts, profiler queries, or an Excel template for iterative tracking? Share your thoughts, questions, and success stories in the comments below!

---

If you found this guide valuable, share it with your colleagues and fellow DBAs. Together, let’s create a culture where performance is not a mystery, but a measurable, repeatable triumph.