Name
PKG-06: Use package body persistent data structures to cache and optimize data-driven processing.
Synopsis
When you declare data inside a package but not within any individual procedure or function in the package, that data persists for your entire session.[1] A package-level collection, for example, retains its values (say, 1000 rows of data) until you DELETE one or more rows from the collection, close your connection, or recompile the package.
This data persistence means you can use package data as a “local” cache—local to that single session/user. The System Global Area (SGA) acts as a cache for all users and greatly improves overall database performance. Your own session-specific cache can improve your application performance. You can cache at multiple levels:
A single value, such as the name of the current user
A record of values, such as the default configuration for the current user
An entire collection or list of values, such as the result set of a query that must be processed multiple times
Regardless of the complexity of data, the conditions and steps for caching are similar:
The data must be static for the duration of the session. It’s possible to come up with ways to update the cache, but such efforts are likely to cancel out performance gains.
You need to declare the data structures inside the package body so that you can manage their contents and integrity (see [DAT-15: Expose package globals using “get and set” modules.]).
You also need to build access programs to those data structures so that even inside the package body, you manipulate the cache through a well-defined interface.
Example
For reasons of space, I will show the simplest package-based caching mechanism here. See Section for more complex sample packages.
Consider the USER function. It returns the name of the currently connected user. This value never changes during your session, right? The USER function in PL/SQL is implemented as follows (as defined in the STANDARD package, and with less-than-ideal formatting):
function USER return varchar2 is
c varchar2(255);
begin
select user into c from sys.dual;
return c;
end;So every time you call USER in PL/SQL, it runs a query. That is quite unnecessary, and you can use caching to ensure that this query is run just once per session:
CREATE OR REPLACE PACKAGE thisuser IS name CONSTANT VARCHAR2(30) := USER; END thisuser; /
Now you can reference the value of USER without multiple calls to USER, like this:
FOR user_rec IN user_cur
LOOP
IF user_rec.user_name = thisuser.name
THEN
...See Section for a reference to the thisuser package; there you will find a script you can run to test the performance advantage of thisuser over direct, repetitive calls to USER.
Benefits
This technique improves application performance by avoiding unnecessary and (relatively speaking) slow data access through the SGA.
It’s especially handy when you execute long-running batch processes that must perform multiple passes through large result sets. Load up the query results in a collection of records, and then you have bidirectional, random access to the data for your batch process.
Challenges
Each session has its own copy of package data, and Oracle uses real memory for this session data. So if you plan to cache data, be aware of the volume of data and the number of users who will cache it.
Resources
init.pkg and init.tst : An example package and script to compare the performance of caching a record of data.
emplu.pkg and emplu.tst : An example package and script to compare the performance of caching multiple rows of data.
[1] You can insert the PRAGMA SERIALLY_REUSABLE statement into your package if you don’twant package level data to be persistent.