Database Reference
In-Depth Information
Increasing PAT beyond 4 GB does not result in higher values of
_PGA_MAX_SIZE
than 480 MB.
In Oracle9
i
,
_PGA_MAX_SIZE
had a limit of 200 MB.
Just like
PGA_AGGREGATE_TARGET
,
_PGA_MAX_SIZE
is a dynamic parameter that can be modi-
fied with
ALTER
SYSTEM
. Changing
_PGA_MAX_SIZE
increases
_SMM_MAX_SIZE
in a similar way
that modifying
PGA_AGGREGATE_TARGET
does. However, the rule that
_SMM_MAX_SIZE
is 50% of
_PGA_MAX_SIZE
does not hold for manual changes of
_PGA_MAX_SIZE
. Following is an example
that increases
_PGA_MAX_SIZE
beyond the limit of 480 MB that can be reached by modifying
PGA_AGGREGATE_TARGET
:
SQL> @auto_pga_parameters
NAME Value (KB) DESCRIPTION
-------------------- ---------- --------------------------------------------
pga_aggregate_target 1048576 Target size for the aggregate PGA memory
consumed by the instance
_pga_max_size 209700 Maximum size of the PGA memory for one
process
_smm_max_size 104850 maximum work area size in auto mode (serial)
_smm_px_max_size 524288 maximum work area size in auto mode (global)
SQL> ALTER SYSTEM SET "_pga_max_size"=500m;
System altered.
SQL> @auto_pga_parameters
NAME Value (KB) DESCRIPTION
-------------------- ---------- --------------------------------------------
pga_aggregate_target 1048576 Target size for the aggregate PGA memory
consumed by the instance
_pga_max_size 512000 Maximum size of the PGA memory for one
process
_smm_max_size 209715 maximum work area size in auto mode (serial)
_smm_px_max_size 524288 maximum work area size in auto mode (global)
By increasing
_PGA_MAX_SIZE
, the work area size(s) available can be increased, without
extending the memory allowance for the entire instance. When memory is scarce this might
avoid some paging activity. As long as very few sessions concurrently request large work areas,
i.e., competition for PGA memory is low, this may lead to better response time for operations
involving large sorts. By altering
_PGA_MAX_SIZE
,
_SMM_MAX_SIZE
can be dynamically set to values
larger than the normal limit of 240 MB.
_SMM_MAX_SIZE
The parameter
_SMM_MAX_SIZE
limits the maximum size of an individual work area for a single
process.
For values of PAT below 512 MB,
_SMM_MAX_SIZE
is 20% of
PGA_AGGREGATE_TARGET
.
For PAT values of 512 MB and beyond,
_SMM_MAX_SIZE
is always 50% of
_PGA_MAX_SIZE
.
In Oracle9
i
,
_SMM_MAX_SIZE
had a limit of 100 MB. Following is an example of a session
that had two simultaneously active work areas when the given parameters were in effect:
