To err is human, but to really foul things up takes
a computer. – Farmers’ Almanac (1978)

While programming in the Oracle Database you interact a lot with the data in the tables. Retrieving this data from the database involves context switching between the PL/SQL Engine and the SQL engine. This takes up a relatively large amount of time. You may want to reduce these context switches by caching the retrieved data. To do this Oracle supports a number of methods.

  • Package-Based Caching
  • Deterministic Function Caching
  • Function Result Cache (Oracle Database 11g)

Package-Based caching has been around since Oracle 7, since Collections are available (see
Collections in Oracle Part 1).
Deterministic Function Caching has been available since Oracle 8i and in version 11g Oracle offers an even more sophisticated caching mechanism, Function Result Cache. Lets look at these possibilities in more detail:

Package-Based Caching

The most basic version of caching available to us is Package-Based Caching. This involves creating your own data structures to hold the cached data. These data structures, which can be scalar variables, are made using package level variables. If you declare a package level variable and assign it a value, this value persists throughout the session. In other words, it will keep the value assigned until you disconnect, recompile the package or change the value.

When can you use Package-Based Caching

You can use Package-Based Caching when you have need for small reference tables in your PL/SQL Code, like ‘M’ for Male, ‘F’ for Female that will never change (never say never, but the chances are very small this will ever change). If your database server has enough memory to support a copy of the data for each session connected to the instance (and using the cache of course) you can use Package-Based Caching. The cache is built and kept in the PGA and there is a separate version of the cache for every session. This means that if the cache is only 4 Mb in size (which is relatively small) but there are
1000 concurrent sessions using the cache it would mean the need for 4 GB of memory to support the different caches.

If the data you are caching doesn’t change (or you don’t want to be aware of any changes) during your session then you can use Package-Based Caching and reduce the number of context switches your program needs.
If you are using version 11 or higher of the Oracle database you should consider Function Result Cache, but if you’re not, Package-Based Caching is a good option to use.

Examples

If you call the function user (yes, it’s a function, defined in the standard package) to retrieve the current username you are actually executing a SQL statement hence performing a couple of context switches. This is no problem if it’s executed just a couple of times, but consider a loop
that executes thousands or millions of times and needs the value of user in one (or more) of its statements. It may be a good idea to retrieve the value of user once, cache that value and use the cached value throughout the program.

How it is done:

First create a package with a variable to hold the value:

1. CREATE OR REPLACE PACKAGE mycache IS
2. c_user CONSTANT VARCHAR2(30) := USER;
3. END;

Using a simple anonymous block to show the differences between the two approaches:

1. DECLARE
2.    c_iterations CONSTANT PLS_INTEGER := 1000;
3.    l_user VARCHAR2(30);
4. BEGIN
5.    timer.snap;
6.    FOR indx IN 1 .. c_iterations LOOP
7.      l_user := USER;
8.    END LOOP;
9.    timer.show(prefix_in => 'Normal USER');
10.   timer.snap;
11.   FOR indx IN 1 .. c_iterations LOOP
12.     l_user := mycache.c_user;
13.   END LOOP;
14.   timer.show(prefix_in => 'Package USER');
15. END;

Some results:

Number of iterations User Function Cached value
1000 0.02 seconds 0.00 seconds
10000 0.13 seconds 0.00 seconds
100000 1.45 seconds 0.00 seconds
1000000 13.92 seconds 0.01 seconds

Another caching mechanism is the caching of table data. This should only be used if the data is static during the session. For instance for data that is updated during the night, but queried lots of times during the day.

In the package used for this caching mechanism some more work needs to be done. It takes a couple of extra braincycles to get it working order, but as you can see from the results that is well worth it.

1. CREATE OR REPLACE PACKAGE mydept IS
2.   FUNCTION getdname(deptno_in IN dept.deptno%TYPE) RETURN dept.dname%TYPE;
3. END mydept;

1. CREATE OR REPLACE PACKAGE BODY mydept IS
2.   TYPE dept_tt IS TABLE OF dept.dname%TYPE INDEX BY BINARY_INTEGER;
3.
4.   g_depts dept_tt;
5.
6.   FUNCTION getdname(deptno_in IN dept.deptno%TYPE) RETURN dept.dname%TYPE IS
7.   BEGIN
8.     RETURN g_depts(deptno_in);
9.   END getdname;
10.
11.   PROCEDURE initialization IS
12.   BEGIN
13.     FOR rec IN (SELECT d.deptno
14.                       ,d.dname
15.                  FROM dept d) LOOP
16.       g_depts(rec.deptno) := rec.dname;
17.     END LOOP;
18.   END initialization;
19. BEGIN
20.   initialization;
21. END mydept;

1. DECLARE
2.   c_iterations CONSTANT PLS_INTEGER := 1000;
3.   l_dname dept.dname%type;
4. BEGIN
5.   timer.snap;
6.   FOR indx IN 1 .. c_iterations LOOP
7.     select dname
8.       into l_dname
9.       from dept
10.    where deptno = 10;
11.   END LOOP;
12.   timer.show(prefix_in => 'Using SQL');
13.   timer.snap;
14.   FOR indx IN 1 .. c_iterations LOOP
15.     l_dname := mydept.getdname(10);
16.   END LOOP;
17.   timer.show(prefix_in => 'Using Caching');
18. END;
Number of iterations Using SQL Using Caching
1000 0.02 seconds 0.00 seconds
10000 0.29 seconds 0.00 seconds
100000 2.80 seconds 0.03 seconds
1000000 26.52 seconds 0.36 seconds

The timer package is available for download at bar-solutions.com

If you have a bigger table you want to cache you can of course also take advantage of the bulk
colllection features described in bulk-processing-in-oracle-part-1

Ref: Oracle PL/SQL Programming 5th Edition – Steven Feuerstein

Go to Caching – Part 2