16 December 2011

%TYPE and RESULT_CACHE - Correction needed? (9604)

The 15 December quiz tested your knowledge of 11.2's ability to automatically detect when the contents  of a table on which a result cache function relies has been changed (and then invalidate the cache).

Two players notified us that even though we scored the following choice as incorrect (which meant, in the context of this quiz, that a change to the table would not invalidate the cache):
CREATE OR REPLACE FUNCTION plch_func (n IN NUMBER)
   RETURN NUMBER
   RESULT_CACHE
IS
   l_n   plch_tab1.n%TYPE := n;
BEGIN
   DBMS_OUTPUT.put_line ('Running plch_func');
   RETURN l_n;
END;
The output from the verification does not support this conclusion.

So, first: the reason that this version of the function should not result in an invalidation of the cache highlights the distinct between dependencies between database objects and "relies on" for a result cache.

The plch_func function depends on the plch_tab1 in the "traditional" sense: since there is a reference to the table in the function through the %TYPE declaration, when this table changes, the function is marked as invalid and will have to be recompiled (note that the Oracle11g fine-grained dependency feature reduces the scenarios under which this invalidation will occur).

But that is a compile-time dependency, and very different from the "relies on" relationship that Oracle tracks automatically for purposes of ensuring clean data in a result cache. In other words, if the contents (but not the structure) of he plch_tab1 table changes, the compilation status of plch_func is not affected.

This is not the case with the result cache feature, precisely because this cache is all about delivering data (the contents of the table) to users.

So...that was the idea: if the only "mention" of plch_tab1 in plch_func is %TYPE, then that function does not rely on plch_tab1, and changes to that table will not force an invalidation and flush of its cache.

Having said that, when I ran the verification code, I noticed that the output did not match my expectations. The first time I ran it, I saw this:
%TYPE Reference
1
1
when I should have seen this:
%TYPE Reference
Running plch_func
1
1
That seemed very strange. It was as though the compilation of the new version of the function did not flush the cache. I then put an update statement before the CREATE OR REPLACE to force the cache to be invalidated. Then I saw the expected results. Then I didn't believe that could possibly be necessary, so I removed the update statement and now do see the expected results consistently:
%TYPE Reference
Running plch_func
1
1
The two players who wrote in said they saw these results:
%TYPE Reference
Running plch_func
1
Running plch_func
1
Very odd! I cannot reproduce that, for sure. I'd love to hear about the experiences of any players who have an 11.2 instance with which to work (11.1 still requires that you provide an explicit RELIES ON clause to specify the tables). I have also added that update statement to the verification code.

Let me know what you discover!

Thanks, Steven

