The Context Switch Penalty#

Oracle Database has two distinct engines:

1. The SQL Engine: Executes queries (SELECT, INSERT).
2. The PL/SQL Engine: Executes procedural code (IF/THEN, LOOP).

When you write a PL/SQL FOR loop that executes an INSERT statement inside the loop, the PL/SQL engine must hand the data over to the SQL engine, wait for the insert, and then take back control. This handover is called a Context Switch.

If you loop 1 million times, you cause 1 million context switches. This overhead will absolutely destroy your performance. The golden rule of PL/SQL tuning is: Minimize Context Switches.

Batching with BULK COLLECT and FORALL#

To eliminate context switches, we send data to the SQL engine in massive batches.

The Bad Way (Row-by-Row = Slow-by-Slow)

FOR r IN (SELECT emp_id, salary FROM employees WHERE status = 'ACTIVE') LOOP
  UPDATE payroll SET bonus = r.salary * 0.1 WHERE emp_id = r.emp_id;
END LOOP;

The Fast Way (BULK Processing)

DECLARE
  TYPE emp_list_t IS TABLE OF employees.emp_id%TYPE;
  TYPE sal_list_t IS TABLE OF employees.salary%TYPE;
  
  v_emps emp_list_t;
  v_sals sal_list_t;
BEGIN
  -- 1 Context switch to fetch all 10,000 rows into memory
  SELECT emp_id, salary BULK COLLECT INTO v_emps, v_sals 
  FROM employees WHERE status = 'ACTIVE';
  
  -- 1 Context switch to update all 10,000 rows
  FORALL i IN 1..v_emps.COUNT
    UPDATE payroll SET bonus = v_sals(i) * 0.1 WHERE emp_id = v_emps(i);
END;
/

By batching, we reduced 10,000 context switches down to just 2.

Function Caching (RESULT_CACHE)#

If you have a function that calculates a complex tax rate based on the current year, and you call it 50,000 times in a report, it executes 50,000 times. But the answer is always the same!

By adding the RESULT_CACHE clause to the function definition, Oracle executes the function once, saves the answer in shared memory, and returns the cached answer for the remaining 49,999 calls.

CREATE OR REPLACE FUNCTION get_tax_rate (p_year NUMBER) 
  RETURN NUMBER 
  RESULT_CACHE 
IS
  v_rate NUMBER;
BEGIN
  -- This expensive query only runs ONCE per year requested!
  SELECT complex_tax_calc INTO v_rate FROM tax_rules WHERE year = p_year;
  RETURN v_rate;
END;
/

If the tax_rules table is updated, Oracle automatically invalidates the cache and recalculates it on the next call.

Passing by Reference (NOCOPY)#

When you pass an IN OUT parameter to a procedure, Oracle makes a complete copy of the variable in memory. If you are passing a massive collection containing 10,000 records, this copying process consumes huge amounts of PGA memory and CPU.

You can tell Oracle to pass the variable by reference (just a memory pointer) using the NOCOPY compiler hint.

PROCEDURE process_massive_array (
  p_data IN OUT NOCOPY large_collection_type
) IS
BEGIN
  -- No memory is duplicated. We are modifying the original array directly.
  NULL;
END;

Common Gotchas#

Key Takeaways#