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 diagram illustrates how this works, and shows the relationship between processes, the SGA, and the PGA:
Program Global Area
DBWR
LGWR
SMON
System Global Area
Server Process
Database User
Description of Relevant Features:
The image represents the memory architecture of an Oracle database, focusing on the interaction between various memory components and processes.
Program Global Area (PGA): Multiple instances are shown, associated with different users and server processes. PGAs store data and control information for individual server processes.
System Global Area (SGA): This central component acts as a shared memory structure accessible by multiple processes, serving as the core of Oracle’s memory management.
Database Processes (DBWR, LGWR, SMON): These essential background processes interact with the SGA:
DBWR (Database Writer): Responsible for writing modified data blocks from the SGA to disk.
LGWR (Log Writer): Handles logging of redo entries from the SGA to the redo log files.
SMON (System Monitor): Manages instance recovery, if necessary, and performs cleanup tasks.
Server Processes: These handle the requests of Database Users and interact with the SGA to execute SQL commands and retrieve data.
The Program Global Area
Program Global Area: This is a private memory area for a process.
Server Process: Server processes are created when users connect to Oracle.
System Global Area: This shared memory area used by all processes.
DBWR, LGWR, and SMON: Even the background processes get private memory.
Database User: Users communicate with server processes over the LAN.
Contents of PGA
The PGA is subdivided into different areas, each with a different purpose. Figure 5-9.1 shows the possible contents of the PGA for a dedicated server session. Not all of the PGA areas will exist in every case.
Figure 5–9.1 PGA Contents
Private SQL Area: A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas. Do not confuse a private SQL area, which is in the UGA, with the shared SQL area, which stores execution plans in the SGA. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. For example, 20 executions of SELECT * FROM employees in one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data. A cursor is a name or handle to a specific private SQL area. As shown in Figure 5-9.2, you can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably. Figure 5-9.2: Cursor
A private SQL area is divided into the following areas:
run-time area: This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan. Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.
persistent area: This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.
Program Global Area
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. 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.
DBWn:
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.
