Oracle Tips by Burleson Consulting
Automatic RAM memory
management in Oracle
As we have noted, a serious problem in Oracle8i was the requirement
that all dedicated connections use a one-size-fits-all
sort_area_size. Oracle now has the option of running automatic PGA
memory management. Oracle has introduced a new init.ora parameter
called pga_aggregate_target. When the pga_aggregate_target parameter
is set and you are using dedicated Oracle connections, Oracle will
ignore all of the PGA parameters in the init.ora file, including
sort_area_size and sort_area_retained_size. Oracle recommends that
the value of pga_aggregate_target be set to the amount of remaining
memory (less a 20% overhead for other UNIX tasks) on the UNIX server
after the instance has been started.
Once the pga_aggregate_target has been set, Oracle will
automatically manage PGA memory allocation, based upon the
individual needs of each Oracle connection. Oracle also allows the
pga_aggregate_target parameter to be modified at the instance level
with the alter system command, thereby allowing the DBA to
dynamically adjust the total RAM region available to Oracle.
Oracle also introduces a new parameter called workarea_size_policy.
When this parameter is set to automatic, all Oracle connections will
benefit from the shared PGA memory. When workarea_size_policy is set
to manual, connections will allocate memory according to the values
for the sort_area_size parameter. Under this automatic mode, Oracle
tries to maximize the number of work areas that are using optimal
memory and uses one-pass memory for the others.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA
Copyright © 1996 - 2014 by
Burleson. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.