Oracle PL/SQL Programming, 5th Edition

Data Caching Techniques

A very common technique for improving performance is to build caches for data that needs to be accessed repeatedly—and that is, at least for some period of time, static (does not change).

The SGA of the Oracle database is the “mother of all caches,” Oracle-wise. It is a (usually) very large and (always) very complex area of memory that serves as the intermediary between the actual database (files on disk) and the programs that manipulate that database.

As described more thoroughly in Chapter 20, the SGA caches the following information (and much more, but these are the most relevant for PL/SQL programmers):

• Parsed cursors

• Data queried by cursors from the database

• Partially compiled representations of our programs

For the most part, however, the database does not use the SGA to cache program data. When you declare a variable in your program, the memory for that data is consumed in the PGA. Each connection to the database has its own PGA; the memory required to store your program data is, therefore, copied in each connection that calls that program.

Fortunately, there is a benefit to the use of PGA memory: your PL/SQL program can retrieve information more quickly from the PGA than it can from the SGA. Thus, PGA-based caching offers some interesting opportunities to improve performance. Oracle also provides other PL/SQL-specific caching mechanisms to help improve performance of your programs. In this section, you will learn about three types of PL/SQL caching:

Package-based ...