The PL/SQL function result cache provides the mechanism for caching the results of PL/SQL functions in a shared global area (SGA), available to every session that runs on your database.

When a result-cached function is invoked, the system checks the cache. If the cache contains the result from a previous call to the function with the same parameter values, the system returns the cached result to the invoker and does not re-execute the function body.

If the cache does not contain the result, the system executes the function body and adds the result (for these parameter values) to the cache before returning control to the invoker.

You can enable result-caching for a function simply using the RESULT_CACHE clause.

To make a function result-cached include the option RESULT_CACHE both in function declaration and function definition.

Example code

Create the following PL/SQL package:

SQL> CREATE OR REPLACE PACKAGE object_count_pkg IS
  2    FUNCTION get_object_count(owner_in VARCHAR2) RETURN NUMBER RESULT_CACHE;
  3  END object_count_pkg;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY object_count_pkg AS
  2    FUNCTION get_object_count (owner_in VARCHAR2)
  3       RETURN NUMBER
  4       RESULT_CACHE RELIES_ON (DBA_OBJECTS)
  5    IS
  6       object_count NUMBER;
  7    BEGIN
  8  
  9       SELECT COUNT(*)
 10         INTO object_count
 11         FROM dba_objects
 12        WHERE owner = owner_in;
 13  
 14       RETURN object_count;
 15  
 16    EXCEPTION
 17    WHEN OTHERS THEN
 18       dbms_output.put_line('An error was encountered - '||SQLCODE);
 19    END get_object_count;
 20  END object_count_pkg;
 21  /

Package body created.

Create and run the following procedure once:

SQL> SET SERVEROUT ON
SQL> SET TIMING ON
SQL> DECLARE
  2    OBJECT_COUNT number;
  3  BEGIN
  4    OBJECT_COUNT := object_count_pkg.get_object_count('SYSTEM');
  5    dbms_output.put_line('COUNT = '||OBJECT_COUNT);
  6  END;
  7  /
COUNT = 618

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.86

And now execute the same code a couple of times more:

SQL> /
COUNT = 618

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> /
COUNT = 618

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

The elapsed time for executing the function has dropped from 00:00:00.86 to 00:00:00.01

Last step, confirm that the result cache of the function is cached by querying the V$RESULT_CACHE_OBJECTS view:

SQL> SELECT name FROM V$RESULT_CACHE_OBJECTS;

NAME
-----------------------------------------------------------------------------
PUBLIC.DBA_OBJECTS
TEST.OBJECT_COUNT_PKG
"TEST"."OBJECT_COUNT_PKG"::11."GET_OBJECT_COUNT"#8440831613f0f5d3 #3

The V$RESULT_CACHE_OBJECTS dynamic view displays all the cached objects in the database.

In the output example above, you can verify that “GET_OBJECT_COUNT” function is found as a cached object.

Link to script that contains the examples in this post.

 

 

 

1 Comment