A Program Global Area, or PGA as it is often called, is an area of private memory set aside for the exclusive use of one process. Each process that connects to an Oracle database, whether a server process or a background process, gets a Program Global Area assigned to it.
The following MouseOver illustrates how this works, and shows the relationship between processes, the SGA, and the PGA:
PGA DBWR SGA
Program Global Areas are used to store information that does not need to be shared, or that should not be shared, by all processes and user sessions.
Program Global Area
The Program Global Area is an area of memory allocated and private for one process. The
configuration of the PGA depends on the connection configuration of the Oracle database: either shared server or dedicated.
In a shared server configuration, multiple users share a connection to the database, minimizing memory usage on the server, but potentially affecting response time for user requests. In a shared server environment,
the SGA holds the session information for a user instead of the PGA. Shared server environments are ideal for a large number of simultaneous connections to the database with infrequent or short-lived requests.
In a dedicated server environment, each user process gets its own connection to the database; the PGA contains the session memory for this configuration.
The PGA also includes a sort area. The sort area is used whenever a user request requires a sort, bitmap merge, or hash join operation.
As of Oracle9i, the PGA_AGGREGATE_TARGET parameter, in conjunction with the WORKAREA_SIZE_POLICY initialization parameter, can ease system administration by allowing the DBA to choose a total size for all work areas and let Oracle manage and allocate the memory
between all user processes. As mentioned earlier in this chapter, the parameter MEMORY_TARGET manages the PGA and SGA memory as a whole to optimize performance. The next lesson talks more about this.
The database writer process, known as DBWR in older versions of Oracle, writes new or changed data blocks (known as dirty blocks) in the buffer cache to the datafiles. Using an LRU algorithm, DBWn writes the oldest, least active blocks first.
As a result, the most commonly requested blocks, even if they are dirty blocks, are in memory. Up to 20 DBWn processes can be started, DBW0 through DBW9 and DBWa through DBWj.
The number of DBWn processes is controlled by the DB_WRITER_PROCESSES parameter.
System Global Area
The System Global Area is a group of shared memory structures for an Oracle instance, shared by the users of the database instance.
When an Oracle instance is started, memory is allocated for the SGA based on the values specified in the initialization parameter file or hard-coded in the Oracle software. Many of the parameters that control the size of the various parts of the SGA are dynamic;
however, if the parameter SGA_MAX_SIZE is specified, the total size of all SGA areas must not exceed the value of SGA_MAX_SIZE. If SGA_MAX_SIZE is not specified, but the parameter SGA_TARGET is specified,
Oracle automatically adjusts the sizes of the SGA components so that the total amount of memory allocated is equal to SGA_TARGET. SGA_TARGET is a dynamic parameter; it can be changed while the instance is running.
The parameter MEMORY_TARGET, new to Oracle 11g, balances all memory available to Oracle between the SGA and the
Program Global Area (discussed later in this chapter) to optimize performance. Memory in the SGA is allocated in units of granules. A granule can be either 4MB or 16MB, depending on the total size of the SGA.
If the SGA is less than or equal to 128MB, a granule is 4MB; otherwise, it is 16MB.