Use Package Data to Minimize SQL Access
When you declare a variable in a package body or a specification, its scope is not restricted to any particular procedure or function. As a result, the scope of package-level data is the entire Oracle session, and the value of that data persists for the entire session. Take advantage of this fact to minimize the frequency with which you have to go to the System Global Area (SGA) to access data. Performing lookups against structures located in your ownProgram Global Area (PGA) is much faster than going through the SGA—even if the data you want is resident in shared memory.
This tip is most handy when you find that your application needs to perform multiple lookups that do not change during your session. One very obvious example is the value returned by the USER function. It never changes during your session because it returns the name of the currently connected session. So why would you ever want to call USER more than once?
You might be thinking that it can't be that slow to run that built-in function. Well, no, it isn't, but it does execute a "SELECT FROM dual", and if you happen to have an application that runs USER many, many times, it can make a difference. I ran into just that situation when I was building a code generator (called PL/Generator) several years ago. After doing some performance analysis, I discovered that I was executing the USER function 15,000 times in the process of generating a large, complex package. That statistic almost certainly indicated a problem in my code base, but I didn't have time to sweep through my code and re-architect. So I took a different approach. I created the following package:
and then I very carefully performed a global search and replace operation (replace USER with thisuser.name). Recompile, execute . . . and, lo and behold, I shaved a few seconds off a 45-second generation session. That's not too bad. By using this packaged variable, I was able to call USER just once, and after that I simply evaluate the thisuser.name constant.
And that's the basic idea behindcaching data in session-level data structures. Of course, you might have much more complex requirements—you might want to cache a bunch of related data, say in a record structure, or you might even want to keep track of an entire list of already queried data.
Rather than present each of these scenarios in detail, I offer the following scripts (available from the O'Reilly site) that you can download and try yourself. Once you are convinced of the value of this technique, you can adapt my code to fit your requirements.
Caching a single value
Avoid repetitive queries for values like USER, SYSDATE (which, in Oracle9i, is finally no longer obtained with a "SELECT FROM dual") and even application-specific static values.
thisuser.pkg
Creates a package that caches the USER value, both in a named constant and via a function (which is callable from client-side PL/SQL).
thisuser.tst
A test script that demonstrates the improved performance of the cached approach.
Caching a single record of data
In the following script, I extract data from a user configuration table for the current user and cache that information in a record defined at the package level.
init.pkg
Creates the user configuration table and package, populates the table with test data.
init.tst
An associated test script.
Caching multiple rows of data
A more interesting and complex example, the following script demonstrates the use of acollection to cache multiple rows of data to avoid querying any single row more than once in a session. This technique works only when the data being queried is static for your entire session; this is probably most applicable to small code-description lookup tables.
emplu.pkg
Creates two packages, one that retrieves data with a standard query against the database, and another that accesses the data via a collection-based cache.
emplu.tst
A test script that demonstrates the improved performance of the cached approach.
You can find more information about data caching in package-level data in Chapter 17.