PL/SQL Function Result Cache

Database version: Oracle 11g

If you're looking for an easy way to boost the performance of a PL/SQL function, consider using Oracle 11g's built-in caching mechanism. All you have to do is add the RESULT_CACHE clause to your function declaration.

Here's the syntax:

RESULT_CACHE [ RELIES_ON (table [, table2 ... tableN] ]


The RELIES_ON clause is optional with 11g release 2. If not specified, Oracle detects on its own the base tables (if any) referenced by your function. (For functions within packages, the RELIES_ON clause can only be used in the package body, not the spec.)

Example usage:

CREATE OR REPLACE FUNCTION f_dept_by_id_rcache(p_dept_id IN NUMBER)
RETURN depts%ROWTYPE result_cache AS
...


Here's how it works.

• The first time the function is called with a particular set of parameters, the return value is cached (no value is cached if an unhandled exception occurs)
• The next time the function is called with that same set of parameters, the cached value is returned and the body of the function is not executed
• If the base table(s) (specified with RELIES_ON or identified by Oracle) change, the cache is invalidated
• Function results are cached in the SGA, which makes them available to all sessions
• Oracle removes the older cached results if the system needs more memory

According to Oracle documentation, you should cache functions that are used often but whose base tables change rarely. For more restrictions on what types of functions can be cached, see the Oracle documentation.

If you're wondering what the difference is between a function that uses RESULT_CACHE and a function that is declared to be deterministic, the answer is that a deterministic function can only be called within a SQL statement.

How Fast is It?

/* Prepare test table */
create table depts(dep_id number, dep_name varchar2(100))
/
insert into depts values (1, 'Accounting');
insert into depts values (2, 'Damage Control');
insert into depts values (3, 'Information Technology');
insert into depts values (4, 'Marketing');
commit;

/* Create typical function to fetch dept record by id */
CREATE OR REPLACE FUNCTION f_dept_by_id(p_dept_id IN NUMBER)
  RETURN depts%ROWTYPE AS
  l_dept_rec depts%ROWTYPE;
BEGIN
  SELECT *
  INTO   l_dept_rec
  FROM   depts
  WHERE  dep_id = p_dept_id;
  RETURN l_dept_rec;
END f_dept_by_id;
/

/* Second function same as first, except with RESULT CACHE */
CREATE OR REPLACE FUNCTION f_dept_by_id_rcache(p_dept_id IN NUMBER) 
  RETURN depts%ROWTYPE result_cache AS
  l_dept_rec depts%ROWTYPE;
BEGIN
  SELECT *
  INTO   l_dept_rec
  FROM   depts
  WHERE  dep_id = p_dept_id;
  RETURN l_dept_rec;
END f_dept_by_id_rcache;
/

/* Anonymous block that compares the two functions */
DECLARE
  TYPE dept_id_tab IS TABLE OF depts.dep_id%TYPE INDEX BY PLS_INTEGER;
  l_depts_to_fetch dept_id_tab;
  l_start_time PLS_INTEGER;
    
  PROCEDURE p_test_fetches(p_with_result_caching IN BOOLEAN, 
                           p_depts                IN dept_id_tab) IS
   l_dept_rec depts%ROWTYPE;
   c_iterations CONSTANT PLS_INTEGER := 1000;   
  BEGIN
    FOR itr IN 1 .. c_iterations LOOP
      FOR i IN 1 .. p_depts.count LOOP
        IF p_with_result_caching THEN
   l_dept_rec :=  f_dept_by_id_rcache(p_dept_id => p_depts(i));
        ELSE
   l_dept_rec  :=  f_dept_by_id(p_dept_id => p_depts(i));
        END IF;
       END LOOP;
    END LOOP;
  END p_test_fetches;

BEGIN
  SELECT dep_id BULK COLLECT
  INTO   l_depts_to_fetch
  FROM   depts;

  -- test without result caching
  l_start_time := dbms_utility.get_cpu_time;
  p_test_fetches(p_with_result_caching => FALSE,
                 p_depts      => l_depts_to_fetch);
  dbms_output.put_line('Without result_cache: ' ||
                       to_char(dbms_utility.get_cpu_time - l_start_time));

  -- test with result caching
  l_start_time := dbms_utility.get_cpu_time;
  p_test_fetches(p_with_result_caching => TRUE,
                 p_depts      => l_depts_to_fetch);
  dbms_output.put_line('WITH result_cache: ' ||
                       to_char(dbms_utility.get_cpu_time - l_start_time));
END;
/