The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

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):

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.

"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 thefunction depends.

In my testing I DID NOT experienced this behavior, that is, even when I executed the codeof 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.

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.