Function Caching

Function Caching is a new Oracle 11g feature. It looks pretty neat. However, I am not going to explain or test any of that here. I am going to show you how you can cache your function results without 11g. I have actually done it in Oracle 9i and 10g. First lets create a test table and function.

create table foobar
as
select * from dba_objects;

CREATE OR REPLACE FUNCTION FN_FOOBAR (p_input in varchar2)
return number
as
v_cnt number;
begin
select count(*) into v_cnt
from foobar
where owner = p_input;
return v_cnt;
end;

Now, I will run a test run to get my consistent gets or logical reads for a very simple query.

First run set autotrace traceonly.

select owner
from foobar;

Statistics--------------------------------------------------------
0 recursive calls
0 db block gets
4117 consistent gets
0 physical reads
0 redo size
5436485 bytes sent via SQL*Net to client
38364 bytes received via SQL*Net from client
3454 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51783 rows processed

My consistent gets are 4117. That gives us a benchmark on how much is being done. Now we add the function.

select owner,fn_foobar(owner) cnt
from foobar;

Statistics--------------------------------------------------------
51785 recursive calls
0 db block gets
37287877 consistent gets
0 physical reads
0 redo size
887746 bytes sent via SQL*Net to client
38364 bytes received via SQL*Net from client
3454 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51783 rows processed

Now my consistent gets are 37,287,877. It is doing a lot more work. However, a lot of the work is for no reason. Foobar is the objects table in the database. Select against it and you will get alot rows for SYS with the same number. So many of the calls yield the same result.

Something like this.
Owner Cnt
SYS 23039
SYS 23039
SYS 23039


So lets make that call just a little different.

select owner,(select fn_foobar(owner) from dual) cnt
from foobar;

Statistics----------------------------------------------------------
59 recursive calls
0 db block gets
46597 consistent gets
0 physical reads
0 redo size
887746 bytes sent via SQL*Net to client
38364 bytes received via SQL*Net from client
3454 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51783 rows processed

Now the consistence gets are down to 46,597. What is happening is that the call to the function is actually being cached. So when Oracle goes I need to call fn_foobar for SYS, it first looks in the cache. If its there then it never runs the function. This is essentially what is happening in 11g automatically.