As Oracle DBAs, we typically spend considerable time planning, sizing, and monitoring the System Global Area (SGA) because of its importance to our databases' performance. However, the Program Global Area (PGA) can be equally important to throughput and performance because it's where database applications do much of their work.

Recently I had the chance to delve into the PGA's inner workings during a conversion of several Oracle 8i databases to the Oracle 9iR2 environment. I found that under 9i, it is even easier to monitor what is going on inside the PGA, and with the new Automatic PGA Memory Management features of 9i, Oracle now manages sizing for dedicated sessions. (We'll talk about how the PGA is managed for shared sessions a bit later.)

First, some details. Our current production database uses Oracle 9iR2 in a Windows 2000 Advanced Server environment. The database server needs to support a minimum of 200 dedicated connections for our primary Powerbuilder client-server applications. The database also supports a few dozen shared connections for some web and reporting applications, but the majority of the production server is dedicated for online transaction processing (OLTP). Since the database has to manage a considerable number of dedicated connections for OLTP processing, the PGA needed to be configured for maximum efficiency.

PGA Architecture: Work Areas

Oracle defines the PGA as a private memory region containing data and control information for a server process. Essentially, it is where the run time version of the code that is being executed is stored temporarily--for example, the runtime area of a cursor.

Complex queries--for example, ones that use a lot of sorting (GROUP BY, ORDER BY, ROLLUP), or whose query access plans utilize hash joins, bitmap merges, and bitmap creates--tend to allocate a large portion of this runtime area for work areas for these memory-intensive operations. In addition, bulk-loading operations that require large write buffers need large work areas.

Essentially, how a work area is sized determines the efficiency and speed of the query. For the best results the work area for, say, a large query with a lot of sorting should be large enough that all its input data and auxiliary memory structures created by its SQL operators will fit inside that work area. Oracle terms this the optimal size of a work area.

So, what happens when the work area's size is exceeded? Response time increases because the server process has to make an extra pass over the input data (termed the one-pass size of the work area). Moreover, if the work area is exceedingly small, the server process has to make multiple passes over the work area (termed the multi-pass size of the work area). Tuning the work areas in the PGA so that the query runs within the optimal size of the work area eliminates these additional passes over the input data, insuring the query runs faster and uses PGA resources more efficiently.

Automatic PGA Memory Management

Before Oracle 9i, the maximum size of work areas was based on values set for the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE initialization parameters. Since the optimal work area size is ideally based on the size of the input data and the number of work areas already active, it is difficult to set these parameters for optimum performance for every situation and point in time.

However, with the new automatic PGA memory management features of Oracle 9i, the allocation of optimum sizes for work areas is handed over to the DBMS itself. In fact, for dedicated sessions, the initialization parameter settings mentioned previously are ignored. Instead, Oracle uses the initialization parameter PGA_AGGREGATE_TARGET to derive appropriate memory allocations for work areas sizes.

The Oracle DBMS attempts to honor the target value set by PGA_AGGREGATE_TARGET at all times. The total amount of PGA memory available to the instance's active work areas is automatically derived and is equal to the value of PGA_AGGREGATE_TARGET less the total PGA memory in use by other system components (for example, PGA memory used by sessions). It accomplishes this by dynamically controlling the amount of memory allocated to SQL work areas while simultaneously maximizing the number of optimally-sized work areas. Remaining non-optimally sized work areas are executed in one-pass mode, unless PGA_AGGREGATE_TARGET has been set too low; in that case, multi-pass mode will be used for those work areas.

Page 2

Back to DBAsupport.com