DEVELOPER: PL/SQL Practices
On the PL/SQL Function Result CacheBy 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 the PL/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
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:
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).
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.
Steven Feuerstein (email@example.com) is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein's self-appointed mission in life these days is to improve the quality and quantity of PL/SQL code testing.