My Function Result Cache talk

onedoesnotsimplyresultcacheIf you’re interested in my presentation on the Function Result Cache, it’s now available from my presentations page. It was given this morning at Oracle’s offices in Perth to the local AUSOUG branch and seemed to go down well and I got some good feedback. It was only a little overshadowed by all the hoopla over the release of 12c 🙂


Comments

  1. Thanks for the presentation Jeff.

    With regard to Function Result Cache and RAC, the Oracle 11.2 documentation has this to say:

    Result Caches in Oracle RAC Environment
    Cached results are stored in the system global area (SGA). In an Oracle RAC environment, each database instance manages its own local function result cache. However, the contents of the local result cache are accessible to sessions attached to other Oracle RAC instances. If a required result is missing from the result cache of the local instance, the result might be retrieved from the local cache of another instance, instead of being locally computed.
    The access pattern and work load of an instance determine the set of results in its local cache; therefore, the local caches of different instances can have different sets of results.
    Although each database instance might have its own set of cached results, the mechanisms for handling invalid results are Oracle RAC environment-wide. If results were invalidated only in the local instance’s result cache, other instances might use invalid results. For example, consider a result cache of item prices that are computed from data in database tables. If any of these database tables is updated in a way that affects the price of an item, the cached price of that item must be invalidated in every database instance in the Oracle RAC environment.

    (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#g3340664)

    I thought this interesting and set up the following function and test script. I then ran the script twice on node 1 to confirm that the result cache was operating and then ran it on a second node (an 11.2.0.3 database on AIX):

    CREATE OR REPLACE FUNCTION my_func (p_int IN INTEGER)
    RETURN DATE
    RESULT_CACHE
    AS
    BEGIN
    dbms_output.put (‘.’);
    RETURN TO_DATE(’01/01/2013′,’dd/mm/yyyy’) + p_int;
    END my_func;
    /

    DECLARE
    l_date DATE;
    BEGIN
    FOR i IN 1..2000
    LOOP
    l_date := my_func (i);
    END LOOP;
    dbms_output.put_line (‘finished!’);
    END;
    /

    — node 1
    SQL> EXEC dbms_result_cache.flush

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.09
    SQL> DECLARE
    2 l_date DATE;
    3 BEGIN
    4 FOR i IN 1..2000
    5 LOOP
    6 l_date := my_func (i);
    7 END LOOP;
    8 dbms_output.put_line (‘finished!’);
    9 END;
    10 /
    …………………………………………………………………………………………………………………..
    — snip —
    ……………………………..finished!

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.18
    SQL> DECLARE
    2 l_date DATE;
    3 BEGIN
    4 FOR i IN 1..2000
    5 LOOP
    6 l_date := my_func (i);
    7 END LOOP;
    8 dbms_output.put_line (‘finished!’);
    9 END;
    10 /
    finished!

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.01

    All as expected so far, so now for the node 2 run:

    SQL> DECLARE
    2 l_date DATE;
    3 BEGIN
    4 FOR i IN 1..2000
    5 LOOP
    6 l_date := my_func (i);
    7 END LOOP;
    8 dbms_output.put_line (‘finished!’);
    9 END;
    10 /
    finished!

    PL/SQL procedure successfully completed.

    Elapsed: 00:01:47.56

    So, as the documentation says, it would seem that the result cache from node 1 was used for node 2. However, look at the time it took to execute: 1:48 minutes! Clearly something’s not right.

    Checking the details in Active Session History, there were a lot of events, mostly process communication related, with KOTTBX$ as the object being referenced. At this stage I don’t have any explanation for this behaviour. It’s definitely Result Cache related as I repeated the test using a non-result cache function and each node ran fine with no delays. Potentially it’s related to the configuration of the RAC system I used. A quick Google and Oracle Support check didn’t reveal anything so more digging will be required…

    • Interesting, thanks! I have no idea what the cause of the performance hit you saw would be.

      “If any of these database tables is updated in a way that affects the price of an item, the cached price of that item must be invalidated in every database instance in the Oracle RAC environment.” – I don’t know much about RAC but I would have thought each node would know when to invalidate their local cache as soon as the underlying table changes are propogated?

      • For my example I didn’t access any tables so that should rule out any issues associated with moving data blocks between nodes. I’ll probably escalate this to Oracle Support if I can’t uncover anything more. After your presentation I found two places in the code I’m currently working on where function result cache would be beneficial. However, with the RAC delay I simply can’t consider using this feature.

Leave a Reply

Your email address will not be published / Required fields are marked *