# Efficient PL/SQL for Large Oracle Databases — Course Plan

> **The one sentence:** The code runs in the database — not in the loop.

## About the Course

| | |
| --- | --- |
| **Goal** | Teach PL/SQL developers to write code that behaves well on hundreds of millions of rows — not just on 10,000. |
| **Audience** | Developers who already write PL/SQL: blocks, cursors, procedures, packages. There is no basics chapter. |
| **Version** | Oracle 19c and up. Every command is available from 12c. |
| **Structure** | 10 chapters. Each chapter = one principle, a bad example vs. a recommended one, and commands linked to the [appendix](appendix.md). |
| **Duration** | 20–30 minutes per chapter. One working day in total. |

### The Thesis in Three Lines

1. **SQL first.** If it can be written as a single SQL statement — it is not PL/SQL.
2. **Bulk when PL/SQL is unavoidable.** Read and write in batches, never row by row.
3. **Always measure.** Without a number there is no optimization — only guessing.

### Shared Example Schema

Every example in the course and the appendix runs on the same tables. Learn the context once.

| Table | Columns | Size |
| --- | --- | --- |
| `customers` | `customer_id` (PK), `status`, `tier`, `last_order_date` | 5M rows |
| `orders` | `order_id` (PK), `customer_id`, `order_date`, `amount`, `status` | 500M rows, partitioned by `order_date` (monthly: `p_2024_12`, …) |
| `orders_archive` | same as `orders` | archive |
| `error_log` | `logged_at`, `ctx`, `err_code`, `err_msg`, `backtrace` | error log |

Business procedures that appear in the examples (`pricing_pkg.reprice`, `process_order`, `apply_discount`, `load_orders`) — their bodies do not matter; they stand for "logic that genuinely needs PL/SQL".

### How to Read a Chapter

```text
## Chapter N — Title
Principle          — one sentence.
What breaks at scale — why it hurts specifically at 500M rows.
❌ Bad             — code + why.
✅ Recommended     — code + why.
Commands           — each linked to the appendix: syntax + example + note.
Rule of thumb      — one sentence to remember.
```

### Chapter List

1. SQL First — one statement instead of a loop
2. Bulk — read and write in batches
3. Functions Inside SQL — minimize context switches
4. In-Memory Lookup — load once, don't query in a loop
5. COMMIT Strategy — never COMMIT per row
6. Dynamic SQL — bind, don't concatenate
7. Exceptions — don't hide errors
8. Locking — short transactions, never wait on a lock
9. Huge Tables — direct path, parallel, chunks
10. Measure — don't guess

---

## Chapter 1 — SQL First

**Principle:** One SQL statement instead of a loop of statements.

**What breaks at scale:** A loop over 5M customers = 10M single-row statements, each one a switch between the PL/SQL and SQL engines. The same work as one MERGE = one switch, and the optimizer picks a hash join and a parallel full scan instead of 10M index probes.

### ❌ Bad

```sql
DECLARE
  v_cnt NUMBER;
BEGIN
  FOR r IN (SELECT customer_id, MAX(order_date) AS last_dt
            FROM   orders GROUP BY customer_id) LOOP
    SELECT COUNT(*) INTO v_cnt
    FROM   customers WHERE customer_id = r.customer_id;
    IF v_cnt > 0 THEN
      UPDATE customers
      SET    last_order_date = r.last_dt
      WHERE  customer_id = r.customer_id;
    END IF;
  END LOOP;
  COMMIT;
END;
```

Why:

- Two statements per customer — 10M round-trips between the PL/SQL engine and the SQL engine.
- `COUNT(*)` to test existence — counts every match when one is enough.

### ✅ Recommended

```sql
MERGE INTO customers c
USING (SELECT customer_id, MAX(order_date) AS last_dt
       FROM   orders
       GROUP  BY customer_id) o
ON    (c.customer_id = o.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.last_order_date = o.last_dt;

-- Existence check: EXISTS stops at the first row found
SELECT CASE WHEN EXISTS (SELECT 1 FROM orders WHERE customer_id = :id)
            THEN 1 ELSE 0 END
INTO   v_has_orders
FROM   dual;
```

