|
Category :
Database
Resources -> RDBMS Server
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
The
9i version of oracle introduced the concept of Automatic PGA Memory
Management.
What is PGA?
At a
high level, Program Global Area or PGA as it is called, resides in the
process private memory of the server process. The composition of the
PGA differs based on whether the connection made to the database is
dedicated or MTS (Multi-Threaded Server). For dedicated connections,
the PGA has 2 components : stack space and UGA. UGA has the user
session data, cursor state, and sort area.
On the other hand for a Shared
Server configuration, where one shared server is shared by multiple
user processes, UGA is part of the Shared Pool or Large Pool (if large
pool is configured for the database).
What is Automatic PGA
Memory Management ?
Oracle9i provides an option to
completely automate the management of PGA memory.
Administrators just need to specify the maximum amount of PGA memory
available
to an instance using a newly introduced initialization parameter and
the database server automatically distributes this memory among
various active queries in an intelligent manner so as to ensure
maximum performance benefits and the most efficient utilization of
memory.
Various database parameters
that control PGA memory management:
(1) workarea_size_policy=auto
If the value of
this parameter is set to AUTO then, Work areas used by
memory-intensive operators are sized automatically, based on the PGA
memory used by the system, the target PGA memory set in
PGA_AGGREGATE_TARGET,
and the requirement of each individual operator. You can specify
AUTO
only when PGA_AGGREGATE_TARGET
is defined
(2)
pga_aggregate_target
This
parameter sets the value for total PGA memory that can be used across
all the sessions is a database. Oracle provides us with the following
method for calculating an appropriate value for this parameter:
- For
OLTP systems
PGA_AGGREGATE_TARGET = (<Total Physical Machine Memory > *
80%) * 20%
- For DSS systems
PGA_AGGREGATE_TARGET = (<Total Physical Machine Memory > *
80%) * 50%
Important Note : The next 3 parameters are hidden parameters.
Please consult with Oracle Corporation before changing the values for
these parameters.
(3) _pga_max_size
Sets the maximum size of
the PGA memory for a single process.
(4) _smm_max_size
Sets the maximum PGA
allocated to each slave in a parallel process.
(5) _smm_px_max_size
PGA upper limit on the sum
across the slaves of the memory for a single work area operation only
when the degree of parallelism is greater that a certain value.
|
