Contents of the Oracle Program Global Area (PGA)

The Program Global Area (PGA) is nonshared memory that contains data and control information used by one Oracle process. Oracle creates a PGA for every foreground server process and every background process, such as DBWn or LGWR, and releases that memory when the process terminates. Some Oracle performance documentation expands PGA as Process Global Area; both names refer to the same process-private memory.

This ownership distinguishes the PGA from the System Global Area (SGA). The SGA contains information that processes attached to an instance can share, including cached data blocks and shared SQL areas. A PGA instead holds state that belongs to one process or execution context. Other processes cannot use that PGA as a common memory pool.

The distinction matters whenever Oracle executes SQL. A shared SQL area in the SGA can contain a statement's parse tree and execution plan so that several sessions can reuse them. Each execution still needs private values and state, including bind values, cursor position, row-processing state, and memory for sorts or hash operations. Those private requirements are associated with a process's PGA, subject to connection-mode details discussed later in this lesson.

A PGA is not one flat, permanently divided block. It is a private allocation containing structures that appear, grow, shrink, and disappear as a process performs work. For a dedicated server connection, the most important conceptual contents are the private SQL area, SQL work areas, the User Global Area, and supporting process memory.

A private SQL area stores session-specific information used to process a SQL statement. When a session issues a statement, its private SQL area can refer to a shared SQL area in the SGA while retaining state that must not be shared with another session. Multiple private SQL areas can therefore point to one reusable statement and execution plan without sharing bind values or execution progress.

Representative private SQL information includes:

• Bind variable values and related persistent cursor state
• Runtime execution state, including row-processing information
• The current fetch position for a cursor returning rows
• Control information required by the current execution

The persistent part of the private SQL area retains information associated with a cursor, such as bind-related state. Its runtime part represents a particular execution. Executing a cursor creates runtime state for that execution; closing or completing the work allows Oracle to release state that is no longer needed. A cursor is commonly described as a handle or name for the associated private SQL area.

The word private is essential. Oracle may share parsed statement information and an execution plan, but it cannot let one session overwrite another session's bind values, fetch position, or intermediate state. This separation permits reuse without confusing the executions that depend on the shared SQL.

SQL work areas are PGA allocations used by memory-intensive operators. A complex execution plan can contain several operators, each with its own work area. Parallel SQL can use work areas in several processes, so the statement's total memory demand may be larger than the PGA allocation visible for any one process.

Operations that can require SQL work areas include:

• Sorts for ORDER BY, GROUP BY, ROLLUP, and analytic or window functions
• Hash joins and other hash-based operations
• Bitmap index merge and bitmap creation operations
• Write buffers used by bulk-load operations

Oracle automatically sizes these tunable work areas when WORKAREA_SIZE_POLICY is AUTO. The allocation reflects the operator's need, the current workload, and the memory available under the aggregate target. The work area is not a fixed amount permanently reserved for the session; Oracle allocates it for an operation and can reclaim it when that operation finishes.

Oracle describes a work area's execution according to how much of the operation fits in memory:

• Optimal: the work area is large enough to process the operation in memory without an extra pass over its input.
• One-pass: the operator performs one additional pass over part of the input because the work area is smaller than optimal.
• Multipass: the operator requires multiple passes, increasing temporary I/O and potentially degrading response time sharply.

A larger PGA target may allow more work areas to run optimally or in one-pass mode, but it is not automatically the correct response to every SQL performance problem. A DBA must consider execution plans, workload concurrency, temporary I/O, SGA requirements, operating-system memory, and platform configuration together.

The User Global Area (UGA) is memory associated with a database session. It stores session state such as logon information and other values that must remain available as the session continues. If a session loads a stateful PL/SQL package, the values of the package variables are also part of the session's state. Ordinary local variables do not all persist for the full session simply because PL/SQL is running.

In a dedicated server connection, the UGA resides in the dedicated server process's PGA. The process and session relationship makes private placement possible: the same server process continues servicing that session and can access its state in its own memory.

In a shared server connection, the UGA cannot remain tied to one shared server's PGA because a different shared server process may handle a later request for the same session. Oracle therefore keeps the UGA in the SGA, normally in the large pool when it is configured or otherwise in the shared pool. This is a relocation of session state; it does not make the individual PGAs shared.

An Oracle process also needs call-stack space, local variables, temporary data structures, and internal control memory to run its code. These supporting allocations grow and shrink with process activity and disappear when the process terminates. They should not be treated as a complete, invariant map of every PGA structure.

Oracle's process-memory views group dynamic PGA allocations into practical categories such as SQL, PL/SQL, Java, OLAP, Freeable, and Other. These monitoring categories are useful for diagnosis, but they describe observed allocation types rather than replacing the conceptual distinctions among the private SQL area, work areas, and session state.

