PGA memory management Oracle 10/11g

A program global area (PGA) is a memory region that contains data and control information for a server process. It is a nonshared memory created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.

Starting with Oracle9i Release 1 and above, there are two ways to manage this other non-UGA memory in the PGA:

• Manual PGA memory management, where you tell Oracle how much memory it can use to sort and hash any time it needs to sort or hash in a specific process.
• Automatic PGA memory management, where you tell Oracle how much memory it should attempt to use system wide.

This setting is controlled by the database initialization parameter WORKAREA_SIZE_POLICY and may be altered at session level.

For automatic PGA memory management: WORKAREA_SIZE_POLICY=AUTO

For manual PGA memory management:    WORKAREA_SIZE_POLICY=MANUAL

Starting in Oracle 11g Release 1, automatic PGA memory management can be implemented using one of two techniques:

•  By setting the PGA_AGGREGATE_TARGET initialization parameter and telling Oracle how much PGA memory to attempt to use.
•  By setting the MEMORY_TARGET initialization parameter and telling Oracle how much total memory the database instance should use for both the SGA and the PGA; the PGA size will be figured out by the database itself from this parameter.

MANUAL PGA Memory Management:

• SORT_AREA_SIZE: The total amount of RAM that will be used to sort information before swapping out to disk.
• SORT_AREA_RETAINED_SIZE: The amount of memory that will be used to hold sorted data after the sort is complete.That is if SORT_AREA_SIZE is 512KB and SORT_AREA_RETAINED_SIZE is 250KB,your server process would use upto 512 KB of memory to sort the data during the initial processing of the query.When the sort was complete,the sorting area would “shrink” to 250KB, and any sorted data that did not fit in that 250KB would be written out to the temporary tablespace.
• HASH_AREA_SIZE: The amount of memory your server process can use to store hash tables in memory.These structures are used during a hash join, typically when joining a large set with another one.The smaller of the two sets would behashed into memory any anything that didn’t fit in the hash area region of memory would be sorted in the temp tablespace.

Automatic PGA memory management:

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 (unless and until you change it). You then tell Oracle, "This is how much memory you should try to limit yourself to across all work areas” (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’s true,

let’s take a look at how to set up automatic PGA memory management and turn it on.The process of setting this up involves deciding on the proper values for two instance initialization parameters:

• 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 willb vary based on the current workload 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 work areas used to sort or 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.

In Oracle 11g Release 1 and above, instead of setting the PGA_AGGREGATE_TARGET, you can set the

MEMORY_TARGET parameter. When the database uses the MEMORY_TARGET parameter, it decides how much memory to allocate to the SGA and PGA respectively. It may also decide to reallocate these memory amounts while the database is up and running. This fact, however, doesn’t affect how automatic PGA memory management (described below) works; rather it just decides the setting for the PGA_AGGREGATE_TARGET.

So, the entire goal of automatic PGA memory management is to maximize the use of RAM while at the same time not using more RAM than you want. Under manual memory management, this was a virtually impossible goal to achieve. If you set SORT_AREA_SIZE to 10MB, when one user was performing a sort operation that user would use up to 10MB for the sort work area. If 100 users were doing the same, they would use up to 1,000MB of memory. If you had 500MB of free memory, the single user performing a sort by himself could have used much more memory, and the 100 users should have used much less.That is what automatic PGA memory management was designed to do. Under a light workload, memory usage could be maximized as the load increases on the system, and as more users perform sort or hash operations, the amount of memory allocated to them would decrease—to reach the goal of using all available RAM, but not attempting to use more than physically exists.

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.

Are there times, however, when you won't want to use AUTOMATIC PGA Memory Management? Absolutely, but fortunately they seem to be the exception and not the rule. The automatic 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 am and does tremendously large hash joins, some index builds, and the like? It should be permitted to use all of the resources on the machine.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 database.

"How is this memory allocated?" and "What will be the amount of RAM used by my session?"

When using things that begin with "A"—for automatic—you lose a degree of control, as the underlying algorithms decide what to do and how to control things.

We can make some observations based on information from MetaLink note 147806.1:

• The PGA_AGGREGATE_TARGET is a goal of an upper limit. It is not a value that is preallocated when the database is started up. You can observe this by setting the PGA_AGGREGATE_TARGET to a value much higher than the amount of physical memory you have available on your server. You will not see any large allocation of memory as a result.

• A serial (nonparallel query) session will use a small percentage of the PGA_AGGREGATE_TARGET, typically about 5 percent or less. So, if you’ve set the PGA_AGGREGATE_TARGET to 100MB, you’d expect to use no more than about 5MB per work area (e.g., the sort or hash work area). You may well have multiple work areas in your session for multiple queries, or more than one sort or hash operation in a single query, but each work area will be about 5 percent or less of the PGA_AGGREGATE_TARGET. Note that this 5 percent is not a hard and fast rule; things change over time, the automatic algorithms can and will change in the database.

• As the workload on your server goes up (more concurrent queries, concurrent users), the amount of PGA memory allocated to your work areas will go down. The database will try to keep the sum of all PGA allocations under the threshold set by PGA_AGGREGATE_TARGET. This is analogous to having a DBA sit at a console all day,setting the SORT_AREA_SIZE and HASH_AREA_SIZE parameters based on the amount of work being performed in the database.

• A parallel query may use up to about 30 percent of the PGA_AGGREGATE_TARGET, with each parallel process getting its slice of that 30 percent. That is, each parallel process would be able to use about 0.3 * PGA_AGGREGATE_TARGET / (number of parallel processes).

The  PGA_AGGREGATE_TARGET is just that: a target, not a directive. We can and will exceed this value for various reasons.

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 when using a shared server, you must size your SGA's large pool to have enough space to cater to every possible user that will ever connect to your database concurrently. So, the SGA of a database supporting shared server connections is generally much larger than the SGA for a similarly configured dedicated server mode-only database.