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 thoughts on “New in Oracle 11g: PL/SQL Function Result Cache

  1. 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>

  2. 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

  3. 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.

  4. 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

Comments are closed.

Next Post

Getting started with JPA mapping : How IntelliJ can map your data with a few clicks

Facebook0TwitterLinkedinWith JPA, there finally is an ORM standard and every major IDE has support for it. Today I will show you how to map your data with IntelliJ IDEA. I also use the brand new TopLink 11g preview (a JPA implementation), but you can try this at home with every […]