New in Oracle 11g: PL/SQL Function Result Cache 20188367001

New in Oracle 11g: PL/SQL Function Result Cache

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!

5 Comments

  1. Jurgen Kemmelings August 1, 2007
  2. Gareth Roberts August 1, 2007
  3. Steeve Bisson July 31, 2007
  4. Jurgen Kemmelings July 31, 2007
  5. Patrick Wolf July 31, 2007