# Deep Dive — RESULT_CACHE

> A function whose answers are stored in the SGA, shared by every session, and thrown away automatically the moment the underlying table changes.

Used in: [Chapter 3 — Functions Inside SQL](../course-plan.md) · Appendix entry: [RESULT_CACHE](../appendix.md#result_cache) · Related: [DETERMINISTIC](deterministic.md), [ASSOCIATIVE ARRAY](associative-array.md), [PRAGMA UDF](pragma-udf.md)

---

## 1. What It Is and When

A `RESULT_CACHE` function stores each (arguments → result) pair in the **server result cache** (part of the shared pool). A second call with the same arguments — from any session — returns the stored result without running the body. Oracle tracks which tables the function read and invalidates the cached results when any of them changes.

The sweet spot: small, rarely changing lookup tables (configuration, code tables, tiers, rates) read by many sessions. Not for: big tables, volatile tables, high-cardinality arguments.

The SQL-level sibling is the `/*+ RESULT_CACHE */` hint on a query.

## 2. Syntax

```sql
CREATE [OR REPLACE] FUNCTION <name> (<params>) RETURN <type>
  RESULT_CACHE [RELIES_ON (<table>, ...)]     -- RELIES_ON is ignored since 11.2 (auto-tracked)
IS ... END;

-- management
DBMS_RESULT_CACHE.STATUS          -- ENABLED / DISABLED / BYPASS
DBMS_RESULT_CACHE.MEMORY_REPORT   -- usage
DBMS_RESULT_CACHE.FLUSH           -- drop everything
DBMS_RESULT_CACHE.INVALIDATE(owner, name)
DBMS_RESULT_CACHE.BYPASS(TRUE)    -- temporarily disable (e.g. during a bulk load)

-- parameters (DBA): RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MAX_RESULT (% per result), RESULT_CACHE_MODE
-- views: V$RESULT_CACHE_OBJECTS, V$RESULT_CACHE_STATISTICS, V$RESULT_CACHE_DEPENDENCY
```

Restrictions: no `OUT`/`IN OUT` parameters; arguments and result must be scalar, record, or collection of scalars (no LOB, ref cursor, object with LOB); no invoker's rights; no side effects (the body may not run when cached); pipelined functions excluded.

## 3. Examples on the Course Schema

### 3a. A lookup that belongs in the cache

```sql
CREATE OR REPLACE FUNCTION tier_discount (p_tier VARCHAR2) RETURN NUMBER
  RESULT_CACHE
IS
  v_pct NUMBER;
BEGIN
  SELECT discount_pct INTO v_pct FROM tier_rates WHERE tier = p_tier;   -- 4-row table
  RETURN v_pct;
END;
/
SELECT order_id, amount * (1 - tier_discount(c.tier) / 100) AS net
FROM   orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE  o.order_date >= DATE '2026-01-01';
```

4 distinct arguments → 4 cache entries → the `SELECT` inside the function runs 4 times for 40M rows, across every session. (The function *call* still happens 40M times; only the body is skipped — see [PRAGMA UDF](pragma-udf.md).)

### 3b. Watching it work

```sql
SELECT name, value FROM v$result_cache_statistics
WHERE  name IN ('Create Count Success', 'Find Count', 'Invalidation Count');

SELECT id, type, status, name, row_count, scan_count
FROM   v$result_cache_objects
WHERE  name LIKE '%TIER_DISCOUNT%';
-- status: Published = usable, Invalid = a dependency changed, Bypass = disabled
```

`Find Count` climbing while `Create Count` stays flat = hits.

### 3c. Invalidation is automatic — and total for that function

```sql
UPDATE tier_rates SET discount_pct = 12 WHERE tier = 'GOLD';
COMMIT;
-- every cached result of tier_discount is now Invalid, for every argument, in every session.
-- The next call per argument recomputes and republishes.
```

Any committed DML on any table the function read invalidates all its results. That is correct, and it is why volatile tables make the cache useless (constant churn).

### 3d. The wrong candidate

```sql
CREATE OR REPLACE FUNCTION customer_tier (p_id NUMBER) RETURN VARCHAR2
  RESULT_CACHE
IS ... SELECT tier INTO v FROM customers WHERE customer_id = p_id ... END;
```

5M distinct arguments → up to 5M cache entries competing for `RESULT_CACHE_MAX_SIZE` (typically a few hundred MB, shared with the SQL result cache); every update to `customers` invalidates all of them; and every miss takes the result-cache latch. Under load this is slower than the plain query. For this shape, use a join, or a session-level [ASSOCIATIVE ARRAY](associative-array.md).

### 3e. Bypass during a bulk load

```sql
BEGIN
  DBMS_RESULT_CACHE.BYPASS(TRUE);       -- this session: no cache reads or writes
  load_tier_rates;                      -- many updates → would invalidate constantly
  DBMS_RESULT_CACHE.BYPASS(FALSE);
END;
```

## 4. What Breaks at Scale

- **Latch contention.** The result cache is protected by a single latch (`Result Cache: RC Latch`). Many sessions missing at the same time (high-cardinality arguments, or right after an invalidation) serialize on it. Symptom: `latch free` waits on that latch in AWR.
- **Memory.** `RESULT_CACHE_MAX_SIZE` is shared by all result-cache users. A function with millions of distinct arguments evicts everyone else's entries.
- **RAC.** Each instance has its own cache; invalidations are propagated, but hits are per instance.
- **Invalidation storms.** A dependency table updated every few seconds → results are invalid most of the time → all cost, no benefit. Check `Invalidation Count` vs `Find Count`.
- **Per-call overhead remains.** The context switch SQL→PL/SQL still happens per row; the cache only removes the body's work. Combine with `PRAGMA UDF`, or inline the lookup as a join when the table is small anyway.

## 5. Pitfalls

1. Caching a function of `SYSDATE`, `SYS_CONTEXT`, `USER` — the cache ignores those inputs; every session gets the first session's answer.
2. Side effects in the body (logging, counters) — they stop happening once the result is cached.
3. Expecting `RELIES_ON` to matter — ignored since 11.2; dependencies are tracked automatically, including through views.
4. `RESULT_CACHE_MAX_SIZE = 0` — the cache is disabled at instance level; the function silently runs uncached (`DBMS_RESULT_CACHE.STATUS` says `DISABLED`).
5. Result larger than `RESULT_CACHE_MAX_RESULT` (default 5% of the cache) — not cached, silently.
6. Functions on tables with VPD policies or on remote tables (database links) — not cached.
7. Using it as "free memoization" for a per-row lookup on a big table — see 3d.

## 6. Related

- [DETERMINISTIC](deterministic.md) — call elimination within a statement (no table reads allowed).
- [ASSOCIATIVE ARRAY](associative-array.md) — session-private cache for per-row lookups.
- [PRAGMA UDF](pragma-udf.md) — reducing the remaining per-call cost.