Tuning PGA Memory

Tuesday Apr 22nd 2003 by DatabaseJournal.com Staff

Oracle9i provides an option to completely automate the management of PGA memory. Oracle professionals merely need to specify the maximum amount of PGA memory available to an instance using a newly introduced initialization parameter PGA_AGGREGATE_TARGET.

by Sreeram Surapaneni

Oracle is becoming one of the most flexible and sophisticated database management systems. Its ability to provide performance views for memory management components of the database is an invaluable tool especially for managing DSS systems. With it, DBA`s can more easily ensure that all the settings for Oracle memory utilization are optimal for their applications. Process Global Area, often known as the Program Global Area (PGA) is a dedicated area of Oracle memory used by individual processes to perform memory intensive functions, such as sorting or hash join. In previous releases of Oracle the PGA is configured by carefully adjusting a number of initialization parameters, such as, SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc. Oracle9i provides an option to completely automate the management of PGA memory. Oracle professionals merely need to specify the maximum amount of PGA memory available to an instance using a newly introduced initialization parameter PGA_AGGREGATE_TARGET.

When an Oracle process requires an operation, such as a sort , hash join or group by it goes to the shared tunable memory area within pga_aggregate_target region and attempts to obtain enough contiguous memory structures to perform the operation. This shared turnable memory part can be shrunk/expanded in function of the system load. If the process is able to acquire these memory structures immediately, it is marked as an optimal memory access. If the memory acquisition requires a single pass through pga_aggregate_target, the memory allocation is marked as one pass. If all memory is in use, Oracle may have to make multiple passes through pga_aggregate_target to acquire the memory. Multipass executions indicate a memory shortage, and you should always allocate enough PGA memory to ensure that at least 95 percent of connected tasks can acquire their memory optimally.

To illustrate this consider the following queries

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

select name, value

The result of the query might look like the following:

NAME                                               VALUE  
aggregate PGA target parameter                     524288000 bytes 
aggregate PGA auto target                          463435776 bytes 
global memory bound                                25600 bytes 
total PGA inuse                                    9353216 bytes 
total PGA allocated                                73516032 bytes 
maximum PGA allocated                              698371072 bytes 
total PGA used for auto workareas                  0 bytes 
maximum PGA used for auto workareas                560744448 bytes 
total PGA used for manual workareas                0 bytes 
maximum PGA used for manual workareas              0 bytes 
over allocation count                              0 bytes 
bytes processed                              4.0072E+10 bytes 
extra bytes read/written                     3.1517E+10 bytes 
cache hit percentage                               60.02 percent 

Important parameter to look at in the above output is the value of aggregate PGA auto target, which represents shared tunable part of PGA memory and it should not be small compared to the value of PGA_AGGREGATE_TARGET. Oracle dynamically derives the value for aggregate PGA auto target from the value set for PGA_AGGREGATE_TARGET and is continuously adjusted by Oracle. It is the amount of memory that can be used for work areas running in automatic mode.You must ensure that enough PGA memory is left for work areas running in automatic mode for memory intensive operations. The other untunable memory part of PGA memory contains context information for each session, for each open/active cursor, PL/SQL or Java memory. The other parameter in the above ouput to look at is over allocation count which means the number of times Oracle did not honor the parameter pga_aggregate_target. This happens when PGA memory is too small and Oracle is unable to accommodate the untunable PGA memory part plus the minimum memory required to execute the work area workload. Ideally, this value should be zero. Parameter cache hit percentage in the above output means that percentage of work areas that has used an optimal amount PGA memory during execution since instance start-up.


  • Whenever the value of the v$sysstat statistic estimated PGA memory for one-pass exceeds pga_aggregate_target, then you'll want to increase pga_aggregate_target.
  • Whenever the value of the v$sysstat statistic workarea executions-multipass is greater than 1 percent, the database may benefit from additional PGA memory.

To substantiate this consider the following query:

col c1 heading 'Workarea|Profile' format a35
col c2 heading 'Count' format 999,999,999
col c3 heading 'Percentage' format 99
select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3
select name,value count,(sum(value) over ()) total
name like 'workarea exec%'


PROFILE                        CNT        PERCENTAGE
 -----------------------------  ---------- ----------
 workarea executions - optimal        5395         98
 workarea executions - onepass         284          2
 workarea executions - multipass         0          0

To determine how key PGA statistics will be impacted if you change the value of PGA_AGGREGATE_TARGET, can be illustrated with the following example. The following select statement can be used to find this information

SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, 
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, 
FROM   v$pga_target_advice; 

The output of this query might look like the following:

---------- -------------- -------------------- 
63         23             367 
125        24             30 
250        30             3 
375        39             0 
500        58             0 
600        59             0 
700        59             0 
800        60             0 
900        60             0 
1000       61             0 
1500       67             0 
2000       76             0 
3000       83             0 
4000       85             0 

From the above results we should set the PGA_AGGREGATE_TARGET parameter to a value where we avoid any over allocation, so lowest PGA_AGGREGATE_TARGET value we can set is 375 (where ESTD_OVERALLOC_COUNT is 0). After eliminating over-allocations, the goal is to maximize the PGA cache hit Percentage based on your response-time requirement and memory constraints.

Restrictions: Automatic PGA memory management via PGA_AGGREGATE_TARGET only applies to dedicated server connections. Memory allocation for shared server connections is still managed by SORT_AREA_SIZE, HASH_AREA_SIZE, and so forth.

Sreeram Surapaneni is an Oracle Certifited DBA (7.3,8,8i,9i) working currently as DBA, Research In Motion Ltd, Canada and can be reached at Sreeram Surapaneni.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved