Cross-Session PL/SQL Function Result Cache in 11g

 The cross-session PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL. This article describes the usage and administration of the function result cache.

• Usage
• Administration
• DBMS_RESULT_CACHE 

Related articles:

• Query Result Cache 

Usage

Enabling a function to use the function result cache is as simple as adding the RESULT_CACHE clause, and optionally the RELIES_ON clause. The following examples show their usage.

First, create and populate a test table.

CREATE TABLE res_cache_test_tab (
  id    NUMBER,
  value NUMBER
);

BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO res_cache_test_tab VALUES (i, i*10);
  END LOOP;
  COMMIT;
END;
/

The following function returns the VALUE from the test table for the specified ID. It also includes a call to the DBMS_LOCK.SLEEP procedure to slow down the function. Notice the inclusion of the RESULT_CACHE clause.

CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER)
  RETURN NUMBER
  RESULT_CACHE
AS
  l_value res_cache_test_tab.value%TYPE;
BEGIN
  SELECT value
  INTO   l_value
  FROM   res_cache_test_tab
  WHERE  id = p_in;
  
  -- Pause for 1 second.
  DBMS_LOCK.sleep(1);
  
  RETURN l_value;
END get_value;
/

The following procedure tests the performance of the get_value function by making calls to it in two loops and measuring the elapsed time for each run.

CREATE OR REPLACE PROCEDURE run_test AS
  l_start NUMBER;
  l_loops NUMBER := 10;
  l_value res_cache_test_tab.value%TYPE;
BEGIN
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_value := get_value(i);
  END LOOP;
  
  DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
  
  l_start := DBMS_UTILITY.get_time;
  
  FOR i IN 1 .. l_loops LOOP
    l_value := get_value(i);
  END LOOP;
  
  DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END run_test;
/

Running the procedure gives the following results.

SET SERVEROUTPUT ON
EXEC run_test;
First Loop: 1003 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

SQL>

The first loop takes approximately 10 seconds, 1 second per function call, while the second is almost instantaneous. If we run the test code again in a new session we can see that elapsed time remains the same.

CONN test/test
SET SERVEROUTPUT ON
EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

SQL>

What's more, if we alter the contents of the table, we still get the fast elapsed time, indicating the existing values are still being used.

UPDATE res_cache_test_tab
SET    value = value * 10;
COMMIT;

EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

SQL>

This represents a potential data integrity problem. The optional RELIES_ON clause is used to specify dependent tables and views so the result cache can be invalidated if the dependent objects are modified.

CREATE OR REPLACE  FUNCTION get_value (p_in IN NUMBER)
  RETURN NUMBER
  RESULT_CACHE RELIES_ON (res_cache_test_tab)
AS
  l_value res_cache_test_tab.value%TYPE;
BEGIN
  SELECT value
  INTO   l_value
  FROM   res_cache_test_tab
  WHERE  id = p_in;
  
  -- Pause for 1 second.
  DBMS_LOCK.sleep(1);
  
  RETURN l_value;
END get_value;
/

After recreating the function, we see a normal first-time run, followed by a run using the cached results.

EXEC run_test;
First Loop: 1001 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

SQL>

Next, we update the base table and perform another run.

UPDATE res_cache_test_tab
SET    value = value * 10;
COMMIT;

EXEC run_test;
First Loop: 1002 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

SQL>

The elapsed times show the function results cache was invalidated by the table update.

There are a number of restrictions that must be considered when using the PL/SQL function cache.

Administration

In a RAC environment, each instance has its own cached results, but invalidations of the cached results are maintained across all instances.

The more data you wish to cache, the more memory you need to allocate in the SGA. The PL/SQL function result cache and SQL result cache are managed together using the following parameters:

Information about the result cache is displayed using the following views:

DBMS_RESULT_CACHE

The DBMS_RESULT_CACHE package provides a PL/SQL API for result cache management.

The STATUS function displays the current status of the result cache.

SQL> SELECT DBMS_RESULT_CACHE.status FROM dual;

STATUS
-----------------------------------------------------
ENABLED

1 row selected.

SQL>

The MEMORY_REPORT procedure displays information about memory usage of the result cache.

SQL> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 1056K bytes (1056 blocks)
Maximum Result Size = 52K bytes (52 blocks)
[Memory]
Total Memory = 97160 bytes [0.048% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
....... Cache Mgr  = 108 bytes
....... Memory Mgr = 124 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 92028 bytes [0.046% of the Shared Pool]
....... Overhead = 59260 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 1916 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 32 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 30 blocks
................... PLSQL   = 10 blocks (10 count)
................... Invalid = 20 blocks (20 count)

PL/SQL procedure successfully completed.

SQL>

The INVALIDATE procedure invalidates all result-set objects for a specific object, specified using an OWNER and NAME (OBJECT_NAME) or OBJECT_ID from the %_OBJECTS views.

SQL> EXEC DBMS_RESULT_CACHE.invalidate('TEST', 'GET_VALUE');

PL/SQL procedure successfully completed.

SQL>

The INVALIDATE_OBJECT procedure invalidates a specific result-set object in the result cache, specified using an ID or CACHE_ID from the V$RESULT_CACHE_OBJECTS view.

SQL> exec DBMS_RESULT_CACHE.invalidate_object(31);

PL/SQL procedure successfully completed.

SQL>

The BYPASS procedure determines if the result cache is bypassed or not.

SQL> exec DBMS_RESULT_CACHE.bypass(true);

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESULT_CACHE.bypass(false);

PL/SQL procedure successfully completed.

SQL>

The FLUSH procedure and function remove all objects from the result cache and optionally release all memory and clear existing cache statistics.

SQL> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

SQL>

The last two procedures are especially useful during Hot-Patching of PL/SQL programs. Assume you have a function (MY_PACKAGE_1.MY_FUNCTION_1) using the result cache that relies code in another package (MY_PACKAGE_2). When the body of MY_PACKAGE_2 is recompiled the function relying on it is not invalidated, so the result cache is also not invalidated. If the code change results in different return values the contents of the result cache will now be incorrect. To keep the contents of the result cache consistent, use the following procedure:

1. Run the following code on each instance.
   

   BEGIN
     DBMS_RESULT_CACHE.bypass(TRUE);
     DBMS_RESULT_CACHE.flush;
   END;
   

2. Compile any new PL/SQL.
3. Run the following code on each instance.
   

   BEGIN
     DBMS_RESULT_CACHE.bypass(FALSE);
   END;
   

For more information see: