8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Cross-Session PL/SQL Function Result Cache in Oracle Database 11g Release 1
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 a SQL query, or within a loop in PL/SQL. This article describes the usage and administration of the function result cache.
Related articles.
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.
Note: The RELIES ON
clause is unnecessary in 11.2 as it automatically tracks dependencies and invalidates the cached results when necessary.
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:
Run the following code on each instance.
begin dbms_result_cache.bypass(true); dbms_result_cache.flush; end;
- Compile any new PL/SQL.
Run the following code on each instance.
begin dbms_result_cache.bypass(false); end;
For more information see:
- PL/SQL Function Result Cache
- Query Result Cache
- Efficient Function Calls From SQL
- Using the Cross-Session PL/SQL Function Result Cache
- DBMS_RESULT_CACHE
Hope this helps. Regards Tim...