Almost every Oracle
professionals 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.
Automatic Memory Management (AMM) replaces
pga_aggregate_target, For 10g see
and 11g and beyond, see my notes on the memory_target_parameter.
pga_aggregate_target Oracle provides a dictionary
view called v$pgastat. The v$pgastat view
shows the total amount of RAM memory utilization for
every RAM memory region within the database.
You can also increase your pga_aggregate_target
above the default 200 megabyte setting by setting the
hidden _pga_max_size parameter.
- _pga_max_size =
- _smm_px_max_size =
pga_aggregate_target and _pga_max_size
hidden parameter set to 1 meg we see a
5x improvement over the default for parallel
queries and sorts:
- A RAM sort or hash
join may now have up to 50 megabytes (5%
- Parallel queries
may now have up to 330 megabytes of RAM
(30% of pga_aggegate_target),
such that a DEGREE=4 parallel query
would have 83 megabytes (333 meg/4).
In Oracle 10g we see that the AWR has a time-series
table called dba_hist_pgastat to measure PGA
usage over time. You can get complete 10g scripts
for monitoring AWR dba_hist_pgastat here:
Advanced Oracle Monitoring and Tuning Script Collection
This information can tell you
the high water mark of RAM utilization, and allow you to
size RAM memory demands according to the relative stress
on the system. Here is a simple query against
column name format a40
column value format 999,999,999
From this listing, you can see
the value of pga_aggregate_target and the high
water marks for all RAM memory areas used by this
instance. But let's take a look at optimal, one pass,
and multipass RAM memory executions.
When an Oracle process requires an operation, such as a
sort or a hash join, it goes to the shared RAM memory
area within pga_aggregate_target region and
attempts to obtain enough contiguous RAM frames to
perform the operation. If the process is able to acquire
these RAM frames immediately from
pga_aggregate_target, it is marked as an "optimal"
If the RAM acquisition
requires a single pass through pga_aggregate_target,
the RAM memory allocation is marked as one pass. If all
RAM is in use, Oracle may have to make multiple passes
through pga_aggregate_target to acquire the RAM
memory. This is called multipass.
Remember, RAM memory is extremely fast, and most sorts
or hash joins are completed in microseconds. Oracle
allows a single process to use up to 5 percent of the
pga_aggregate_target, and parallel operations are
allowed to consume up to 30 percent of the PGA RAM pool.
Pga_aggregate_target "multipass" executions indicate a
RAM shortage, and you should always allocate enough RAM
to ensure that at least 95 percent of connected tasks
can acquire their RAM memory optimally.
You can also obtain information about workarea
executions by querying the v$sysstat view shown
c1 heading 'Workarea|Profile' format a35
col c2 heading 'Count' format 999,999,999
col c3 heading 'Percentage' format 99
select name c1,cnt c2,decode(total, 0, 0,
select name,value cnt,(sum(value) over ()) total
name like 'workarea exec%'