Enterprise PL/SQL Tuning, NOCOPY & Function Caching

🏎️ PL/SQL Compiler Optimization Levels

The Oracle PL/SQL compiler features an aggressive optimization engine controlled by the session parameter PLSQL_OPTIMIZE_LEVEL:

• Analogy: Supercharged Race Car Engine Tuning.
  • Level 0: Running a car engine with standard factory limiters.
  • Level 2 (Default): Re-arranging internal code expressions, restructuring loops, and optimizing context switches automatically.
  • Level 3 (Aggressive Optimization): Installing a twin-turbocharger. Inlines small subprograms directly into invocation sites to eliminate call stack overhead!

-- Setting aggressive compiler optimization level (Enables subprogram inlining & loop unrolling)
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

🚀 Memory Pass-By-Reference: The NOCOPY Hint

By default, when passing IN OUT or OUT parameters (such as large collections containing 100,000 records) to a procedure, PL/SQL uses Pass-By-Value:

                          Pass-By-Value vs. Pass-By-Reference
                                           │
        ┌──────────────────────────────────┴──────────────────────────────────┐
        ▼                                                                     ▼
Default Pass-By-Value (Slow & Memory Intensive)             Pass-By-Reference (NOCOPY Hint)
Allocates temporary memory copy of entire collection        Passes direct memory pointer to original collection
If procedure succeeds, copies temp array back               Zero memory allocation; sub-second performance

Adding the NOCOPY compiler hint instructs Oracle to use Pass-By-Reference, passing a memory pointer to the original collection:

DECLARE
   TYPE t_large_array IS TABLE OF VARCHAR2(1000);
   v_data t_large_array := t_large_array();

   -- Fast parameter passing using NOCOPY pass-by-reference hint!
   PROCEDURE process_big_data (p_collection IN OUT NOCOPY t_large_array) IS
   BEGIN
      -- Modifies original memory directly without creating PGA RAM copies!
      p_collection.EXTEND;
      p_collection(p_collection.COUNT) := 'New Element';
   END process_big_data;
BEGIN
   process_big_data(v_data);
END;
/

⚡ Function Result Caching (RESULT_CACHE)

When a complex PL/SQL function calculates heavy metrics (e.g., tax rules or currency conversions) repeatedly across millions of query rows, calling the function continuously degrades CPU speed.

Oracle provides Function Result Caching (RESULT_CACHE) to cache input-to-output parameter results directly in System Global Area (SGA) shared memory:

• Analogy: A Smart Vault Cache Calculator. When requested calculate_tax(100, 'US'), the function computes 15.00 and saves the answer in SGA RAM. Next time ANY user requests calculate_tax(100, 'US'), the result is returned instantly in nanoseconds without executing the function code!

CREATE OR REPLACE FUNCTION get_department_budget (
    p_dept_id IN NUMBER
) RETURN NUMBER
RESULT_CACHE -- Caches return values in SGA shared memory across all sessions!
IS
   v_budget NUMBER;
BEGIN
   -- Heavy analytical aggregation query
   SELECT SUM(salary * 1.25) INTO v_budget
   FROM employees
   WHERE department_id = p_dept_id;

   RETURN v_budget;
END get_department_budget;
/

[!TIP] Automatic Cache Invalidation: Oracle automatically tracks underlying table modifications! If an UPDATE executes on the employees table, Oracle automatically invalidates stale entries in the RESULT_CACHE so users never read stale budget numbers.

❓ Conceptual Quizzes

💻 Practice Problems