Every server and background process has its own PGA, but Oracle also measures and manages their combined allocation. The collection of these individual private regions is the instance PGA. It is an aggregate total, not a single shared area into which all processes write.

Figure 5-10 shows three foreground server-process PGAs and representative background-process PGAs. The diagram's enclosing instance-PGA boundary expresses aggregation, not shared access. DBWn cannot use a server process's PGA, and one server process cannot use another server process's PGA.

Oracle normally sizes SQL work areas automatically. The parameters below operate at different levels, so a target should not be confused with a limit or with a complete instance-memory management model.

A nonzero PGA_AGGREGATE_TARGET enables automatic work-area sizing and results in WORKAREA_SIZE_POLICY=AUTO. Setting the target to zero selects manual work-area sizing for backward compatibility. Current installations should not adopt legacy per-operation *_AREA_SIZE tuning without a specific, validated reason.

Oracle attempts to keep aggregate private memory near the target by adjusting tunable work areas. Other PGA consumers are less tunable. PL/SQL, Java, and other allocations can reduce the portion available to automatic work areas, and total allocation can temporarily or materially exceed the target. PGA_AGGREGATE_LIMIT supplies the separate upper bound. If the limit is exceeded, Oracle first cancels calls for sessions consuming the most untunable PGA memory and can then terminate sessions if usage remains excessive.

Oracle AI Database 26ai also introduces unified memory management through MEMORY_SIZE. In that mode, Oracle coordinates usable memory across the SGA, PGA, Managed Global Area (MGA), UGA, and other segments. This is an alternative management model, not an instruction to enable MEMORY_SIZE, MEMORY_TARGET, and independent memory controls simultaneously. Platform, large-page configuration, workload, and deployment architecture determine the appropriate model.

In a multitenant database, PGA targets and limits can also participate in PDB resource control under CDB-level constraints. This lesson focuses on instance architecture; production PDB values should be chosen with the documented Resource Manager and memory-parameter rules.

The following query displays the relevant initialization-parameter state without assuming which memory model is active:

SELECT name,
       display_value
FROM   v$parameter
WHERE  name IN (
         'memory_size',
         'memory_target',
         'pga_aggregate_target',
         'pga_aggregate_limit',
         'workarea_size_policy'
       )
ORDER  BY name;

A zero value can mean that a particular mode is inactive. Interpret each value together with the other memory parameters and the instance's deployment model rather than treating one row as a complete description of memory management.

V$PGASTAT reports instance-level PGA statistics. This query converts only byte-valued statistics to megabytes:

SELECT name,
       ROUND(value / 1024 / 1024, 2) AS megabytes
FROM   v$pgastat
WHERE  unit = 'bytes'
AND    name IN (
         'aggregate PGA target parameter',
         'aggregate PGA auto target',
         'total PGA allocated',
         'total PGA inuse',
         'total freeable PGA memory'
       )
ORDER  BY name;

• total PGA allocated is the amount currently allocated by the instance.
• total PGA inuse indicates the amount currently consumed by active work areas.
• aggregate PGA auto target is the amount currently available to work areas running in automatic mode.
• total freeable PGA memory is allocated memory that Oracle can potentially release.

Process-level diagnosis uses different views. V$PROCESS exposes PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, and PGA_MAX_MEM for each Oracle process. V$PROCESS_MEMORY categorizes dynamic allocations. Work-area history and PGA advice views provide deeper tuning evidence, but those diagnostics extend beyond the objective of naming PGA contents.

The essential PGA concepts are:

• Every Oracle server or background process owns private PGA memory.
• Private SQL areas retain session-specific statement and execution state while referring to reusable shared SQL information in the SGA.
• SQL work areas support sorts, hashes, bitmap operations, and other memory-intensive operators.
• The UGA contains session state and resides in the PGA for dedicated server connections but in the SGA for shared server connections.
• Call-stack and other internal allocations support the process without forming one permanent, uniform layout.
• The instance PGA is the aggregate of individual PGAs, not another shared global area.
• Automatic management sizes tunable work areas toward a target, while PGA_AGGREGATE_LIMIT protects the aggregate upper bound.

Understanding these boundaries prevents common memory-architecture errors. The SGA shares reusable information, the PGA protects process-private execution state, and the UGA follows the lifetime and accessibility needs of a session. With this conceptual model, a DBA can interpret PGA views and memory controls without assuming that every private allocation is a work area or that the aggregate target is an absolute limit.

Test your understanding of PGA contents, process ownership, and memory placement.

Take the Oracle PGA quiz

[1] private SQL area: An area in memory that holds a parsed statement and other information for processing. The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas.