Introduction to PGA
management
Almost every Oracle professional agrees
that the old-fashioned sort_area_size and
hash_area_size parameters imposed a
cumbersome one-size-fits-all approach to
sorting and hash joins. Different tasks
require different RAM areas, and the trick
has been to allow "enough" PGA RAM for
sorting and hash joins without having any
high-resource task "hog" all of the PGA, to
the exclusion of other users.
Oracle9i introduced the
pga_aggregate_target parameters to fix this
resource issue, and by-and-large,
pga_aggregate_target works very well for
most systems. You can check your overall PGA
usage with the v$pga_target_advice advisory
utility or a STATSPACK or AWR report. High
values for multi-pass executions, high disk
sorts, or low hash join invocation might
indicate a low resource usage for PGA
regions.
Let's take a look at the issues
surrounding the hidden limits of
pga_aggregate_target.
Hidden parameters for Oracle PGA regions
With proper understanding (and knowing
that these undocumented parameters are not
supported by Oracle), you can adjust your
PGA regions to allow for system-specific
sorting and hash joins.
- _pga_max_size
- this hidden
parameter defaults to 200 megabytes,
regardless of the setting for
pga_aggregate_target.
- _smm_px_max_size
- This
parameter is used for Oracle parallel
query, and defaults to 30% of the
pga_aggregate_target setting, divided by
degree of parallelism (as set by a
PARALLEL hint, "alter table xxx
parallel" command, or the
parallel_automatic_tuning initialization
parameter). For example, by default a
DEGREE=4 parallel query would have a
maximum sort area value of 15 megabytes
per session with a 200 megabyte
pga_aggregate_target setting. Remember,
parallel full-table scans bypass the
data buffers and store the incoming data
rows in the PGA region and not inside
the data buffers (as defined by the
db_cache_size parameter).
The limits of sorting and hashing
There are important limitations of
pga_aggregate_target:
- The total work area cannot exceed
200 megabytes of RAM because of the
default setting for _pga_max_size.
- No RAM sort may use more than 5% of
pga_aggregate_target or _pga_max_size,
whichever is smaller. This means that no
task may use more than 200 megabytes for
sorting or hash joins. The algorithm
further reduces this to (200/2) for
sorts so the actual limit for pure sorts
will be 100 megabytes.
These restrictions were made to ensure
that no large sorts or hash joins hog the
PGA RAM area, but there are some secrets to
optimize the PGA. For example, the following
set of parameters may be mutually-exclusive:
- sort_area_size=1048576 <--
sort_area_size is ignored when
pga_aggregate_target is set and when
workarea_size_policy =auto, unless you
are using a specialized feature such as
the MTS. If dedicated server connections
are used, the sort_area_size parameter
is ignored.
- pga_aggregate_target = 500m
<-- The maximum default allowed value is
200 megabytes, this limits sorts to 25
megabytes (5% of 500m).
- mts_servers<>0 <-- If
Multi-threaded server is being used, the
pga_aggregate_target setting would be
ignored in all versions except
Oracle10g.
(Note: there may be some cases where
sort_area_size is used in Oracle utilities,
but these have not been documented, even
with pga_aggregate_target.)
We also see these additional undocumented
parameters:
Parameter Name |
Description |
_smm_advice_enabled |
if TRUE, enable v$pga_advice |
_smm_advice_log_size |
overwrites default size of the
PGA advice workarea history log |
_smm_auto_cost_enabled |
if TRUE, use the AUTO size
policy cost functions |
_smm_auto_max_io_size |
Maximum IO size (in KB) used by
sort/hash-join in auto mode |
_smm_auto_min_io_size\ |
Minimum IO size (in KB) used by
sort/hash-join in auto mode |
_smm_bound |
overwrites memory manager
automatically computed bound |
_smm_control |
provides controls on the memory
manager |
_smm_max_size |
maximum work area size in auto
mode (serial) |
_smm_min_size |
minimum work area size in auto
mode |
_smm_px_max_size |
maximum work area size in auto
mode (global) |
_smm_trace |
Turn on/off tracing for SQL
memory manager |
WARNING - These are unsupported
parameters and they should not be used
unless you have tested their behavior on
your own database and you are willing to
accept full responsibility for any issues.
Super-size me
For certain Oracle applications the
Oracle professional will want to allow
individual tasks to exceed the default
limits imposed by Oracle. For example,
PC-based, 64 bit Oracle servers (1 or 2
CPU's with 8 gigabytes of RAM) will often
have unused RAM available. For example, a
fully-cached 5 gigabyte database on an 8
gigabyte dedicated Oracle server will have
approximately 1 gigabyte available for the
PGA (allowing 20% for the OS and other SGA
regions):
- O/S - 1.6 gig
- SGA - 5 gig
- PGA Space - 1 gig
- Total - 8 gig
The system has a pga_aggregate_target
setting of 1 gigabyte and the undocumented
parameters are at their default settings. While it is unusual for an online system
to require super-sized regions for sorting
(because the result sets for online screens
are normally small), there can be a benefit
to having large RAM regions available for
the Oracle optimizer.
The Oracle cost-based optimizer will
determine whether a hash join would be
beneficial over a nested-loop join, so
making more PGA available for hash joins
will not have any detrimental effect since
the optimizer will only invoke a super-sized
hash join if it is better than a nested-loop
join. In a system like the example above,
the following settings would increase the
default sizes for large sorts and hash joins
while limiting those for parallel sorts.
- pga_aggregate_target = 4g
- _pga_max_size = 400m
- _smm_px_max_size = 333m
With these hidden parameters set we see
significant size increase for serial sorts
and a throttling effect for parallel queries
and sorts. To see a reproducible, artificial
test case demonstrating sort throttling,
Mike Ault has prepared a 230 page artificial
test case:
Validation of Sort Sizes in a Linux
Oracle10g Database. However, bear in
mind that it only valid for a specific
release of Oracle10g, on a specific hardware
and OS environment, and not using any
optional features such as the MTS.
- A RAM sort or hash join may now have
up to the full 200 megabytes (5% of
pga_aggregate_target) a 400% increase
over a 1 gigabyte pga_aggregate_target
setting. With the default settings, only
a 200% (100 megabyte size) increase
would be possible.
- Parallel queries are now limited to
333 megabytes of RAM (30% of
pga_aggregate_target or _smm_px_max_size), such that a DEGREE=4
parallel query would have a maximum of
83 megabytes (333 meg/4) per slave which
may actually be less due to internal
sizing algorithms that set the memory
increments used in setting sort areas.
This throttling is to prevent one
parallel query using all available
memory since _smm_px_max_size would
default to 1.2 gigabytes with the
setting for pga_aggregate_target at 4
gigabytes.
- You must be careful in setting the
pga_aggregate_target to greater than the
available memory, calculate the maximum
number of users who would be
sorting/hashing and multiple that times
the predicted size to get your actual
limitations otherwise ORA-4030 errors or
swapping may occur.
In conclusion, overriding the built-in
safeguards of pga_aggregate_target can make
more efficient use of RAM resources in cases
where large RAM regions are available on the
database server. When used with care (and
the blessing of Oracle Technical Support) it
can often make sense to over-ride these
default values to make better use of
expensive RAM resources.
There is also lots
of evidence that changing these parameters
will have a positive effect of large,
batch-oriented Oracle jobs, but you must be
very careful to fully understand the
limitations of the PGA parameters:
Success stories for PGA
size expansion
If you have a limited
number of active sessions you may wish to
override the PGA governor that only allows
any single task to consume 5% of the total
PGA. Laurent Schneider notes in Oracle
MOSC that overriding the PGA defaults
made a large batch processes run more than
8x faster:
"I set appropriate values for
pga_aggregate_target and _pga_max_size...
alter system set pga_aggregate_target=6G;
alter system set "_pga_max_size"=2000000000;
...and I gave the query some hints
"NOREWRITE FULL USE_HASH ORDERED". As a
result, it boosted my query performance from
12 hours to 1.5 hour."
Ah, if only it were that
easy, just change a setting and batch jobs
run six times faster.
Laurent Schneider notes some perils and
reliability issues relating to this
parameter and says "this parameter
often leads to an ORA-4030,
even when plenty of memory available, for
some obscure reasons".
PGA usage Note:
There are other tricks for overcoming the built-in governor for PGA
usage. Oracle has a 5% limit for
any individual process, and by using parallel DML any single batch job can
consume 30% of the PGA without touching any of the undocumented parameters.
Oracle author
Laurent Schneider noted:
?I finally
opted for a more maintainable solution.
No more hints, no more undocumented parameter, but parallel
processing up to 16 threads on a 4 CPU server.
As discussed in
MOSC thread 460157.996, a supported way to increase the
maximum PGA memory per single SQL query is to increase the degree of
parallelism."