11g new feature - PL/SQL Function Result Cache

Starting with Oracle 11g, there is very useful feature related to caching results which returned by PL/SQL Function. 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.



SQL> create table myt1 as select * from dba_objects where rownum<11;

Table created.

SQL> insert into myt1 select * from myt1;

10 rows created.

SQL> /

20 rows created.

SQL> commit;

Commit complete.

SQL> select object_id, count(*) from myt1 group by object_id;

OBJECT_ID   COUNT(*)
---------- ----------
25          4
54          4
28          4
29          4
20          4
46          4
41          4
40          4
3          4
15          4

10 rows selected.

As you see, for every object_id there 4 records. Now, writing function which uses RESULT_CACHE feature:

SQL> create sequence MYSEQ1 MAXVALUE 1000000 MINVALUE 1 INCREMENT BY 1 START WITH 1;

Sequence created.

SQL> create or replace function myfunc1 (objid number)
return varchar
result_cache
relies_on(myt1)
is
obj_name varchar(1000);
call_counter number;
begin
select myseq1.nextval into call_counter from dual; ---- This for controlling how many times our function called in reality
select distinct object_name into obj_name from myt1 where object_id=objid;
return obj_name;
end;

Function created.

result_cache ----->This tells Oracle to cache the result which will be returned by this function
relies_on(myt1) ------> It tells the function that the cache depends on table myt1. If the data in myt1 change, the cache needs to be refreshed. The refresh happens automatically without your intervention. If the data does not change, the cache continues to provide the cached values as quickly as possible.

Now, lets try to execute our function. Our PL/SQL block will loop 40 times (select object_id from myt1 returns 40 records) and later we will check how many times our function called. We will check it using our sequence's (we created it above) current value:

SQL>
1  begin
2  for i in (select object_id from myt1) loop
3  dbms_output.put_line(myfunc1(i.object_id));
4  end loop;
5* end;
SQL> /
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5

PL/SQL procedure successfully completed.

SQL> select myseq1.currval from dual;

CURRVAL
----------
10



According our code we would execute myfunc1 40 times. But, SEQUENCE's current values says it is 10.

Reason: Before calling function, Oracle check result cache if the same function called with the same parameters before. If found, function will not be called.

If we do the same LOOP again SEQUENCE current value will not change, because, everything (the same function with the same parameters) are in the cache. In other words Oracle will never call function for the same parameters:

SQL>
1  begin
2  for i in (select object_id from myt1) loop
3  dbms_output.put_line(myfunc1(i.object_id));
4  end loop;
5* end;
SQL> /
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5

PL/SQL procedure successfully completed.

SQL> select myseq1.currval from dual;

CURRVAL
----------
10

Now, lets check how RELIES_ON clause works. Here, I will update (without commit) our table (MYT1) and that operation should invalidate result cache. Consequently, when we call the same loop Oracle again will execute function because of invalidation in cache:

SQL> update myt1 set object_id=object_id*10;

40 rows updated.

SQL>
1  begin
2  for i in (select object_id from myt1) loop
3  dbms_output.put_line(myfunc1(i.object_id));
4  end loop;
5* end;
SQL> /
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
I_IND1
I_CDEF2
I_OBJ5

PL/SQL procedure successfully completed.

SQL> select myseq1.currval from dual;

CURRVAL
----------
20

As you see, SEQUENCE current value increased.