Database Reference
In-Depth Information
USERS MY_PGA TOTAL_PGA
---------- ---------- ---------
1 7 7
26 7 182
51 7 357
76 7 532
101 7 707
126 7 882
151 7 1,057
176 7 1,232
201 7 1,407
226 7 1,582
251 7 1,757
11 rows selected.
■
This query uses a technique—recursive subquery factoring—that is available only in Oracle 11
g
release 2 and
above. It will not work in earlier releases.
Note
Had I run this test (I have 2GB of real memory on this server and my SGA is 256MB), by the time I got to 250
users, the machine would have begun paging and swapping to the point where it would have been impossible to
continue; at 500 users I would have allocated around 3,514MB of RAM! So, the DBA would probably not set the
SORT_AREA_SIZE
to 5MB on this system, but rather to about 0.5 MB, in an attempt to keep the maximum PGA usage
at a bearable level at peak. At 500 users, I would have had about 500MB of PGA allocated, perhaps similar to what we
observed with automatic memory management, but even when there were fewer users, we would still have written to
temp rather than performing the sort in memory.
Manual memory management represents a very predictable—but suboptimal—use of memory as the workload
increases or decreases over time. Automatic PGA memory management was designed specifically to allow a small
community of users to use as much RAM as possible when it was available, to back off on this allocation over time
as the load increased, and increase the amount of RAM allocated for individual operations over time as the load
decreased.
Using PGA_AGGREGATE_TARGET to Control Memory Allocation
Earlier, I wrote that “in theory” we can use the
PGA_AGGREGATE_TARGET
to control the overall amount of PGA memory
used by the instance. We saw in the last example that this is not a hard limit, however. The instance will attempt to stay
within the bounds of the
PGA_AGGREGATE_TARGET
, but if it can't, it won't stop processing; rather, it will just be forced to
exceed that threshold.
Another reason this limit is “theory” is because work areas, though large contributors to PGA memory, are not
the only contributors to PGA memory. Many factors contribute to PGA memory allocation and only the work areas are
under the control of the database instance. If you create and execute a PL/SQL block of code that fills in a large array
with data in dedicated server mode where the UGA is in the PGA, Oracle can't do anything but allow you to do it.
