Oracle Sort Areas and PGA Work-Area Sizing

A sort area is the SQL work area used by one execution-plan operator to order or organize rows. Oracle normally allocates this private execution memory from the Program Global Area (PGA) of the server process performing the work. The allocation belongs to an operator and execution; it is not one permanent block reserved for every database user.

A single SQL statement can contain several sort operators and therefore several sort areas. Parallel execution can increase aggregate demand further because multiple parallel execution servers can allocate work areas. Oracle AI Database 26ai manages these competing requirements dynamically so that useful operations can proceed without assigning every possible sort its optimal amount of memory at the same time.

The execution plan determines whether a sort occurs. An ORDER BY clause often produces a sort, but Oracle may obtain the required order from an access path and avoid a separate sort operator. Conversely, a plan may sort for grouping, analytics, a merge join, duplicate elimination, or index creation even when the SQL statement has no outer ORDER BY clause.

Only an outermost ORDER BY guarantees the order of a query's final result. A plan may happen to return grouped or indexed rows in a recognizable sequence, but applications must not treat that physical behavior as a contractual order. Likewise, seeing a sorting-related SQL clause does not prove that the plan contains a separate sort operator. Examine the actual or displayed execution plan.

1. Oracle opens a sort operator, and the memory manager assigns its work-area size.
2. Rows enter the operator and are organized in private process memory.
3. If the work area holds the input and auxiliary structures, the operation runs in optimal mode.
4. If more space is required, Oracle uses a temporary segment and performs one-pass or multipass processing.
5. Oracle consumes or returns the sorted rows, closes the operator, and reclaims the work-area allocation.

A temporary spill is not a database failure. It is a normal mechanism that bounds memory use when many operations compete for PGA resources. Temporary-storage I/O becomes a performance concern when important statements repeatedly perform large one-pass operations or, especially, multipass work that materially increases elapsed time and storage traffic.

These classifications describe the work performed by an individual operator. They do not establish the complete statement's performance by themselves. Access paths, join order, cardinality estimates, concurrency, CPU, temporary-storage performance, parallelism, and the number of rows returned can dominate the total response time.

Nor does “optimal” mean that Oracle should grant unlimited memory to every operation. Under a concurrent workload, increasing one work area's allocation reduces memory available for other work areas or other database components. Automatic management balances those demands at the instance level.

WORKAREA_SIZE_POLICY=AUTO is the current default. In automatic mode, Oracle sizes work areas according to the aggregate PGA target, current PGA consumption, operator requirements, and active workload. The memory bound can change as concurrency changes, so two executions of the same plan do not necessarily receive identical work-area sizes.

Automatic sizing responds to the workload rather than treating a sort in isolation. When only a few work areas are active, Oracle may be able to assign more memory to each one. As additional sessions open memory-intensive operators, the memory manager can reduce the bound available to an individual work area so that aggregate PGA demand remains manageable. A statement that executes optimally during a quiet interval can therefore use one-pass processing during a highly concurrent interval without any change to its SQL text or plan.

Parallel execution adds another dimension. The query coordinator and parallel execution servers participate in the statement, and the worker processes can allocate work areas for the operators they perform. Total memory and temporary-space demand must therefore be evaluated across the parallel operation rather than inferred from one session's visible allocation. Increasing parallelism can shorten elapsed time for suitable work, but it can also increase concurrent PGA and temporary-storage demand.

In a multitenant database, parameter settings and dynamic-performance-view rows can also have container scope. The CON_ID columns in current views distinguish CDB-wide, root, and PDB-related data where applicable. A DBA should confirm the current container and privileges before interpreting a result or comparing it with a CDB-level target. This lesson keeps the SQL compact, but production analysis must retain that scope.

The following query displays relevant parameter state without assuming which instance-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',
         'sort_area_size',
         'sort_area_retained_size'
       )
ORDER  BY name;

The two SORT_AREA_* parameters can appear in V$PARAMETER even when automatic policy means they do not govern ordinary SQL work-area sizing. Interpret them together with WORKAREA_SIZE_POLICY and the active instance-memory mode. A displayed value by itself is not evidence that the parameter is controlling the current sort.

A nonzero PGA_AGGREGATE_TARGET selects automatic work-area sizing. Setting the target to zero selects manual policy for backward compatibility. Oracle 26ai also offers unified instance memory through MEMORY_SIZE; this alternative model coordinates more than the PGA and should not be treated as another knob to enable alongside every older memory-management method.

Older Oracle guidance often begins with SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE. Both parameters remain documented, but Oracle retains them for backward compatibility and does not recommend using them unless the instance uses shared server. Automatic sizing through PGA_AGGREGATE_TARGET is the normal current model.

In manual mode, SORT_AREA_SIZE limits the amount each sort can consume. One query can have multiple sorts, and parallel execution servers can allocate separate areas. When the operation needs more memory than the configured amount, Oracle uses temporary segments for intermediate runs.

