Program Global Area (PGA)
A Program Global Area (PGA) is a
memory region that contains data and control information for a
server process. It is a non-shared memory region created by Oracle
when a server process is started. Access to the PGA is exclusive to
that server process and it is read and written only by Oracle code
acting on its behalf. It contains a private SQL area and a session
memory area.
A private SQL area contains data
such as bind information and runtime memory structures. Each session
that issues a SQL statement has a private SQL area. Session memory
is the memory allocated to hold a session's variables (logon
information) and other information related to the session. For a
shared server, the session memory is shared and not private.
Not all RAM in Oracle is shared memory. When you start a
user process, that process has a private RAM area, used for sorting SQL results
and managing special joins called ?hash? joins. This private RAM is known as the
Program Global Area (PGA). Each individual PGA memory area is allocated each
time a new user connects to the database. For more information, click on ?Oracle
PGA Sizing?.
Oracle Database 12c will manage the PGA for you if you
set the pga_aggregate_target parameter (we will discuss parameters and how they
are set later in this book), but you can manually allocate the size of the PGA
via parameters such as sort_area_size and hash_area_size. We recommend that you
allow Oracle to configure these areas, and just configure the pga_aggregate_target
parameter.
The PGA can be critical to performance, particularly if
your application is doing a large number of sorts. Sort operations occur
if you use ORDER by and GROUP BY commands in your SQL statements. The
Oracle docs list these methods of PGA management and the associated parameters:
|
SGA
|
PGA
|
Description
|
Initialization Parameters
|
|
Instance
Auto
|
Instance
Auto
|
The database tunes the size of the instance based on a single instance
target size.
|
you set:
-
total memory target size for the database instance (memory_target)
-
optional maximum memory size for the database instance (memory_max_target)
|
|
Auto
|
Auto
|
The database automatically tunes the SGA based on an SGA target.
The database automatically tunes the PGA based on a PGA target.
|
you set:
-
SGA target size (sga_target)
-
optional SGA maximum size (sga_max_size)
-
PGA aggregate target size (pga_aggregate_target)
the database automatically configures the
pga_aggregate_limit
initialization parameter. you may set this parameter manually.
|
|
Auto
|
Manual
|
The database automatically tunes the SGA based on an SGA target.
You control the PGA manually, setting the maximum work area size for
each type of SQL
|
you set:
-
sga target size (sga_target)
-
optional SGA maximum size (sga_max_size)
-
PGA
work area parameters such as
sort_area_size,
hash_area_size,
and
bitmap_merge_area_size
|
|
Manual
|
Auto
|
You control the SGA manually by setting individual component sizes.
The database automatically tunes the PGA based on a PGA target.
|
you set:
-
shared pool size (shared_pool_size)
-
buffer cache size (db_cache_size)
-
large pool size (large_pool_size)
-
java pool size (java_pool_size)
-
streams pool size (streams_pool_size)
-
pga aggregate target size (pga_aggregate_target)foot
2
the database automatically configures the
pga_aggregate_limit
initialization parameter. you may set this parameter manually.
|
and include the URL for the page.