Why:

- One statement, one switch; the optimizer sees the whole picture and picks a set-based plan.
- No "customer does not exist" case to handle — the `ON` clause does it.

**Commands:** [`MERGE`](appendix.md#merge) · [`INSERT SELECT`](appendix.md#insert-select) · [`UPDATE WHERE EXISTS`](appendix.md#update-where-exists) · [`EXISTS`](appendix.md#exists) · [`FETCH FIRST`](appendix.md#fetch-first)

**Rule of thumb:** If it can be written as one SQL statement — it is not PL/SQL.

---

## Chapter 2 — Bulk

**Principle:** When PL/SQL is unavoidable — read in batches (`BULK COLLECT … LIMIT`) and write in batches (`FORALL`).

**What breaks at scale:** Every `FETCH` and every single-row DML is a context switch. On 500M rows that is a billion switches — most of the runtime is overhead, not work.

### ❌ Bad

```sql
DECLARE
  CURSOR c IS SELECT order_id, amount FROM orders WHERE status = 'NEW';
  r c%ROWTYPE;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO r;
    EXIT WHEN c%NOTFOUND;
    UPDATE orders
    SET    amount = pricing_pkg.reprice(r.order_id, r.amount)
    WHERE  order_id = r.order_id;
  END LOOP;
  CLOSE c;
  COMMIT;
END;
```

Why:

- One fetch + one update per row: twice as many context switches as rows.
- `pricing_pkg.reprice` is called from inside the UPDATE — another switch per row (see Chapter 3).

### ✅ Recommended

```sql
DECLARE
  CURSOR c IS SELECT order_id, amount FROM orders WHERE status = 'NEW';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
    EXIT WHEN l_rows.COUNT = 0;
    FOR i IN 1 .. l_rows.COUNT LOOP                       -- logic in PL/SQL, in memory
      l_rows(i).amount := pricing_pkg.reprice(l_rows(i).order_id, l_rows(i).amount);
    END LOOP;
    FORALL i IN 1 .. l_rows.COUNT                         -- one DML for the whole batch
      UPDATE orders SET amount = l_rows(i).amount
      WHERE  order_id = l_rows(i).order_id;
    COMMIT;
  END LOOP;
  CLOSE c;
END;
```

Why:

- 10,000 rows = 2 switches (fetch + FORALL) instead of 20,000.
- `LIMIT` keeps PGA flat; without it — 500M rows in session memory.

**Commands:** [`BULK COLLECT`](appendix.md#bulk-collect) · [`LIMIT`](appendix.md#limit) · [`FORALL`](appendix.md#forall) · [`SAVE EXCEPTIONS`](appendix.md#save-exceptions) · [`CURSOR FOR LOOP`](appendix.md#cursor-for-loop)

**Rule of thumb:** PL/SQL touches data only in batches — `BULK COLLECT LIMIT` in, `FORALL` out.

---

## Chapter 3 — Functions Inside SQL

**Principle:** A PL/SQL function inside SQL is a hidden loop. Logic in SQL first; a function only when there is no alternative — and then declared correctly.

**What breaks at scale:** A function in the `WHERE` clause on `orders` is called 500M times. If it runs its own `SELECT` — that is 500M hidden queries, and none of them show up in the execution plan.

### ❌ Bad

```sql
CREATE OR REPLACE FUNCTION is_vip (p_customer_id NUMBER) RETURN NUMBER IS
  v_tier customers.tier%TYPE;
BEGIN
  SELECT tier INTO v_tier FROM customers WHERE customer_id = p_customer_id;
  RETURN CASE WHEN v_tier = 'VIP' THEN 1 ELSE 0 END;
END;
/
SELECT SUM(amount)
FROM   orders
WHERE  is_vip(customer_id) = 1;      -- 500M calls, each with its own SELECT
```

Why:

- A context switch per row + a SELECT per row — the plan shows an "innocent" full scan and hides the real cost.
- The optimizer cannot use an index or a join — the logic is a black box.

### ✅ Recommended

```sql
-- 1. Logic in SQL: a JOIN instead of a function
SELECT SUM(o.amount)
FROM   orders o
JOIN   customers c ON c.customer_id = o.customer_id
WHERE  c.tier = 'VIP';

-- 2. When a function is unavoidable (pure computation): declare it so the engine can save calls
CREATE OR REPLACE FUNCTION vat_amount (p_amount NUMBER) RETURN NUMBER
  DETERMINISTIC
IS
  PRAGMA UDF;
BEGIN
  RETURN ROUND(p_amount * 0.17, 2);
END;
/
```

Why:

- The JOIN lets the optimizer pick a hash join — one scan of each table.
- `DETERMINISTIC` lets Oracle skip repeated calls on the same input; `PRAGMA UDF` makes the SQL→PL/SQL switch cheaper. For a stable lookup table — `RESULT_CACHE`. For a one-off query — `WITH FUNCTION`.

**Commands:** [`DETERMINISTIC`](appendix.md#deterministic) · [`RESULT_CACHE`](appendix.md#result_cache) · [`PRAGMA UDF`](appendix.md#pragma-udf) · [`WITH FUNCTION`](appendix.md#with-function)

**Rule of thumb:** A function in `WHERE` = a hidden loop. JOIN/CASE first; a function only when there is no alternative — then `DETERMINISTIC` + `PRAGMA UDF`.

---

## Chapter 4 — In-Memory Lookup

**Principle:** A lookup table queried inside a loop — load it once into an in-memory collection and query that.

**What breaks at scale:** `SELECT INTO` inside a loop over 500M orders = 500M queries against `customers`. Even with a perfect index — each one costs a context switch, a cursor lookup and 3–4 block reads.

### ❌ Bad

```sql
DECLARE
  v_tier customers.tier%TYPE;
BEGIN
  FOR r IN (SELECT order_id, customer_id, amount FROM orders WHERE status = 'NEW') LOOP
    SELECT tier INTO v_tier
    FROM   customers WHERE customer_id = r.customer_id;   -- 500M times
    IF v_tier = 'VIP' THEN
      apply_discount(r.order_id, r.amount);
    END IF;
  END LOOP;
END;
```

Why:

- The same question ("what is customer X's tier") is asked hundreds of times for the same customer.
- Every answer goes through the SQL engine although it could sit in session memory.

### ✅ Recommended

```sql
DECLARE
  TYPE t_tier_map IS TABLE OF customers.tier%TYPE INDEX BY PLS_INTEGER;
  l_tier t_tier_map;
BEGIN
  -- Load once. Only what is needed (VIP) — small, fits in PGA.
  FOR c IN (SELECT customer_id, tier FROM customers WHERE tier = 'VIP') LOOP
    l_tier(c.customer_id) := c.tier;
  END LOOP;

  FOR r IN (SELECT order_id, customer_id, amount FROM orders WHERE status = 'NEW') LOOP
    IF l_tier.EXISTS(r.customer_id) THEN                  -- O(1), no SQL
      apply_discount(r.order_id, r.amount);
    END IF;
  END LOOP;                                               -- (the loop itself — Bulk, Chapter 2)
END;
```

Why:

- One query against `customers` instead of 500M; the lookup is an in-memory hash.
- When passing the collection to another procedure — `IN OUT NOCOPY`, otherwise it gets copied.

**Commands:** [`ASSOCIATIVE ARRAY`](appendix.md#associative-array) · [`COLLECTION METHODS`](appendix.md#collection-methods) · [`NOCOPY`](appendix.md#nocopy)

**Rule of thumb:** A small lookup table read in a loop — into memory once. A large one — JOIN (Chapter 1).

---

## Chapter 5 — COMMIT Strategy

**Principle:** COMMIT per batch, never per row. And if UNDO allows — once at the end.

**What breaks at scale:** Every COMMIT waits for the log writer (`log file sync`). 500M commits = 500M I/O waits. On top of that, COMMIT inside a loop over an open cursor is the classic cause of `ORA-01555 snapshot too old` — the job dies after 3 hours, halfway through.

### ❌ Bad

```sql
BEGIN
  FOR r IN (SELECT order_id FROM orders WHERE status = 'NEW') LOOP
    UPDATE orders SET status = 'PROCESSED' WHERE order_id = r.order_id;
    COMMIT;      -- 500M times: log file sync per row + ORA-01555 on the open cursor
  END LOOP;
END;
```

Why:

- Runtime is dominated by redo waits, not by the work.
- No rollback possible: a failure halfway leaves half the data updated with no "starting point".

### ✅ Recommended

```sql
DECLARE
  CURSOR c IS SELECT order_id FROM orders WHERE status = 'NEW';   -- restartable: done rows leave the set
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_ids LIMIT 50000;
    EXIT WHEN l_ids.COUNT = 0;
    FORALL i IN 1 .. l_ids.COUNT
      UPDATE orders SET status = 'PROCESSED' WHERE order_id = l_ids(i);
    COMMIT;                                  -- once per 50K rows: ~10K commits instead of 500M
  END LOOP;
  CLOSE c;
END;
```

Why:

- The COMMIT is a recovery point: on failure, rerun — `WHERE status = 'NEW'` skips what is already done.
- Batch size balances UNDO against commit count. 10K–100K is a sensible range; beyond that — coordinate with the DBA.

**Commands:** [`COMMIT`](appendix.md#commit) · [`SAVEPOINT`](appendix.md#savepoint)

**Rule of thumb:** COMMIT per batch, never per row. A batch = a unit of work that can be repeated.

---

## Chapter 6 — Dynamic SQL

**Principle:** Values go in as binds (`USING`), not concatenated. Identifiers (table names) go through `DBMS_ASSERT`.

**What breaks at scale:** Every concatenated value = new SQL text = new hard parse = new shared pool entry. 100K distinct values = 100K plans in the library cache, latch contention, and eventually `ORA-04031`. Plus SQL injection for free.

### ❌ Bad

```sql
PROCEDURE archive_year (p_year NUMBER, p_status VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE
    'INSERT INTO orders_archive_' || p_year ||
    ' SELECT * FROM orders WHERE status = ''' || p_status || '''' ||
    ' AND EXTRACT(YEAR FROM order_date) = ' || p_year;
END;
```

Why:

- `p_status` is concatenated: every value = a hard parse; and `p_status := 'X'' OR 1=1--'` does whatever it wants.
- `EXTRACT(YEAR …)` on the partition key disables partition pruning — 500M rows scanned instead of 40M.

### ✅ Recommended

```sql
PROCEDURE archive_year (p_year NUMBER, p_status VARCHAR2) IS
  l_table VARCHAR2(128) := DBMS_ASSERT.SQL_OBJECT_NAME('orders_archive_' || p_year);
BEGIN
  EXECUTE IMMEDIATE
    'INSERT INTO ' || l_table ||
    ' SELECT * FROM orders WHERE status = :1' ||
    ' AND order_date >= :2 AND order_date < :3'
    USING p_status,
          TO_DATE(p_year || '0101', 'YYYYMMDD'),
          TO_DATE((p_year + 1) || '0101', 'YYYYMMDD');
END;
```

Why:

- One SQL text for all years and statuses → one parse, one plan in the shared pool.
- `DBMS_ASSERT.SQL_OBJECT_NAME` raises `ORA-44002` if the table does not exist — no way to inject a name.
- A date range on the partition key → pruning works.

**Commands:** [`EXECUTE IMMEDIATE`](appendix.md#execute-immediate) · [`OPEN FOR`](appendix.md#open-for) · [`DBMS_ASSERT`](appendix.md#dbms_assert) · [`DBMS_SQL`](appendix.md#dbms_sql)

**Rule of thumb:** Values → bind (`USING`). Identifiers → `DBMS_ASSERT`. A value concatenated into a SQL string = a bug.

---

## Chapter 7 — Exceptions

**Principle:** Never swallow errors. An expected error — a specific handler. Everything else — log and `RAISE`.

**What breaks at scale:** `WHEN OTHERS THEN NULL` in a loop over 500M rows = 3M orders that failed silently, and nobody knows until the customer calls. And in a nightly job — the job "succeeded".

### ❌ Bad

```sql
BEGIN
  FOR r IN (SELECT order_id, amount FROM orders WHERE status = 'NEW') LOOP
    BEGIN
      process_order(r.order_id, r.amount);
    EXCEPTION
      WHEN OTHERS THEN NULL;   -- "so it won't crash". And we never learn about 3M failed rows.
    END;
  END LOOP;
END;
```

Why:

- The error, the row that caused it and the stack — all gone. No way to recover.
- `WHEN OTHERS` also catches `ORA-01555`, `ORA-04031`, `ORA-00060` — infrastructure errors that must stop the job.

### ✅ Recommended

```sql
CREATE OR REPLACE PROCEDURE log_error (
  p_ctx VARCHAR2, p_code NUMBER, p_msg VARCHAR2, p_backtrace VARCHAR2 DEFAULT NULL) IS
  PRAGMA AUTONOMOUS_TRANSACTION;                          -- the log survives even if the job rolls back
BEGIN
  INSERT INTO error_log (logged_at, ctx, err_code, err_msg, backtrace)
  VALUES (SYSTIMESTAMP, p_ctx, p_code, p_msg, p_backtrace);
  COMMIT;
END;
/
BEGIN
  process_order(p_order_id, p_amount);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;                                                 -- expected case, decided deliberately
  WHEN OTHERS THEN
    log_error('process_order ' || p_order_id, SQLCODE, SQLERRM,
              DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;                                                -- don't swallow — the caller decides
END;
```

Why:

- An expected error is handled by name, with an explicit business decision. Everything else — logged with the exact line (backtrace) and re-raised.
- In bulk processing (Chapter 2) — `SAVE EXCEPTIONS` gives the same thing at batch level.

**Commands:** [`EXCEPTION`](appendix.md#exception) · [`RAISE_APPLICATION_ERROR`](appendix.md#raise_application_error) · [`PRAGMA EXCEPTION_INIT`](appendix.md#pragma-exception_init) · [`FORMAT_ERROR_BACKTRACE`](appendix.md#format_error_backtrace) · [`PRAGMA AUTONOMOUS_TRANSACTION`](appendix.md#pragma-autonomous_transaction)

**Rule of thumb:** `WHEN OTHERS` without `RAISE` or a log = a bug. Expected error — specific handler; everything else — log and `RAISE`.

---

## Chapter 8 — Locking

**Principle:** Lock little, lock late, lock briefly. `SKIP LOCKED` lets several workers run on the same table without colliding.

**What breaks at scale:** `FOR UPDATE` on 50M rows followed by hours of processing = every application session touching those rows is stuck. And if two jobs do it — deadlock (`ORA-00060`) or an endless queue.

### ❌ Bad

```sql
DECLARE
  CURSOR c IS SELECT order_id FROM orders WHERE status = 'NEW' FOR UPDATE;
BEGIN
  FOR r IN c LOOP                          -- locks all 50M NEW rows at once
    process_order(r.order_id);             -- long processing: everyone who needs a row waits
    UPDATE orders SET status = 'DONE' WHERE CURRENT OF c;
  END LOOP;
  COMMIT;                                  -- the lock is released only here — hours later
END;
```

Why:

- The lock lasts the whole life of the job. A second worker on the same table waits or fails.
- No parallelism possible — all 500M rows on one thread.

### ✅ Recommended

```sql
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  LOOP
    SELECT order_id BULK COLLECT INTO l_ids
    FROM   orders
    WHERE  status = 'NEW' AND ROWNUM <= 1000
    FOR UPDATE SKIP LOCKED;                -- locks one batch only; skips what another worker holds
    EXIT WHEN l_ids.COUNT = 0;
    FOR i IN 1 .. l_ids.COUNT LOOP
      process_order(l_ids(i));
    END LOOP;
    FORALL i IN 1 .. l_ids.COUNT
      UPDATE orders SET status = 'DONE' WHERE order_id = l_ids(i);
    COMMIT;                                -- the lock lives seconds, not hours
  END LOOP;
END;
```

Why:

- Each batch = a short transaction holding 1,000 locks; the COMMIT releases them immediately.
- The same code runs in 8 sessions in parallel: `SKIP LOCKED` divides the work between them automatically, with no coordination.

**Commands:** [`FOR UPDATE`](appendix.md#for-update)

**Rule of thumb:** Lock little, lock late, lock briefly. `SKIP LOCKED` = several workers on one table without collisions.

---

## Chapter 9 — Huge Tables

**Principle:** On tens of millions of rows use the database's tools — direct path, parallel, partitions — not a loop.

**What breaks at scale:** A `DELETE` of 40M rows = 40M undo + redo records, hours, and indexes maintained row by row. A regular `INSERT` of 40M = through the buffer cache, block by block. And a single PL/SQL loop over 500M — one thread, 8 hours, while the server has 64 cores.

### ❌ Bad

```sql
-- Archive a year: 40M rows, row by row, plus a DELETE generating 40M undo + redo
BEGIN
  FOR r IN (SELECT * FROM orders WHERE order_date < DATE '2025-01-01') LOOP
    INSERT INTO orders_archive VALUES r;
    DELETE FROM orders WHERE order_id = r.order_id;
  END LOOP;
  COMMIT;
END;
```

Why:

- 80M single-row statements, through the buffer cache, with full undo. One core works, 63 idle.
- The DELETE does not release space — the table stays the same size, with holes.

### ✅ Recommended

```sql
-- a. Archive a whole partition = metadata swap. Zero data copied.
ALTER TABLE orders EXCHANGE PARTITION p_2024_12
  WITH TABLE orders_archive_2024_12 INCLUDING INDEXES WITHOUT VALIDATION;

-- b. When copying is unavoidable: direct path + parallel (bypasses buffer cache, minimal undo)
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(a, 8) */ INTO orders_archive a
SELECT /*+ PARALLEL(o, 8) */ * FROM orders o WHERE o.order_date < DATE '2025-01-01';
COMMIT;
ALTER TABLE orders TRUNCATE PARTITION p_2024_12 UPDATE INDEXES;   -- not DELETE

-- c. When PL/SQL per row is unavoidable: chunks in parallel
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK('reprice');
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('reprice', USER, 'ORDERS', TRUE, 100000);
  DBMS_PARALLEL_EXECUTE.RUN_TASK('reprice',
    'UPDATE orders SET amount = pricing_pkg.reprice(order_id, amount)
      WHERE rowid BETWEEN :start_id AND :end_id', DBMS_SQL.NATIVE, parallel_level => 8);
END;
```

Why:

- EXCHANGE PARTITION: 40M rows in a millisecond. This is why `orders` is partitioned by `order_date`.
- APPEND + PARALLEL: 8 cores writing straight to the datafiles. TRUNCATE PARTITION: DDL, seconds, no undo.
- DBMS_PARALLEL_EXECUTE: splits the table into rowid chunks, runs 8 at a time, each chunk with its own COMMIT and resume on failure.

**Commands:** [`APPEND HINT`](appendix.md#append-hint) · [`PARALLEL DML`](appendix.md#parallel-dml) · [`DBMS_PARALLEL_EXECUTE`](appendix.md#dbms_parallel_execute) · [`TRUNCATE`](appendix.md#truncate) · [`EXCHANGE PARTITION`](appendix.md#exchange-partition)

**Rule of thumb:** Millions of rows: not `DELETE` — `TRUNCATE`/`EXCHANGE`; not a plain `INSERT` — `APPEND` + `PARALLEL`; not one loop — chunks.

---

## Chapter 10 — Measure

**Principle:** Every job identifies itself (module/action) and times itself. Without a number — no optimization.

**What breaks at scale:** "The job takes 4 hours" — but which step? Which SQL? The DBA sees a session in `V$SESSION` with `module = 'SQL*Plus'` and anonymous SQL. Optimization becomes guessing, and the guess is usually wrong.

### ❌ Bad

```sql
-- "The job takes 4 hours." Where? No idea.
BEGIN
  load_orders;
  reprice_orders;
  archive_orders;
END;
```

Why:

- No steps, no timings, no identification in `V$SQL`/AWR. Every SQL of the job looks like any other SQL.

### ✅ Recommended

```sql
DECLARE
  t0 PLS_INTEGER;
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('nightly_batch', 'load');
  t0 := DBMS_UTILITY.GET_TIME;
  load_orders;
  DBMS_OUTPUT.PUT_LINE('load: ' || (DBMS_UTILITY.GET_TIME - t0) / 100 || 's');

  DBMS_APPLICATION_INFO.SET_ACTION('reprice');
  t0 := DBMS_UTILITY.GET_TIME;
  reprice_orders;
  DBMS_OUTPUT.PUT_LINE('reprice: ' || (DBMS_UTILITY.GET_TIME - t0) / 100 || 's');

  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;
/
-- Now every SQL of the job is tagged, and you can ask "what is heaviest":
SELECT sql_id, executions, elapsed_time/1e6 AS sec, SUBSTR(sql_text, 1, 60) AS txt
FROM   v$sql WHERE module = 'nightly_batch'
ORDER  BY elapsed_time DESC FETCH FIRST 5 ROWS ONLY;
```

Why:

- A time per step → you know where 3.5 of the 4 hours go. (In production — a log table, not `DBMS_OUTPUT`.)
- `module`/`action` show up in `V$SESSION`, `V$SQL`, AWR, ASH → the DBA and you talk about the same thing.
- Next step: `DBMS_HPROF` for a per-function profile ([deep dive](deep-dives/dbms_hprof.md)), `DBMS_XPLAN.DISPLAY_CURSOR` on the heavy SQL ([deep dive](deep-dives/dbms_xplan.md)).

**Commands:** [`DBMS_APPLICATION_INFO`](appendix.md#dbms_application_info) · [`DBMS_UTILITY GET_TIME`](appendix.md#dbms_utility-get_time) · [`DBMS_HPROF`](appendix.md#dbms_hprof) · [`DBMS_XPLAN`](appendix.md#dbms_xplan)

**Rule of thumb:** Every job identifies and times itself. Without a number there is no optimization, only guessing.

---

## Deliberately Left Out

| Topic | Why not |
| --- | --- |
| Triggers | Not a performance tool. On huge tables they mostly cost — and hide logic. |
| Object Types, Pipelined functions | Niche. Useful for complex ETL, not for the core of this course. |
| Java / External procedures | Outside PL/SQL. |
| Indexing strategy, SQL tuning | A separate course. Here we assume the SQL is already tuned and deal with what wraps it. |
| Materialized Views, In-Memory, SQL Result Cache | DBA infrastructure solutions, not PL/SQL code. |
| `PLSQL_OPTIMIZE_LEVEL`, NATIVE compilation | Single-digit percent gains. Irrelevant when the problem is row-by-row. |