Oracle PL/SQL Programming, Third Edition

PL/SQL’s Use of Memory in the Oracle Server

By economizing on its use of machine resources such as memory and CPU, Oracle can support tens of thousands of simultaneous users on a single database. Oracle’s memory management techniques have become quite sophisticated over the years, and correspondingly difficult to understand. Although administrators of busy databases need a thorough knowledge of memory management, advanced PL/SQL programmers should also have a good understanding of this topic. Virtually all PL/SQL programmers will want to know how to avoid undermining Oracle’s memory sharing algorithms.

Server Memory 101

Let’s first look at some basics. An Oracle database instance consists of a shared memory area known as the System Global Area (SGA), plus a number of background processes.^[25] Although a large part of the SGA typically consists of buffer pools that cache table data, another part that’s of particular importance to PL/SQL performance is the shared pool. The shared pool performs two maincaching functions:

• It holds metadata from the data dictionary

• It holds parsed representations of SQL statements and PL/SQL programs

The first time any user session runs a particular PL/SQL program, Oracle puts the executable portion of the program into the part of the shared pool called the library cache . When that session needs to execute the same PL/SQL program, Oracle will re-use the cached copy of it, saving a trip to the relatively slow disks. Oracle can even use this cached ...