https://www.oracle.com/technetwork/issue-archive/2007/07-mar/o27asktom-084983.html

see last article there for a bit of information.


the pga aggregate target is used to set a "soft" (exceedable) limit for dynamic memory allocated by processes (sort areas, hash area, program variables and the like).


It is a system wide setting, and this quote from Expert Oracle Database Architecture describes it:

<quote>
Automatic PGA Memory Management

Starting with Oracle9i, Release 1, a new way to manage PGA memory was introduced that avoids using the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE Parameters. It was introduced to attempt to address a few issues:

* Ease of use: Much confusion surrounded how to set the proper *_AREA_SIZE parameters. There was also much confusion over how those parameters actually worked and how memory was allocated.

* Manual allocation was a ¿one-size-fits-all¿ method: Typically as the number of users running similar applications against a database went up, the amount of memory used for sorting/hashing went up linearly as well. If 10 concurrent users with a sort area size of 1MB used 10MB of memory, 100 concurrent users would probably use 100MB, 1,000 would probably use 1000MB, and so on. Unless the DBA was sitting at the console continually adjusting the sort/hash area size settings, everyone would pretty much use the same values all day long. Consider the previous example, where you saw for yourself how the physical I/O to temp decreased as the amount of RAM we allowed ourselves to use went up. If you run that example for yourself, you will almost certainly see a decrease in response time as the amount of RAM available for sorting increases. Manual allocation fixes the amount of memory to be used for sorting at a more or less constant number, regardless of how much memory is actually available. Automatic memory management allows us to use the memory when it is available; it dynamically adjusts the amount of memory we use based on the workload.

* Memory control: As a result of the previous point, it was hard, if not impossible, to keep the Oracle instance inside a ¿box¿ memory-wise. You could not control the amount of memory the instance was going to use, as you had no real control over the number of simultaneous sorts/hashes taking place. It was far too easy to use more real memory (actual physical free memory) than was available on the machine.


Enter automatic PGA memory management. Here, you first simply set up and size the SGA. The SGA is a fixed-size piece of memory, so you can very accurately see how big it is, and that will be its total size (until and if you change that). You then tell Oracle, ¿This is how much memory you should try to limit yourself across all workareas¿a new umbrella term for the sorting and hashing areas you use.¿ Now, you could in theory take a machine with 2GB of physical memory and allocate 768MB of memory to the SGA and 768MB of memory to the PGA, leaving 512MB of memory for the OS and other processes. I say ¿in theory¿ because it doesn¿t work exactly that cleanly, but it¿s close. Before I discuss why that is true, we¿ll take a look at how to set up automatic PGA memory management and turn it on.

The process to set this up involves deciding on the proper values for two instance initialization parameters, namely

* WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which will use the sort area and hash area size parameters to control the amount of memory allocated, or AUTO, in which case the amount of memory allocated will vary based on the current workload present in the database. The default and recommended value is AUTO.

* PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should allocate, in total, for all workareas used to sort/hash data. Its default value varies by version and may be set by various tools such as the DBCA. In general, if you are using automatic PGA memory management, you should explicitly set this parameter.

So, assuming that WORKAREA_SIZE_POLICY is set to AUTO, and PGA_AGGREGATE_TARGET has a nonzero value, you will be using the new automatic PGA memory management. You can ¿turn it on" in your session via the ALTER SESSION command or at the system level via the ALTER SESSION command.
</quote>


If you set yours to 4gb - then the database will try keep PGA memory allocations under that. It does this by only giving each session some small percentage of the allowed PGA - eg: about 5% or less. So, a single session would not monopolize all of the pga, but rather gets a bit of it.

And if your session exceeds that - it is just like exceeding the sort area size - it'll spill into temp.

Consider the pga aggregate target a way to have the sort area size dynamically set and reset itself as the load on the system goes up and down over time.