Using Oracle Result Cache to Avoid Repeated Work on Deterministic Queries

Many applications run the same reporting query over and over—think of a daily sales‑by‑region summary that only changes when the underlying data is updated. Each execution parses, optimizes, and reads the same blocks, consuming CPU and I/O even though the result is identical. Oracle Result Cache offers a way to store the output of such deterministic queries in the shared pool so that subsequent executions can be served from memory without re‑executing the statement.

How the Result Cache Works

When a SQL statement includes the /*+ RESULT_CACHE */ hint (or a PL/SQL function is declared with the RESULT_CACHE attribute), Oracle checks the result cache during the parse phase. If a matching entry exists and its underlying objects have not been modified, the cached result is returned immediately. If no match is found, the statement runs normally, and upon successful completion Oracle stores the result set in the cache, tagging it with the current SCN of the referenced objects. Any DML that changes one of those objects automatically invalidates the associated cache entries, keeping the cache coherent.

The cache lives in the shared pool, and its size is controlled by two initialization parameters:

• RESULT_CACHE_MAX_SIZE – total memory available for cached results.
• RESULT_CACHE_MAX_RESULT – maximum size of a single cached result.

If the pool runs out of space, Oracle evicts older entries using an LRU algorithm.

Enabling and Using the Cache

By default, RESULT_CACHE_MODE is set to MANUAL, meaning only statements with the hint or functions with the attribute are cached. You can change the mode at the system or session level:

-- Requires ALTER SYSTEM privilege
ALTER SYSTEM SET RESULT_CACHE_MODE = FORCE SCOPE=BOTH;

Setting it to FORCE causes Oracle to attempt caching for every query, but this can increase shared‑pool pressure, so MANUAL is usually safer for production.

Worked Example: Caching a Sales Summary Query

Consider a table SALES with columns REGION, PRODUCT_ID, SALE_DATE, and AMOUNT. The following query returns total sales per region for a given month:

SELECT /*+ RESULT_CACHE */
       REGION,
       SUM(AMOUNT) AS TOTAL_SALES
FROM   SALES
WHERE  SALE_DATE >= TO_DATE(:START_DT, 'YYYY-MM-DD')
  AND  SALE_DATE <  TO_DATE(:END_DT,   'YYYY-MM-DD')
GROUP BY REGION;

To verify that the cache is being used:

1. Run the query once with specific bind values (e.g., :START_DT = '2026-09-01', :END_DT = '2026-10-01').
2. Check the cache statistics:

SELECT NAME, VALUE
FROM   V$RESULT_CACHE_STATISTICS
WHERE  NAME IN ('CREATE_COUNT', 'FIND_COUNT');

After the first execution you should see CREATE_COUNT increase by 1 and FIND_COUNT remain 0. Run the same query again with identical bind values; the second execution should leave CREATE_COUNT unchanged while FIND_COUNT increments, indicating a cache hit.

You can also inspect the cached object directly:

SELECT CACHE_ID, STATUS, SCN, ROW_COUNT
FROM   V$RESULT_CACHE_OBJECTS
WHERE  OBJECT_NAME = 'YOUR_QUERY_HASH';

Replace YOUR_QUERY_HASH with the value shown in V$SQL for the statement.

Trade‑offs and Limitations

The Result Cache is powerful but not a universal speed‑up:

• Deterministic only: queries that depend on SYSDATE, sequences, package variables, or non‑deterministic PL/SQL functions will produce incorrect results if cached. Such expressions must be omitted or marked with /*+ NO_RESULT_CACHE */.
• Shared‑pool consumption: cached results occupy the same memory used for library cache, cursors, and other structures. Setting RESULT_CACHE_MAX_SIZE too high can lead to ORA‑04031: unable to allocate X bytes of shared memory errors.
• Object‑level invalidation: a change to any table referenced in the cached query flushes the entire entry, even if only a single row changed. For queries that touch large, frequently updated tables, the hit ratio may be low.

To gauge whether the cache is helping, monitor the ratio FIND_COUNT / (FIND_COUNT + CREATE_COUNT) in V$RESULT_CACHE_STATISTICS. A consistently low ratio suggests the workload is not a good fit.

Actionable Closing

If you have reporting or lookup queries that run repeatedly with the same bind values and whose underlying data changes infrequently, consider adding the /*+ RESULT_CACHE */ hint. After deploying:

1. Confirm the cache is active by checking FIND_COUNT growth.
2. Watch shared‑pool usage via V$SGASTAT or V$RESULT_CACHE_MEMORY to ensure you are not approaching the limit.
3. Adjust RESULT_CACHE_MAX_SIZE incrementally if evictions become frequent, but keep an eye on overall SGA size.
4. For PL/SQL functions, add the RESULT_CACHE attribute and verify determinism.

By aligning the cache with deterministic, read‑heavy workloads you can reduce CPU and I/O without rewriting application logic, while the automatic invalidation mechanism keeps the results consistent.