Memory Structures - Expert Oracle Database Architecture - page 146

One of the most perplexing things for a DBA can be setting the individual parameters, especially parameters such
as SORT|HASH_AREA_SIZE and so on. I often see systems running with incredibly small values for these
parameters—values so small that system performance is massively impacted in a negative way. This is probably a
result of the fact that the default values are very small themselves: 64KB for sorting and 128KB for hashing. There's a
lot of confusion over how big or small these values should be. Not only that, but the values you should use for them
might vary over time, as the day goes by. At 8:00 a.m., with two users, a 50MB sort area size might be reasonable
for the two users logged in. However, at 12:00 p.m. with 500 users, 50MB might not be appropriate. This is where
the WORKAREA_SIZE_POLICY = AUTO setting and the corresponding PGA_AGGREGATE_TARGET come in handy. Setting
the PGA_AGGREGATE_TARGET , the amount of memory you would like Oracle to feel free to use to sort and hash, is
conceptually easier than trying to figure out the perfect SORT|HASH_AREA_SIZE , especially since there isn't a perfect
value for these parameters; the perfect value varies by workload.
Historically, DBAs configured the amount of memory Oracle would use by setting the size of the SGA (the buffer
cache; the log buffer; and the shared, large, and Java pools). The remaining memory on the machine would then be
used by the dedicated or shared servers in the PGA region. The DBA had little control over how much of this memory
would or would not be used. She could set the SORT_AREA_SIZE , but if there were 10 concurrent sorts, Oracle could use
as much as 10 * SORT_AREA_SIZE bytes of RAM. If there were 100 concurrent sorts, Oracle would use 100 * SORT_AREA_
SIZE bytes; for 1,000 concurrent sorts, 1,000 * SORT_AREA_SIZE ; and so on. Couple that with the fact that other things
go into the PGA, and you really didn't have good control over the maximal use of PGA memory on the system.
What you'd like to happen is for this memory to be allocated differently as the memory demands on the system
grow and shrink. The more users, the less RAM each should use. The fewer users, the more RAM each should use.
Setting WORKAREA_SIZE_POLICY = AUTO is just the way to achieve this. The DBA specifies a single size now, the
PGA_AGGREGATE_TARGET or the maximum amount of PGA memory that the database should strive to use. Oracle then
distributes this memory over the active sessions as it sees fit. Further, with Oracle9 i Release 2 and up, there is even
a PGA advisory (part of Statspack and AWR, available via a V$ dynamic performance view and visible in Enterprise
Manager), much like the buffer cache advisor. It will tell you over time what the optimal PGA_AGGREGATE_TARGET
for your instance is to minimize physical I/O to your temporary tablespaces. You can use this information to either
dynamically change the PGA size online (if you have sufficient RAM) or decide whether you need more RAM on your
server to achieve optimal performance.
Are there times, however, when you won't want to use it? Absolutely, but fortunately they seem to be the
exception and not the rule. The automatic PGA memory management was designed to be multiuser “fair.” In
anticipation of additional users joining the system, the automatic memory management will limit the amount of
memory allocated as a percentage of the PGA_AGGREGATE_TARGET . But what happens when you don't want to be fair,
when you know that you should get all of the memory available? Well, that would be time to use the ALTER SESSION
command to disable automatic memory management in your session (leaving it in place for all others) and to
manually set your SORT|HASH_AREA_SIZE as needed. For example, that large batch process that takes place at 2:00
a.m. and does tremendously large hash joins, some index builds, and the like? It should be permitted to use all of the
resources available to the instance. It does not want to be “fair” about memory use—it wants it all, as it knows it is the
only thing happening in the database right now. That batch job can certainly issue the ALTER SESSION command and
make use of all resources available.
So, in short, I prefer to use automatic PGA memory management for end-user sessions—for the applications that
run day to day against my database. Manual memory management makes sense for large batch jobs that run during
periods when they are the only activities in the instance.
PGA and UGA Wrap-up
So far, we have looked at two memory structures: the PGA and the UGA. You should understand now that the PGA
is private to a process. It is the set of variables that an Oracle dedicated or shared server needs to have independent
of a session. The PGA is a “heap” of memory in which other structures may be allocated. The UGA is also a heap of
memory in which various session-specific structures may be defined. The UGA is allocated from the PGA when you
use a dedicated server to connect to Oracle, and from the SGA under a shared server connection. This implies that