PL/SQL Function Results Cache - A Smart Memory for Your Database

This week's throwback feature of the week is the PL/SQL Function Results Cache. This feature lets you easily cache commonly calculated function results.

Using the PL/SQL Function Results Cache can lead to a huge boost in response time / performance of PL/SQL functions. The results of the functions can be reused by any other sessions calling the same function.

This blog walks through a practical example of how to use the feature.

Why would you want a results cache?

The PL/SQL Function Result Cache is a performance optimization feature in Oracle Database that can lead to a significant boost in response time and reduce the computational overhead for repetitive function calls.

In simple terms, this means faster response times, better performance, and less CPU usage for applications or queries that repeatedly call the same PL/SQL functions with the same input parameters.

Real-World Use Case

Imagine a web store that calculates sales tax based on the customer's zip code when they are ready to check out. Each checkout repeatedly checks the same zip code tax rate, meaning we are repeating calculations. By caching these results, the function doesn't recalculate the same data. This speeds up the checkout process and can improve the web users satisfaction.

PL/SQL Function Results Cache in Action

Let's see this with an easy-to-follow example.

Step 1: Create Test Table and Populate Data

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    salary NUMBER
);

-- Insert test data
INSERT INTO employees (emp_id, salary)
SELECT LEVEL, ROUND(DBMS_RANDOM.VALUE(30000, 150000), 2)
FROM dual CONNECT BY LEVEL <= 10000;

COMMIT;

Like what you see? Check out the free VSCode SQL Developer extension here: https://marketplace.visualstudio.com/items?itemName=Oracle.sql-developer

Step 2: Create a Computationally Intensive PL/SQL Function (without cache)

Let's create a heavy calculation:

CREATE OR REPLACE FUNCTION calc_bonus(p_emp_id NUMBER)
RETURN NUMBER IS
    v_salary NUMBER;
    v_bonus NUMBER;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE emp_id = p_emp_id;

    -- Simulate heavy calc
    DBMS_LOCK.SLEEP(0.03);

    v_bonus := v_salary * 0.10;
    RETURN v_bonus;
END;
/

Step 3: Performance Timing without the Cache

SET TIMING ON;

BEGIN
    FOR i IN 1..100 LOOP
        DBMS_OUTPUT.PUT_LINE(calc_bonus(i));
    END LOOP;
END;
/

Notice that the elapsed time is 3.264 seconds

Step 4: Create the PL/SQL Function to Enable Results Cache

Now, let's create a new function and enable the caching

Note: From Oracle Database 11.2, the RELIES_ON clause is deprecated. Oracle automatically invalidates the cache when underlying data changes.

CREATE OR REPLACE FUNCTION calc_bonus_cached(p_emp_id NUMBER)
RETURN NUMBER RESULT_CACHE IS
    v_salary NUMBER;
    v_bonus NUMBER;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE emp_id = p_emp_id;

    -- Simulate heavy computation
    DBMS_LOCK.SLEEP(0.03);

    v_bonus := v_salary * 0.10;
    RETURN v_bonus;
END;
/

Step 5: Observing Improved Performance

Run the cached function:

BEGIN
    FOR i IN 1..100 LOOP
        DBMS_OUTPUT.PUT_LINE(calc_bonus_cached(i));
    END LOOP;
END;
/

We see an output of 3.184 seconds. Now that the results are cached, we can run it again.

BEGIN
    FOR i IN 1..100 LOOP
        DBMS_OUTPUT.PUT_LINE(calc_bonus_cached(i));
    END LOOP;
END;
/

Notice the elapsed time. 0.012 seconds. This time the results came right from the cache.

Data Integrity and Cache Invalidation

If we update the table then Oracle automatically invalidates the cache for us. Let's see it happen.

-- Update data to test invalidation
UPDATE employees SET salary = salary * 2 WHERE emp_id = 5000;

COMMIT;

BEGIN
    FOR i IN 1..100 LOOP
        DBMS_OUTPUT.PUT_LINE(calc_bonus_cached(i));
    END LOOP;
END;
/

We see about 3 seconds again, meaning Oracle invalidated and refreshed the cache because the data inside the cache was no longer up to date.

Cache Administration and Monitoring

You can use the following admin tools to manage / monitor your cache:

View cache stats:

SELECT * FROM V$RESULT_CACHE_STATISTICS;

Invalidate specific cache entries:

EXEC DBMS_RESULT_CACHE.INVALIDATE('YOUR_SCHEMA', 'CALC_BONUS_CACHED');

Flush an entire cache:

EXEC DBMS_RESULT_CACHE.FLUSH;

View a detailed cache memory report:

EXEC DBMS_RESULT_CACHE.MEMORY_REPORT(detailed => TRUE);

For more info, check out the official PL/SQL Function Results Cache Documentation and the DBMS_RESULT_CACHE package docs.

Cleanup

You can clean up with the following:

DROP FUNCTION calc_bonus_cached;
DROP FUNCTION calc_bonus;
DROP TABLE employees;

Considerations When Using PL/SQL Results Cache:

Cached data uses memory in the shared pool.
Oracle automatically invalidates cache on DML operations on referenced tables.
It's ideal for frequently called functions with identical parameters on data that doesn't change often.

Until next week

-Killian