Memory Used by an Instance

System Global Area
The System Global Area (SGA) is an area of memory that is accessible to all user processes of an Oracle
instance. Three main areas are used by the SGA:
❑ The redo log buffer holds information used for recovery until the information can be written to
the redo log.
❑ The shared pool holds information that can be shared across user processes, such as execution
plans for SQL statements, compiled stored procedures, and information retrieved from the
Oracle data dictionary.
❑ The database buffer pools are a group of memory areas that are used to hold data blocks. A data
block held in a buffer pool can be accessed much more rapidly than going to disk to retrieve
the block, so efficient use of these memory pools is essential for achieving optimal performance.
Following are the three basic database buffer pools:
❑ The DEFAULT pool holds all database objects, unless otherwise specified.
❑ The KEEP pool is used to hold objects in memory if specified for a table or index.
❑ The RECYCLE pool is used for objects that are not likely to be reused again.
Keep the following two points in mind:
❑ For all of these pools, the Oracle instance uses a Least Recently Used (LRU)
algorithm to determine what data blocks to swap out.
❑ In addition to these main pools, the Oracle instance can also use a large pool,
which is used for shared server, backup and recovery, and I/O operations. You
can either configure a large pool or Oracle can create it automatically, if you
configure adaptive parallelism, which is described further in Chapter 2.

Program Global Area
The Program Global Area (PGA) is an area of memory that is just available to a single server process.
The PGA contains items like user variables and cursor information for an individual user’s SQL state-
ment, such as the number of rows that have been retrieved so far. The PGA is also used for sorting data
for an individual user process.
Each time a new SQL statement is received for a user, a new space in the PGA has to be initialized. To
avoid this overhead, transaction-intensive applications, such an online transaction processing (OLTP)
applications, typically use a small set of SQL statements that are continually reused by a user process,
reducing this potential source of overhead.
You can have a single server process handle more than one user process by configuring shared servers,
which are described in Chapter 2, “Using SQL.”