Function Result Cache in ORACLE 11G

User defined function which are defined with RESULT_CACHE statement will be helpful to catch the result sets of function in memory. That way we can avoid re-executing the function for same inputs and which in turn increases the overall performance.

Let us go through the following scenario:

Create a test table
create table func_result_cache_test_tab as select  owner, object_name,object_id from dba_objects ;

Gather statistics on the table
exec dbms_stats.gather_table_Stats (user, 'FUNC_RESULT_CACHE_TEST_TAB');

create another table which holds object_id's

create table object_ids_tab as select object_id from dba_objects where rownum < 1000;

Gather statistics on the table
exec dbms_stats.gather_table_Stats (user, 'OBJECT_IDS_TAB');

Create a function with result_cache clause

create or replace function result_cache_test_fun (p_object_id int)

return varchar2

result_cache

as

    v_object_name func_result_cache_test_tab.object_name%type;

begin

    select object_name into v_object_name from  func_result_cache_test_tab where object_id = p_object_id;

    return v_object_name;

exception

    when others then

        dbms_output.put_line ('Error 100-' || dbms_utility.format_error_stack || dbms_utility.format_error_backtrace );

end;

Now, create a stored procedure to test the above function

create or replace procedure test_proc as

    v_start_time number;

    v_object_name func_result_cache_test_tab.object_name%type;

begin

    v_start_time := dbms_utility.get_time;

    for i in (select object_id from object_ids_tab ) loop

        v_object_name := result_cache_test_fun (i.object_id);

    end loop;

    dbms_output.put_line (' Time taken by function when first time executed ->' || (dbms_utility.get_time - v_start_time) );

     

    v_start_time := dbms_utility.get_time;

    for j in (select object_id from object_ids_tab ) loop

        v_object_name := result_cache_test_fun (j.object_id);

    end loop;

    dbms_output.put_line (' Time taken by function when second time executed ->' || (dbms_utility.get_time - v_start_time) );

exception

    when others then

        dbms_output.put_line ('Error 101-' || dbms_utility.format_error_stack || dbms_utility.format_error_backtrace );

end;

let us now test the execution of function with the help of procedure TEST_PROC.

SQL> exec test_proc;

Time taken by function when first time executed ->543

Time taken by function when second time executed ->0

PL/SQL procedure successfully completed.

If we observe above, the function had taken no time in second time to return the result set it is because the results are fetched from cached memory.

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

·         V$RESULT_CACHE_STATISTICS

·         V$RESULT_CACHE_MEMORY

·         V$RESULT_CACHE_OBJECTS

·         V$RESULT_CACHE_DEPENDENCY

RELIES_ON Clause:

relies_on required to invalidate the result cache if table data is modified.


Bypass result_cache while executing a plsql block of code:

1. Place the result cache in bypass mode:

SQL> exec dbms_result_cache.bypass(true)
PL/SQL procedure successfully completed.

2. Flush the existing cached results:

SQL> exec dbms_result_cache.flush
PL/SQL procedure successfully completed.

3. Apply the PL/SQL code patch.
4. Turn off the PL/SQL result cache bypass mode:

SQL> exec dbms_result_cache.bypass(false)
PL/SQL procedure successfully completed.

When you turn result cache bypassing on, the database doesn’t use any cached results,and no results are stored there until you turn bypass mode off.

Restrictions on Result-Cached Functions

To be result-cached, a function must meet all of the following criteria:

·        It is not defined in a module that has invoker's rights or in an anonymous block.

·        It is not a pipelined table function.

·        It has no OUT or IN OUT parameters.

·        No IN parameter has one of the following types:

o   BLOB

o   CLOB

o   NCLOB

o   REF CURSOR

o   Collection

o   Object

o   Record

·        The return type is none of the following:

o   BLOB

o   CLOB

o   NCLOB

o   REF CURSOR

• o   Object
• Record or PL/SQL collection that contains one of the preceding unsupported return types

For more information refer Here :

  Link1   Link2