Database Reference
In-Depth Information
I prepared the script
pga_aggregate_target_iterator.sql
, which varies
PGA_AGGREGATE_
TARGET
between its minimum value of 10 MB up to a maximum value of 32 GB and calls
auto_
pga_parameters.sql
for each setting, to investigate how changing the value of PAT affects these
hidden parameters. Shutting down and restarting the DBMS is not necessary since all three
parameters are recalculated dynamically within certain limits. The results are presented in
Table 1-1. Results beyond 8 GB are omitted, since no change in policy is seen beyond this value.
Table 1-1.
PGA_AGGREGATE_TARGET (PAT) and Dependent Hidden Parameters
PAT
_pga_max_size
(% of PAT)
_smm_max_size
(% of _pga_max_size)
_smm_max_size
as % of PAT
_smm_px_max_size
(% of PAT)
10 MB
200 MB (2000%)
2 MB (1%)
20%
5 MB (50%)
32 MB
200 MB (625%)
6.4 MB (3.2%)
20%
16 MB (50%)
64 MB
200 MB (320%)
12.8 MB (6.4%)
20%
32 MB (50%)
128 MB
200 MB (156%)
25 MB (12%)
20%
64 MB (50%)
256 MB
200 MB (78%)
51 MB (25%)
20%
128 MB (50%)
512 MB
200 MB (39%)
100 MB (50%)
19.5%
256 MB (50%)
1 GB
204 MB (20%)
102 MB (50%)
10%
512 MB (50%)
2 GB
410 MB(20%)
205 MB (50%)
10%
1 GB (50%)
3 GB
416 MB (13.5%)
208 MB (50%)
6.8%
1536 MB (50%)
4 GB
480 MB (11.7%)
240 MB (50%)
5.8%
2 GB (50%)
8 GB
480 MB (5.8%)
240 MB (50%)
2.9%
4 GB (50%)
Looking at Table 1-1, a few patterns emerge. These are addressed in the sections that
follow. Note that when overriding the settings of
_SMM_MAX_SIZE
or
_SMM_PX_MAX_SIZE
by putting
them into a parameter file, these are no longer dynamically adjusted as
PGA_AGGREGATE_TARGET
is modified. Since both parameters are static, the ability to change them at runtime, albeit indi-
rectly, is lost.
_PGA_MAX_SIZE
The parameter
_PGA_MAX_SIZE
limits the maximum size of all work areas for a single process.
For values of PAT below 1 GB,
_PGA_MAX_SIZE
is 200 MB.
For values of PAT between 1 GB and 2 GB,
_PGA_MAX_SIZE
is 20% of PAT.
At values beyond 2 GB,
_PGA_MAX_SIZE
keeps on growing as PAT is increased, but at a
lower rate, such that
_PGA_MAX_SIZE
is less than 20% of PAT.
A limit of 480 MB on
_PGA_MAX_SIZE
takes effect at a PAT value of 4 GB.
