sizing pga_aggregate_target and pga_max_size for Oracle

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.

For monitoring 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 = 1000m
• _smm_px_max_size = 333m

With 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% of pga_aggegate_target).
   
• 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 v$pgastat:

column name format a40
column value format 999,999,999

select name,value
from
v$pgastat
order by
value desc;

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" RAM access.

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 here:

col 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, round(cnt*100/total)) c3
from
(
select name,value cnt,(sum(value) over ()) total
from
v$sysstat
where
name like 'workarea exec%'
);
 

See code depot for complete pga_aggregate_target scripts

If you liked this tip you might enjoy the bestselling book Oracle 10g Grid & Real Application Clusters - Oracle10g Grid Computing with RAC.  You can order it directly and save 30% here:

http://www.rampant-books.com/book_2004_1_10g_grid.htm