Oracle Database 11g -- SQL Work Area Memory Limits of Server Processes

Problem

A single server process cannot use more than 2GB of memory even when PGA_AGGREGATE_TARGET parameter set to a high enough value

 

Solution

A program global area (PGA) is a memory region which contains data and control information for a server process. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.

Untunable PGA memory is like context information for each session, for each open/active cursor, PL/SQL or Java memory.
Tunable PGA memory is memory allocated for intensive memory SQL operators like sorts, hash-join, group-by, bitmap merge and bitmap index create, also knows as work areas

The size of a work area can be controlled and tuned.

 

In Oracle 8i and prior releases, the maximum size of work areas could be controlled by setting the following parameters: sort_area_size, hash_area_size, bitmap_merge_area_size and create_bitmap_area_size. 

Oracle9i, Release 1 (9.0.1), introduces a new mode to automatically and globally manage the size of work areas by the new initialization parameter pga_aggregate_target, which controls the total amount of PGA memory allocated across all database server processes.

 

If the size of a work area is not big enough to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator, then part of the input data must be spilled to temporary disk storage.

The maximum size of a PGA workarea is controlled by internal hidden parameter _PGA_MAX_SIZE. The default value is 200Mb and the max values is capped at 2GB on 64-bit systems (1GB limit for 32-bit systems), and this behavior is introduced in 10.2 via unpublished Bug 3946308. The fix for Bug 17951233 doesn't allow a work area to obtain more than 2 GBs. It simply prevents the session from failing with ORA-00600 [kcblin_3] error which would have been raised if _PGA_MAX_SIZE set higher than 2GB.

With the automatic PGA management mode, you can control the size for the work area sizes, to be able perform big sort operations or hash joins in memory and avoid messages like: kxhfWrite: hash-join is spilling to disk

You have to set the following parameters to proper values:

1. PGA_AGGREGATE_TARGET should be set to five times the desired work area size

2. _PGA_MAX_SIZE should be set in minimum of twice the desired work area size. The default value is 200Mb, max 2GB

3. _SMM_MAX_SIZE normally this parameter is not needed but maybe under certain circumstances if set it should be equal to the desired work area size (in kb !). This parameter can't have a value greater than 2GB

Also please be careful that the above changes are very dangerous because the values affect all sessions in the database. If you need to have granular control of the work area for a single session, then it is better to set at the session level WORKAREA_SIZE_POLICY=MANUAL and *_AREA_SIZE as high as needed.

Note.
_smm_isort_cap = maximum work area for insertion sort(v1)
Its default value is 100M. After this value is reached, Oracle will spill to disk. If _pga_max_size is explicitly set then the value of
_smm_isort_cap is set to 0  which means that only memory is used (no spill on disk). Its value can set set to higher values than default one
(and lower than _pga_max_size) in which case memory will be used for such types of sorts usually used in DW related queries.
Not spilling to disk does not always mean that query will be faster.

If 2GB is still not enough to process the operation entirely in memory using a single process, then parallel servers could be used, since each parallel server will have its own work areas.

V$SQL_WORKAREA_ACTIVE contains an instantaneous view of the work areas currently allocated by the system. You can join this view against V$SQL_WORKAREA on WORKAREA_ADDRESS to access the definition of that work area. If a work area spills to disk, then this view contains information for the temporary segment created on behalf of this work area.