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. Every process that connects to an Oracle database,
whether a server process handling a client session or a background process like
DBWn or LGWR, gets its own Program Global Area. Unlike the System Global Area, which every process
shares, a PGA belongs to exactly one process and nothing else can read it. The two diagrams later in
this lesson show exactly what lives inside a PGA and how that memory relates to the shared SQL
structures in the SGA.
Contents of PGA
The PGA is subdivided into several distinct areas, each with a different purpose, and not every area
exists in every case. Figure 5-9.1 shows the possible contents of a PGA for a dedicated server
session.
Figure 5-9.1: Possible contents of a PGA for a dedicated server session, including SQL
Work Areas (Sort Area, Hash Area, Bitmap Merge Area), Session Memory, and the Private SQL Area
(Persistent Area and Runtime Area). Not all of these areas exist in every case.
Private SQL Area: holds information about a parsed SQL statement and other
session-specific state. When a server process executes SQL or PL/SQL, it uses the private SQL area
to store bind variable values, query execution state, and query execution work areas. Don't confuse
the private SQL area, which lives in the UGA, with the shared SQL area, which stores execution plans
in the SGA. Multiple private SQL areas, across the same session or entirely different sessions, can
all point to a single shared execution plan. Twenty executions of SELECT * FROM employees
in one session and ten executions of that same query in a different session can all share the same
plan, even though each execution's private SQL area holds its own values and data, never shared with
the others.
A cursor is a name or handle to a specific private SQL area. As Figure 5-9.2 below shows, you can
think of a cursor as a pointer on the client side and as actual state on the server side. Because
cursors and private SQL areas are so closely linked, the two terms often get used interchangeably.
Figure 5-9.2: A cursor as a client-side handle and server-side state. The client process
holds only a pointer to the private SQL area; the actual Persistent Area (bind variable values) and
Runtime Area (query execution state) live in the server process's PGA, which in turn references a
shared execution plan in the SGA's Library Cache. Under a shared server configuration, this same
session state instead lives in the UGA, typically in the Large Pool, while each shared server
process still keeps its own separate PGA.
A private SQL area divides into two areas:
Runtime area: holds query execution state information, for example, tracking how
many rows a full table scan has retrieved so far. Oracle creates the runtime area as the first step of
an execute request, and for DML statements, frees it as soon as the statement closes.
Persistent area: holds bind variable values, supplied to a SQL statement at
runtime when it actually executes. Unlike the runtime area, the persistent area is only freed when
the cursor itself is closed, not when the statement finishes.
Program Global Area
Program Global Areas store information that does not need to be, or should not be, shared across
processes and sessions. Where that memory actually lives depends on how the session connected. In a
shared server configuration, many users share a smaller number of connections to the
database, which minimizes server memory usage but can affect response time under load; in this
configuration, the SGA holds session information for a user instead of the PGA. Shared server suits a
large number of simultaneous connections making infrequent or short-lived requests. In a
dedicated server configuration, each user process gets its own connection, and the
PGA holds that session's memory directly. The PGA also includes a sort area, used whenever a request
requires a sort, bitmap merge, or hash join operation.
The PGA_AGGREGATE_TARGET parameter, together with WORKAREA_SIZE_POLICY,
lets a DBA set a single total size for all work areas and have Oracle manage and allocate that memory
across every user process automatically, rather than sizing each work area by hand.
MEMORY_TARGET goes a step further, balancing all memory available to Oracle between the
SGA and the Program Global Area together to optimize performance. The next lesson covers this in more
depth.
DBWn: the database writer process, called DBWR (singular) in much older versions
of Oracle, writes new or changed data blocks, dirty blocks, from the buffer cache to the datafiles.
Using an LRU-based algorithm, DBWn writes the oldest, least active blocks first, keeping the most
commonly requested blocks in memory even if they happen to be dirty. Up to 100 DBWn processes can run
at once. The first 36 are named DBW0 through DBW9 and DBWa
through DBWz; any beyond that, up to the maximum of 100, are named BW36
through BW99 (no leading "D" on this tier). The actual number running 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 every user of that instance. When an instance starts,
memory is allocated for the SGA based on the initialization parameter file. Most of the parameters
controlling individual SGA component sizes are dynamic; however, if SGA_MAX_SIZE is
specified, the total size of every SGA area combined cannot exceed it. If SGA_MAX_SIZE
isn't specified but SGA_TARGET is, Oracle automatically adjusts the SGA components so
their combined size equals SGA_TARGET, itself a dynamic parameter you can change while
the instance is running. MEMORY_TARGET balances all memory available to Oracle between
the SGA and the PGA together to optimize performance automatically. Memory in the SGA is allocated in
units called granules, whose size Oracle determines based on the total SGA size rather than something
you configure directly.
