Efficient PL/SQL for Large Oracle Databases — Course Plan

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.

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.

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).

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.

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.

-- 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.

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.

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.

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".

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.

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.

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.

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.

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.

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.

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.

-- 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.

-- 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.

-- "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.

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), DBMS_XPLAN.DISPLAY_CURSOR on the heavy SQL (deep dive).