SORT_AREA_RETAINED_SIZE is frequently misunderstood. It limits UGA memory retained after a sort run completes and controls the read buffer used while Oracle returns the sorted rows. After the last row is fetched from the sort space, Oracle releases that memory back to the UGA. It is not memory saved for a future statement's sort.

Connection mode changes the placement of session memory, but it does not turn SQL work areas into shared memory. With dedicated server, the User Global Area (UGA) and SQL work areas are associated with the dedicated server's PGA. With shared server, persistent session state must be available to different shared server processes, so the UGA resides in the SGA while work areas remain in the private PGA of the process doing the work.

The manual retained-size parameters have one additional shared-server rule. Allocation up to SORT_AREA_RETAINED_SIZE is made in the SGA, while the difference between SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE is allocated in the servicing process's PGA. This is legacy manual behavior, not the fixed-threshold model used by automatic work-area sizing.

The two OCI figures show where the connection models differ. OCI deployment does not change the architectural rule: the PGA remains private to a process, while session state that must survive movement among shared server processes must be accessible through shared memory.

Diagnose current behavior before changing memory. V$SQL_WORKAREA_ACTIVE provides an instantaneous view of work areas that are currently allocated. If an active work area has spilled, the view can include its temporary-segment size and tablespace.

SELECT sid,
       sql_id,
       operation_type,
       policy,
       ROUND(actual_mem_used / 1024 / 1024, 2) AS actual_mb,
       ROUND(max_mem_used / 1024 / 1024, 2) AS max_mb,
       number_passes,
       ROUND(tempseg_size / 1024 / 1024, 2) AS temp_mb,
       tablespace
FROM   v$sql_workarea_active
WHERE  operation_type IN ('SORT', 'GROUP BY')
ORDER  BY actual_mem_used DESC;

• POLICY identifies automatic or manual sizing for the active work area.
• ACTUAL_MEM_USED is the PGA memory currently allocated on behalf of the work area.
• MAX_MEM_USED is the maximum memory observed for it.
• NUMBER_PASSES=0 indicates optimal execution.
• TEMPSEG_SIZE and TABLESPACE describe temporary-segment use when a spill has occurred.

The filter keeps the example concise. Other sorting-related plan operations can appear under additional OPERATION_TYPE values. A DBA investigating a specific SQL statement can remove or broaden the filter and relate the work-area row to its execution plan.

V$SQL_WORKAREA_HISTOGRAM reports cumulative work-area execution counts by memory-requirement group:

SELECT SUM(optimal_executions)     AS optimal_executions,
       SUM(onepass_executions)     AS onepass_executions,
       SUM(multipasses_executions) AS multipass_executions,
       SUM(total_executions)       AS total_executions
FROM   v$sql_workarea_histogram;

These values accumulate since instance startup. A before-and-after comparison around a representative workload is more meaningful than treating lifetime totals as one current incident. V$TEMPSEG_USAGE complements this evidence by identifying sessions and SQL associated with active temporary-segment usage at the time the view is queried.

High temporary-space activity does not establish that the PGA target is too small. It shows that one or more operators used temporary storage. Determine why the operator processed that volume and whether the spill materially affected the workload before changing instance memory.

1. Identify the SQL statement and the sort, grouping, or related work-area operator in its execution plan.
2. Determine whether the relevant execution was optimal, one-pass, or multipass.
3. Measure temporary-space consumption and elapsed-time impact during a representative interval.
4. Check whether concurrency, parallel execution, or other PGA consumers reduced memory available to active work areas.
5. Choose among SQL improvement, a different plan, an index, reduced result volume, changed parallelism, memory adjustment, or added capacity.
6. Repeat the workload and measure the result instead of assuming that the change helped.

Reducing unnecessary work can be more effective than granting more memory. A suitable index may supply required order, an unnecessary DISTINCT may be removable, or an inaccurate plan may sort far more rows than the query ultimately returns. These are possibilities, not universal prescriptions; the optimizer, data distribution, write cost, and full workload determine whether a particular change is useful.

Storage architecture also matters, but modern deployments cannot be reduced to distributing database components across separate physical drives. Temporary storage may be ASM-managed, engineered, virtualized, cloud-backed, or controlled by a managed service. Diagnose the observed SQL and storage path rather than applying a legacy drive-placement formula.

• A sort area is private work memory for one execution-plan sort operator.
• One statement can contain several work areas, and parallel execution can multiply aggregate memory demand.
• Automatic policy dynamically sizes work areas from available aggregate PGA resources.
• Optimal, one-pass, and multipass modes describe the extra processing required by an operator.
• Temporary spills are normal, but repeated large multipass work should be investigated.
• SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE are backward-compatible manual controls, not default tuning guidance.
• Current execution plans and work-area views should support any SQL, memory, parallelism, or storage change.

Oracle AI Database 26ai manages sort memory as part of the larger PGA workload. The useful DBA question is not simply “How large is the sort area?” but “Which operator used the work area, how many passes did it require, what temporary I/O resulted, and what evidence supports the proposed remedy?” That question connects memory architecture to measurable SQL performance.