Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle and Memory Resources

Accessing information in memory is much faster than accessing information on a disk. An Oracle instance uses the database server’s memory resources to cache the information it uses to improve performance. Oracle uses an area of shared memory called the System Global Area (SGA) and a private memory area for each server process called the Program Global Area (PGA). Operating systems use virtual memory, which means that an Oracle instance can use more memory than is physically available on the machine.

Prior to Oracle9i, you could specify the size for the SGA or any of its components—database buffer cache, shared pool, or large pool—only in the initialization file, and the size of these memory allocations could not be changed without shutting down and restarting the instance. With Oracle9i, you can now dynamically resize these pools based on a minimum memory allocation called a granule .

Exhausting a database server’s supply of physical memory will cause poor performance. You should gauge the size of the various memory areas Oracle uses or add more memory to the machine to prevent a memory deficit from occurring. What constitutes the right size for the various areas is a function of your application behavior, the data it uses, and your performance requirements.

How Oracle Uses the System Global Area

Oracle uses the SGA for the following operations:

• Caching of database blocks containing table and index data in the database buffer cache

• Caching of parsed ...