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.
Let’s first look at some basics. An Oracle database
instance consists of a shared memory area known
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
. 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 ...