Database Reference
In-Depth Information
As expected, an optimal sort was performed. The response time is 14 s. Halving DOP
results in only two processes sharing the workload and the following measurements:
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
140 23:48 3.1 2.7 0 0
147 23:48 3.8 3.1 0 0
…
147 24:03 1.2 40 1 71
…
140 24:08 1.2 40 1 63
Here,
_SMM_MAX_SIZE
leads to a degradation of response time to around 20 s, since at DOP
two each process requires a work area size of around 75 MB, but only 40 MB was available,
resulting in one-pass sorts and spilling to disk. Now back to the original DOP of four—a reduc-
tion of
_SMM_PX_MAX_SIZE
below the data volume divided by DOP also results in spilling to disk.
Following are the results at DOP four with these settings:
pga_aggregate_target=160m
_smm_px_max_size=122880 # in KB
_smm_max_size=40960 # in KB
This time,
_SMM_PX_MAX_SIZE
is the limiting factor.
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
143 33:27 1.7 1.7 0 0
…
145 33:41 1.2 30 1 40
…
146 33:44 1.2 30 1 32
…
144 33:46 1.2 30 1 32
…
143 33:49 1.2 30 1 31
All slaves spilled their work areas to disk, since work areas were limited to 120 MB/DOP=40 MB
and the query completed in 22 s.
Lessons Learned
When using automatic PGA memory management, three hidden parameters—
_PGA_MAX_SIZE
,
_SMM_MAX_SIZE
, and
_SMM_PX_MAX_SIZE
—work behind the scenes to enforce restrictions on
memory consumption. The parameter
_PGA_MAX_SIZE
limits the size of all work areas in use by
a single process. The size of an individual work area is limited by
_SMM_MAX_SIZE
for both serial
and parallel execution. When parallel execution is used, an additional restriction on the total
size of all work areas in use by the processes involved is in place. This limit is controlled with
the parameter
_SMM_PX_MAX_SIZE
. Within certain limits, all three parameters are recalculated at
runtime as a result of modifying PAT. All three parameters may be set manually to override the
result of this calculation.
