PL/SQL Function Result Cache

On the PL/SQL Function Result Cache

By Steven Feuerstein  

Best practices—and preparation—for PL/SQL in Oracle Database 11g

I have been reading that Oracle is launching the 11th release of its database. Very exciting! But here's the problem: I don't think I'll be able to use it for another two years. So why should I even care about the new PL/SQL features of this future (for me) release?

Ah, yes, the real world. Oracle comes out with a new database release, and by the time it does so, its developers are already focused on the next new release. People like me start writing about, demonstrating, and even training on the newer release. And then there's almost everybody else: still on older releases, hoping and praying that someday maybe their management will see fit to catch up.

I feel your pain.

Having said that, I do think it makes an awful lot of sense to learn now about what Oracle Database 11g will have to offer you and your company in the future. The reason is very simple: once you see what is going to be available in Oracle Database 11g, you will probably change the way you write your code now !

I would say that the single most important new PL/SQL feature in Oracle Database 11g is thePL/SQL function result cache . Quite a mouthful, but then it is quite a feature.

I offer in this answer a quick overview of this feature, and I conclude by discussing how knowing about this feature should affect the way you write PL/SQL programs for earlier Oracle Database releases.

Suppose I am on a team that is building a human resources application. The employees table is one of the key structures, holding all the data for all the employees. Hundreds of users execute numerous programs in the application that read from this table—and read from it very often. Yet the table changes relatively infrequently, perhaps once or twice an hour. As a result, the application code repeatedly retrieves from the block buffer cache what is mostly static data, enduring the overhead of checking to see if the particular query has already been parsed, finding the data in the buffer, and returning it.

The team needs to improve the performance of querying data from the employees table. Currently, we use the following function to return a row from the employees table: 

FUNCTION one_employee (employee_id_in 
IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
IS
    l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
      INTO l_employee
      FROM employees
    WHERE employee_id = employee_id_in;

    RETURN l_employee;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
       /* Return an empty record. */
       RETURN l_employee;
END one_employee;

In Oracle Database 11g, however, we can add a line to the header of this function as follows:  

FUNCTION one_employee (employee_id_in 
IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
   RESULT_CACHE RELIES_ON (employees)
IS
    l_employee   employees%ROWTYPE;
BEGIN
.
.
.
 

This RESULT_CACHE clause tells Oracle Database that it should remember (store in a special in-memory result cache) each record retrieved for a specific employee ID number. And when a session executes this function and passes in an employee ID that was previously stored, the PL/SQL runtime engine will not execute the function body, which includes that query.

Instead, it will simply retrieve the record from the cache and return that data immediately. The result is much faster retrieval.

In addition, by specifying RELIES_ON (employees), we inform Oracle Database that if any session commits changes to that table, any data in the result cache drawn from the table must be invalidated. The next call to the one_employee function would then have to execute the query and retrieve the data fresh from the table.

Because the cache is a part of the System Global Area (SGA), its contents are available to all sessions connected to the instance. Furthermore, Oracle Database will apply its "least recently used algorithm" to the cache, to ensure that the most recently accessed data will be preserved in the cache.

Prior to Oracle Database 11g, a similar kind of caching was possible with package-level collections, but this cache is session-specific and located in the Process Global Area (PGA). This means that if I have 1,000 different sessions running the application, I could use up an enormous amount of memory in addition to that consumed by the SGA.

The PL/SQL function result cache minimizes the amount of memory needed to cache and share this data across all sessions. This low memory profile, plus the automatic purge of cached results whenever changes are committed, makes this feature of Oracle Database 11g very practical for optimizing performance in PL/SQL applications.

Analyze Performance and PGA Memory Impact

To test the improvement in performance and the impact on PGA memory over repeated queries of the data, I put together a set of scripts, available at oracle.com/technetwork/oramag/oracle/07-sep/o57plsql.zip, that compares three different ways to retrieve a row of employee data:

1. Execute the query repeatedly
2. Cache all the rows of the employees table in a packaged collection and then retrieve the data from that cache
3. Use the PL/SQL function result cache to avoid repetitive querying

To try this out yourself, unzip the o57plsql.zip file and run the 11g_emplu.tst script. It should take about five or six seconds to complete, and then you should see results like this: 

PGA before tests are run:
session PGA:  910860 bytes

Execute query each time 
Elapsed: 4.5 seconds. 
session PGA:  910860 bytes

Cache table in PGA memory 
Elapsed: .11 seconds. 
session PGA: 1041932 bytes

Oracle Database 11
                               g result cache 
Elapsed: .27 seconds. 
session PGA: 1041932 bytes
                            

Here are my conclusions from this admittedly incomplete analysis: 

• The Oracle Database 11g PL/SQL function result cache is, indeed, much faster than repetitive querying. In this test, it was over an order of magnitude faster.  
• A packaged collection cache is even faster, most likely because the PL/SQL runtime engine can access the data from PGA memory rather than SGA memory. Unfortunately, this also means that the consumption of memory occurs on a per-session basis, which is not very scalable. 
• The packaged collection approach consumed additional PGA memory, but the Oracle Database 11g function result cache did not.

And then, of course, there are the other key advantages of the function result cache: automatic invalidation of cache contents when a dependent table is changed, the fact that the cache is shared across sessions, and the application of the least recently used algorithm to the memory in the cache.

So Why Should You Care Now?

"All right," you may be saying to yourself, "It's cool. Super cool. But I still can't use it for two years or more, so what good does that do me now?"

You may not be able to use the PL/SQL function result cache yet, but you can write your code now so that when you eventually upgrade to Oracle Database 11g, you will be able to quickly and easily use this cache in your application code.

In other words, you can and should prepare now for this future feature.

How do you do that? By placing all your queries (at least those against tables that change infrequently but are queried often) inside functions, so that you can easily add the RESULT_CACHE clause.

Think about it: today you probably don't do that. Instead, whenever you need data from the database, you write the required query, right there in the application logic you are writing (whether that logic resides in the back end—other PL/SQL programs—or the front end—languages such as Java).

Next Steps READ more about  PL/SQL function result cache  oracle.com/technnetwork/database/documentation READ more  Best Practice PL/SQL  PL/SQL Practices DOWNLOAD  Oracle Database 11g  test scripts for comparing row retrieval speed

And that same query (or some minor variation on it) will likely appear in multiple places in your application code. Why not? It is so easy to write those SQL statements; that's one of the beauties of PL/SQL. But that ease of use in executing SQL inside PL/SQL makes us all take SQL for granted, and when you upgrade to Oracle Database 11g, you will pay the price.

If after upgrading, you want to take advantage of RESULT_CACHE, you will have to find every affected SQL statement and either put the RESULT_CACHE hint inside that query (that's right, this feature is available natively within SQL as well as for functions) or construct the function, put the query inside it, find each of the applicable queries, and replace the query with the function call.

Certainly both of these approaches are eminently doable, but they are also very unlikely to happen. IT managers are loathe to go into existing, working production code and upset the applecart by making lots of changes.

If, conversely, you start right now , in Oracle9i Database or Oracle Database 10g, to place your queries inside functions, you will almost instantly be able to upgrade your code to use this fantastic new feature when you upgrade to Oracle Database 11g.

And, best of all, the application code that calls the function will not have to be changed at all! Your manager will be very impressed.

And that is why you should learn about the new features of Oracle Database 11g. Today.