Quick Tip: Oracle11g function result cache

I just realized it's been a little while since I offered some PL/SQL thoughts on this blog. Sorry, the election campaign is twisting around my priorities. So how about if I share with you what I believe is far and away the most important new feature for PL/SQL developers in Oracle11g: the function result cache.

Suppose you have a table that is queried frequently (let's say thousands of times a minute) but is only updated once or twice an hour. In between those changes, that table is static. Now, most PL/SQL developers have developed a very bad habit: whenever they need to retrieve data they write the necessary SELECT statement directly in their high-level application code. As a result, their application must absorb the overhead of going through the SQL layer in the SGA, over and over again, to get that unchanging data.

If, on the other hand, you put that SELECT statement inside its own function and then define that function as a result cache, magical things happen. Namely, whenever anyone in that database instance calls the function, Oracle first checks to see if anyone has already called the function with the same input values. If so, then the cached return value is returned without running the function body. If the inputs are not found, then the function is executed, the inputs and return data is stored in the cache, and then the data is sent back to the user. The data is never queried more than once from the SQL layer - as long as it hasn't changed. As soon as anyone connected to that instance commits changes to a table on which the cache is dependent, Oracle invalidates the cache, so that the data will have to be re-queried (but just once). You are, as would be expected inside an Oracle database, guaranteed to always see clean, correct data.

Why would you do this? Because the performance improvements are dramatic. In the 11g_emplu.pkg script (available in the demo.zip at PL/SQL Obsession), I compare the performance of a normal database query via a function to a function result cache built around the same query and I see these results:

Execute query each time Elapsed: 5.65 seconds.
Oracle 11g result cache Elapsed: .30 seconds.

Isn't that just amazing and incredible and wonderful? Here's the original version of the function (over 5 seconds):

PACKAGE BODY emplu
IS
FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
IS
   onerow_rec   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO onerow_rec
     FROM employees
    WHERE employee_id = employee_id_in;

   RETURN onerow_rec;
END onerow;
END emplu;                 

and here's the result cache version:

PACKAGE BODY emplu
IS
   FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
      RETURN employees%ROWTYPE
      RESULT_CACHE RELIES_ON ( employees )
   IS
      onerow_rec   employees%ROWTYPE;
   BEGIN
      SELECT *
        INTO onerow_rec
        FROM employees
       WHERE employee_id = employee_id_in;

      RETURN onerow_rec;
   END onerow;
END emplu;

Can you see the difference? Not much of a change, right? I just added that single RESULT_CACHE line. And notice that I would not have to change any of the code that was already calling this function.

Here's the bottom line regarding the function result cache: get ready now to take advantage of this feature. Stop writing SELECT statements directly into your application code. Instead, hide your queries in functions so that you can easily convert to result caches when you upgrade to Oracle11g.