Synopsis.
Sizing the
Program Global Area (PGA) is now one of many tasks made simpler under Oracle
9i. This article discusses how a database server utilizes the PGA, how the PGA
can be automatically sized using PGA_AGGREGATE_TARGET initialization parameter,
and how the PGA’s size can be monitored and tuned using some new dynamic
database views now part of 9i.
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.
