A new feature in the Oracle 11g database is the ‘Function Result Cache’. When you create a function with the new clause ‘RESULT_CACHE’ the result of the function is stored in the cache for each parameter value it is called with. When the function is called again with the same parameter the result is retrieved from the cache instead of determined again. And of course, this can be much faster.
A small example to demonstrate this.
Create a function that does some complex time consuming computations (simulated by waiting 10 seconds). Execution of this function will take about 10 seconds each time.
SQL> create or replace function test_result_cache( p_in in number ) return number
2 as
3 begin
4 sys.dbms_lock.sleep(10);
5 return( p_in );
6 end;
7 /
Function created.
Elapsed: 00:00:00.17
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10)
---------------------
10
Elapsed: 00:00:10.39
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10)
---------------------
10
Elapsed: 00:00:10.10
Now change the function, add the new RESULT_CACHE clause to the create statement of the function. Then the first execution of the function will (of cause) still take 10 seconds but each consecutive call, with the same parameter, will give a much faster response.
SQL> create or replace function test_result_cache( p_in in number ) return number result_cache
2 as
3 begin
4 sys.dbms_lock.sleep(10);
5 return( p_in );
6 end;
7 /
Function created.
Elapsed: 00:00:00.07
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10)
---------------------
10
Elapsed: 00:00:10.35
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10)
---------------------
10
Elapsed: 00:00:00.00
Optionally you can add a RELIES_ON clause to the create statement of the function to indicate on which tables the function depends. If something changes in one of these tables the function result is determined again and not retrieved from the cache. If you omit this RELIES_ON clause it is possible that the function returns old data. So you are responsible for that yourself!
Yes, it works exactly as you expect:
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10)
———————
10
Elapsed: 00:00:10.14
SQL> select test_result_cache(11) from dual;
TEST_RESULT_CACHE(11)
———————
11
Elapsed: 00:00:10.00
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10)
———————
10
Elapsed: 00:00:00.01
SQL> select test_result_cache(11) from dual;
TEST_RESULT_CACHE(11)
———————
11
Elapsed: 00:00:00.00
SQL>
Hi,
That looks like the deterministic option in prior versions, but with more functionality…
Cheers, Gareth
If you change the value p_in passed to the function test_result_cache( p_in in number ). Lets say 11.
Will it know not to use the cache in this case the first time 11 is passed to the function?
ex:
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10)
———————
10
Elapsed: 00:00:10.35
SQL> select test_result_cache(11) from dual;
TEST_RESULT_CACHE(11)
———————
11
Elapsed: 00:00:10.35
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10)
———————
10
Elapsed: 00:00:00.00
SQL> select test_result_cache(11) from dual;
TEST_RESULT_CACHE(11)
———————
11
Elapsed: 00:00:00.00
Many results can be cached. There is a database parameter RESULT_CACHE_MAX_SIZE that specifies the maximum amount of SGA memory that can be used for the result cache. Also there is a package DBMS_RESULT_CACHE to manage the cache, e.g. with DBMS_RESULT_CACHE.FLUSH you can flush the cache.
And yes, in the RELIES_ON() clause you can also mention a view if you do not have access to the table.
A really nice feature!
Do you know how many results are cached by Oracle? Is there a setting somewhere to define the size?
Another questions: With RELIES_ON, can I also reference a table where I don’t have direct read access, eg. in case if I just have access to the table through a view.
Thanks
Patrick