Boosting Oracle Performance with PL/SQL Result Cache

The post is meant as a clear, practical, and easy-to-follow article on the Oracle PL/SQL Result Cache, an often underused feature. As usual your comments or questions are welcomed directly on the blog page or by email at: database@blog.parvu.org Thank you!

What is the PL/SQL Result Cache?

The PL/SQL Result Cache is an Oracle database feature that allows caching the result of a function call in memory so that repeated calls with the same inputs can return instantly—without re-executing the function logic.

In simpler terms, think of it as: “Run once, store the result, reuse it many times.”

Most important use cases; why it matters

In my experience, the real systems I have tuned, often have:

• Repeated queries with the same or similar inputs
• Lookup functions (e.g., get customer name by ID)
• Configuration readers. E.g. functions that read periodically data from tables or file storing the configuration setup.
• Expensive calculations. E.g. calculate the profit of the sales of a certain product type during latest quarter

I noticed that many developers ignore the usage of this feature and run their workloads without caching, as a result:

The function runs every time causing unnecessary overhead.

But, in my experience, with result cache in use, First call executes normally and all subsequent calls return from memory resulting in a→ huge performance gain

Basic syntax

The syntax is quite simple, below are simple examples for creating and using the function:

CREATE OR REPLACE FUNCTION function_name (p_input ...)
RETURN datatype
RESULT_CACHE
IS
BEGIN
    -- logic
END;
/

That’s it. After the function is defined, Oracle handles the caching automatically!

Simple example

Create a function

CREATE OR REPLACE FUNCTION get_employee_name (p_emp_id NUMBER)
RETURN VARCHAR2
RESULT_CACHE
IS
    v_name VARCHAR2(100);
BEGIN
    SELECT name INTO v_name
    FROM emp
    WHERE emp_id = p_emp_id;
    DBMS_OUTPUT.PUT_LINE('Function executed for ID: ' || p_emp_id);
    RETURN v_name;
END;
/

Call the function

SET SERVEROUTPUT ON;
BEGIN
    DBMS_OUTPUT.PUT_LINE(get_employee_name(1));
    DBMS_OUTPUT.PUT_LINE(get_employee_name(1));
    DBMS_OUTPUT.PUT_LINE(get_employee_name(1));
END;
/

Example output:

Notice:

• The function execution message prints only once.
• Subsequent calls use the cached result

How caching works

Oracle caches results based on:

• Function name
• Input parameters

So:

get_employee_name(1) is cached separately
get_employee_name(2) is another cache entry

very important: Automatic invalidation

Oracle automatically invalidates cached results when underlying tables change.

Cache invalidation, back to our example:

If the function depends on emp:

UPDATE emp SET name = 'New Name' WHERE emp_id = 1;
COMMIT;

Oracle will:

• Invalidate cached result
• Recompute the results on next call

This observation is important when troubleshooting complex workloads with continuous changes on the underlying tables.

Explicit dependency control

The developer can declare dependencies explicitly. Below is a simple example followed by the original example above modified to define dependency on a totally different table (demo_part):

CREATE OR REPLACE FUNCTION get_employee_name (p_emp_id NUMBER)
RETURN VARCHAR2
RESULT_CACHE RELIES_ON (emp)
IS
    v_name VARCHAR2(100);
BEGIN
    SELECT name INTO v_name
    FROM emp
    WHERE emp_id = p_emp_id;
    RETURN v_name;
END;
/

Note:

• RELIES_ON is optional in modern Oracle versions
• Oracle usually tracks dependencies automatically

When to use result cache

Good use cases:

• Lookup tables (countries, statuses, config)
• Rarely changing data
• Deterministic logic
• Repeated function calls with same inputs

Avoid when:

• Data changes frequently
• High variability in inputs will result in low cache reuse

Look out for!

A result-cached function must be deterministic in behavior

Also, be very careful and limited with the usage of SYSDATE/SYSTIMESTAMP, session variables

Monitoring the result cache

You can inspect cache usage:

SELECT *
FROM V$RESULT_CACHE_OBJECTS;

SELECT *
FROM V$RESULT_CACHE_STATISTICS;

With application to our examples:

Clearing the cache

BEGIN
    DBMS_RESULT_CACHE.FLUSH;
END;
/

Performance insight

Result cache is:

• Stored in shared memory
• Shared across sessions
• Extremely fast (memory lookup vs SQL execution)

In high-read systems, this usage can reduce database load dramatically.

Best practices

• Keep functions simple and pure
• Cache small, frequently reused results
• Avoid large result sets
• Test with and without cache
• Monitor hit ratios

Key conclusion

The PL/SQL Result Cache is a low-effort, high-impact performance feature:

Adding  RESULT_CACHE, sometimes instantly reduces repeated computation therefore improve response time.