7 comments:

  1. Manfred Kleander16 December, 2011 17:03

    Hello,

    here is my output for the verification code:

    C:\oracle\product\11.2.0\dbhome_2\BIN>sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production on Fr Dez 16 17:51:42 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Benutzernamen eingeben: ops$mkleand/mkleand@entw11g

    Verbunden mit:
    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

    SQL>
    SQL> CREATE TABLE plch_tab1 (n NUMBER)
    2 /

    Tabelle wurde erstellt.

    SQL>
    SQL> CREATE TABLE plch_tab2 (n NUMBER)
    2 /

    Tabelle wurde erstellt.

    SQL>
    SQL> CREATE OR REPLACE VIEW plch_view
    2 AS
    3 SELECT n FROM plch_tab1
    4 UNION
    5 SELECT n FROM plch_tab2
    6 /

    View wurde erstellt.

    SQL>
    SQL> BEGIN
    2 INSERT INTO plch_tab1
    3 VALUES (1);
    4
    5 INSERT INTO plch_tab2
    6 VALUES (2);
    7
    8 COMMIT;
    9 END;
    10 /

    PL/SQL-Prozedur erfolgreich abgeschlossen.

    SQL>
    SQL> CREATE OR REPLACE PROCEDURE plch_query
    2 IS
    3 BEGIN
    4 FOR rec IN (SELECT * FROM plch_tab1)
    5 LOOP
    6 NULL;
    7 END LOOP;
    8 END;
    9 /

    Prozedur wurde erstellt.

    SQL>
    SQL> CREATE OR REPLACE FUNCTION plch_func (n IN NUMBER)
    2 RETURN NUMBER
    3 RESULT_CACHE
    4 IS
    5 l_n plch_tab1.n%TYPE := n;
    6 BEGIN
    7 DBMS_OUTPUT.put_line ('Running plch_func');
    8 RETURN l_n;
    9 END;
    10 /

    Funktion wurde erstellt.

    SQL>
    SQL> CREATE OR REPLACE PROCEDURE plch_test (n IN NUMBER)
    2 IS
    3 BEGIN
    4 DBMS_OUTPUT.put_line (plch_func (1));
    5
    6 EXECUTE IMMEDIATE
    7 'UPDATE plch_tab' || n || ' SET n = n + 0';
    8
    9 DBMS_OUTPUT.put_line (plch_func (1));
    10 ROLLBACK;
    11 END;
    12 /

    Prozedur wurde erstellt.

    SQL>
    SQL> BEGIN
    2 DBMS_OUTPUT.put_line ('%TYPE Reference');
    3 plch_test (1);
    4 END;
    5 /
    %TYPE Reference
    Running plch_func
    1
    Running plch_func
    1

    PL/SQL-Prozedur erfolgreich abgeschlossen.

    SQL>

    So you can see, I get the desired output.

    Best regards,
    Manfred

    ReplyDelete
  2. Hi Manfred!

    Can you show result of the following query?

    select value from v$parameter where name = lower('RESULT_CACHE_MAX_SIZE');

    ReplyDelete
  3. And RESULT_CACHE also can be bypassed, you can see it using:

    select dbms_result_cache.status from dual;

    ReplyDelete
  4. Manfred Kleander17 December, 2011 10:35

    Hi,

    yes this seems to be the probem. Here are the results of the queries:

    C:\oracle\product\11.2.0\dbhome_2\BIN>sqlplus

    SQL*Plus: Release 11.2.0.1.0 Production on Sa Dez 17 11:10:40 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Benutzernamen eingeben: ops$mkleand
    Kennwort eingeben:

    Verbunden mit:
    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

    SQL> select value from v$parameter where name = lower('RESULT_CACHE_MAX_SIZE');


    VALUE
    -------------------------------------------------------------------------------

    0

    SQL> select dbms_result_cache.status from dual;

    STATUS
    -------------------------------------------------------------------------------

    DISABLED

    SQL>

    It seems, that our DBA disabled the result_cache.

    Thank you,
    Manfred

    ReplyDelete
  5. Hello All,

    The PL/SQL documentation about the result cache says the following:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#BABFHACJ

    "When you hot-patch a PL/SQL unit on which a result-cached function depends (directly or indirectly), the cached results associated with the result-cached function might not be automatically flushed in all cases."

    From what you experienced, we may conclude that, at least in some cases, this statement
    could include the result-cached function itself, not only other PL/SQL units on which the
    function depends.

    In my testing I DID NOT experienced this behavior, that is, even when I executed the code
    of all the choices one after the other, the output was as expected, in other words, recompiling the function DID invalidate the cached result.

    Anyway, one of the Quiz general assumptions states that the different choices are INDEPENDENT from each other, so the scoring is correct.

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  6. Manfred Kleander19 December, 2011 14:02

    Hi,

    I guess I know, why the PL/SQL Function Result Cache is disabled in our database. The PL/SQL Function Result Cache is a feature of the Oracle Enterprise Edition and we have only Oracle Standard Edition.

    Best regards,
    Manfred

    ReplyDelete
  7. Yes, indeed! I have added this fact into the explanation for the FRC feature, so that in future quizzes, players will read about this immediately.

    SF

    ReplyDelete